Category Archives: Reporting Services

Excel Services has been Deprecated

SharePoint 2013 (and previous versions) shipped with Excel Services, allowing Excel workbooks to be viewed in the browser. SharePoint 2013 integrated with Office Web Apps (OWA), which also had the capability to show Excel workbooks in the browser. The complication in 2013 was that the two services had somewhat different functionality, resulting in some organizations having to use Excel Services to gain access to specific BI functionality. In SharePoint 2016, Excel on Office Online Server (OOS), formerly known as Office Web Apps (OWA), has been enhanced and now has the capability to work with Excel workbooks that utilize BI capabilities, such as data models.

Microsoft is now recommending that organizations move away from Excel Services and to use OOS instead. This has a couple impacts on clients:

  1. Smaller implementations, such as organizations using SharePoint as a platform for Project Server and that support a limited number of users, will now need a second server. Excel services was a service that ran on SharePoint, but OOS is a separate server product that cannot be installed on the same server as SharePoint.
  2. The list of requirements to support BI is still quite complicated, and will be challenging to communicate to clients. (perhaps a good infographic would help). For example, Excel files with Power View will require SSRS in SharePoint integrated mode to be viewable in the browser. Also, refreshing connections in Excel files with data models will require the PowerPivot add-in.

Microsoft Blog Post about this is here
(see the word doc that is linked in the first paragraph for details on deploying PowerPivot and Power View in 2016)

A good document that compares Excel Services and the Excel Web App from 2013 is here

Advertisements

BI Options

The options for displaying data are vast and confusing. Following is a short(!) summary of some of the available options:

SSRS

  • SSRS is great for displaying static reports, such as when users want the report to look a certain way each and every time the report is viewed
  • SSRS reports can be viewed in the browser or can be saved as PDF or Excel files and emailed to users
  • Since SSRS reports can be viewed in a browser, users can view them with no training other than telling them what link to click on
  • SSRS reports can be set to run the underlying queries on a set schedule so that slow queries can be run overnight
  • SSRS works by sending queries to database, allowing it to send a query to a large database and receive only the records that meet some criteria. This allows SSRS to present live data with no wait times for users, even when the underlying datasets are large.
  • SSRS can work in two modes:
    • SharePoint Integrated
      • Reports are stored in SharePoint libraries.
      • Permissions for reports are in-part handled by SharePoint.
    • Native Mode
      • In Native mode, SSRS provides a site where users can view reports
      • Permissions for reports are configured within the SSRS site
      • Native mode supports the following for authentication: Negotiate, Kerberos, NTLM, Basic
    • Reports can be surfaced in custom ASP.Net applications

Excel

  • Excel can use data from databases as the source of pivot tables and charts
  • Users who know how to use pivot tables can add/remove fields and otherwise rearrange the pivot table to meet their needs
  • Pivot Tables support “slicers”, which allow users to easily filter reports
  • Excel works by importing potentially large numbers of rows, and allowing users to filter/aggregate, etc. This is a nice capability for analysts, but this means that the data isn’t “live”. To get live data, the excel user must “refresh” the dataset, which can take a varying amount of time, depending on the amount of data.
  • Excel “reports” can be uploaded to a SharePoint library, and can be displayed on a SharePoint page via a web part
    • Excel “reports” that are uploaded to a SharePoint library can be refreshed with current data, but that feature requires the installation of additional components on SharePoint.
  • Quite of bit of Excel Documentation will mention “Power View”. However, Power View is based on Silverlight and is being phased out.

Power BI

  • The phrase “Power BI” has meant a few different things of the last couple years:
    • The set of BI tools that are available for Excel, including PowerPivot, Power Query, Power Maps, and Power View
    • A feature within Office 365 that allowed PowerBI Excel workbooks to be displayed and datasets within to be refreshed. This particular capability has been phased out, and has been replaced by PowerBI.com
    • A cloud-based web site (PowerBI.com) that allows users to create dashboards using data from cloud services as well as on-prem databases
  • PowerBI.com
    • As mentioned above, this supports building dashboards based on data from cloud sources or on-prem databases. There are a couple main ways of supporting this scecario:
      • Manual: a user creates an excel file with data from an on-prem source, uploads the file to powerbi.com, and then creates dashboards based on the data. Because there is no connection between PowerBi.com and the on-prem data source, the free version of PowerBI.com may be used.
      • Automated: A user installs and configures a “gateway”, and then builds a dashboard using data from the on-prem database. Once published to PowerBI.com, the gateway allows PowerBI.com to refresh the dataset on a configured schedule.

 

 

The Future of Power View and Comparing Data Refresh Options in Power BI

Oh Power View, we hardly knew you.

From Microsoft support:

“The Power View button in the Reports group has been removed from the ribbon in Excel 2016 for Windows. The interactive visual experience provided by Power View is now available as part of Power BI Desktop.”

This support article goes on to say that Power View can still be turned on in Excel 2016 and is still supported. But this means that creating new projects based on Power View is now questionable. Though, the article is correct: the functionality of Power View is indeed available in Power BI. It’s essentially the same tool from a usage perspective, so if a user knows how to build dashboards in Power View, they’ll be up and running with Power Bi in minutes. Though, while building dashboards is the same, figuring out how data is refreshed and ramping up on licensing is a bit more challenging. So, following are different usage scenarios for refreshing data, starting with Power View in an Excel workbook that’s not hosted in SharePoint, and progresses though different options up to Power BI. Note, in all cases the basic scenario is the same: a dashboard that gets its data from an on-prem SQL Server database.

  • If Excel with a Power View sheet is used without SharePoint, then an employee could open the Excel workbook which could be stored anywhere or even emailed to users. The workbook could be configured to automatically refresh the data from the database as the user opened the workbook.
  • If Excel with a Power View sheet is used with either SharePoint on-prem or Office 365, users would be able to view the dashboard in their browser. However, the dashboard would contain the data as it existed the last time the workbook was refreshed. To refresh the workbook, a user would have to open the Excel file in Excel, refresh the data connections, and save the file.
    • If everything is set up correctly and if the moon is full, users could refresh the workbook in their browser. However, the refreshed data is not saved back to the workbook. So if Sal refreshes the workbook in the browser, he’ll see the refreshed data but when Sue views the same dashboards several minutes later, it won’t have the refreshed data, and will only display the data as it existed the last time the workbook was saved.
    • As an alternative to having a user opening the file and refreshing, a PowerShell script could be used.
  • If SharePoint on-prem has PowerPivot for SharePoint installed, then the data connections could be set to refresh on a schedule. Note that PowerPivot for SharePoint has additional licensing and hardware requirements. If it is not already installed, installing it is not a trivial process.
    • This leaves out a few technical details. For example, automatic refreshing of data sources isn’t supported for Excel workbooks that have Power View worksheets in them. But a Power View could be created in SharePoint outside of the Excel file.
  • If the free version of Power BI is used, users would be able to view the dashboard in their browser. However, the dashboard would contain the data as it existed the last time the workbook was refreshed. To refresh the workbook, a licensed user would have to navigate to the Power BI site and click a link to refresh the data connection. To view the dashboard, users would only need to sign in to Office 365, and click a link to navigate to the dashboard. (The documentation is confusing on this point, but this is the behavior I’ve seen. To be clear, when I tested it, one user who had a Pro license was able to refresh the dataset, while other users who only had the free version of Power BI were able to view it).
  • If everyone who is going to view the dashboard has a PowerBI Pro license, then the dashboard can be set to refresh automatically on a schedule. Users would only need to sign in to Office 365, and click a link to view the dashboards in Power BI. This is the nicest options for users. However, if users are not already licensed for PowerBI, getting licenses for all users is not a trivial cost.
  • Or, as an alternative, use one of the several available methods for getting data into Azure SQL. Power BI can refresh data from Azure SQL once per day without a Pro license. This does mean having to pay for an Azure SQL instance, but that could wind up being quite a bit cheaper than paying for the Power BI Pro licenses.

Reporting with Project Online, attempt #2

Previously, I wrote a bit of a rant about reporting with Project Online. I still think it’s a bit of a disaster, but I’ve learned a bit more since writing that post, and can now produce somewhat functional reports without Excel crashing. It still takes quite a bit longer than with SSRS, but now it is possible.

This post will go through the process of building an Excel PivotTable that uses data from a PowerPivot source, that is getting data via an OData feed from Project Online.

1. Authenticate

PowerPivot is not capable of authenticating to Office 365. Perhaps that will change in the future, but that is the reality today. The workaround is to configure fiddler to submit the authentication token for Excel. So, the broad steps are:

a. Download and install fiddler

b. See this post for instructions on how to configure custom rules in fiddler to enable authentication with Office 365

c. With fiddler running, navigate to the Project Online site in a browser (now fiddler has the authentication token)

d. Open the PowerPivot window, and proceed.

 

2. Get Data

In theory, we should be able to connect to several different OData feeds, and then build a pivot table with them. I could not get this to work. It would start to work, but Excel would repeatedly crash, and I would constantly get out of memory errors. But, the following works:

a. In the PowerPivot window, select “get external data” –>  “from Data Service” –>From OData Feed.

b. Paste in the URL for the OData Feed, such as https://somesite.sharepoint.com/sites/pwa/_api/projectdata/Tasks

c. See the special note below for the Projects feed. DO NOT just use projectdata/Projects

d. Finish the little wizard for the feed, and repeat for any other OData feeds you need from projectdata.

e. Switch to the diagram view, and configure relationships for the feeds, as appropriate

f. Identify which feed is the lowest level you need for the given report. For, example, if you’re reporting on Projects, tasks, and assignments, the “lowest level” feed is assignments. (The feed that is the “many” part of a one to many relationship).

g. Switch to the tab for the “lowest level” feed, as mentioned above. Scroll to the far right, and add calculated fields for each and every field you need to display on the report that is not in this tab already. The goal is to be able to build the pivot table with only using fields from a single tab in PowerPivot. For example, perhaps you need a report that has data from the Assignments feed, but you also need the “ProjectType” field from the projects tab. In the PowerPivot window, switch to the Assignments tab, and add a field with the following formula: =RELATED(Projects[ProjectType]). Repeat this for every field needed on the report.

h. Add a pivot table, using only fields from the one PowerPivot tab.

Note: As mentioned above, the Projects feed requires special handling. When I tried to use the Projects feed, it consistently gave errors, until I added a select statement to specify the list of fields and avoided ParentProjectId.  I simply could not get the Projects feed to work if that field was included, but it works fine if it’s not there. For example:

https://site.sharepoint.com/sites/pwa/_api/projectdata/Projects?$select=ProjectId,ProjectActualWork,ProjectDescription,ProjectName,etc,etc2&$orderby=somefield,somefield2

I assume there’s an issue with a data type. If you know how to resolve this issue, please leave a comment!

So, the above system works for me. I still daydream of using SSRS while building these reports, but it does work. If you know of better ways of building reports with Project Online, again, please leave a comment.

Reporting with Project Online

Summary

Reporting with Project Online is a bit of a disaster.

Edit – I’ve learned a bit more since publishing this. See the newer post here:

https://mikesnotebook.wordpress.com/2014/06/03/reporting-with-project-online-attempt-2/

 

Full Details

With an on premise installation of Project Server, we have access to the reporting database (well, it’s all one database at this point, so perhaps I should call it something else…). The reporting tables are beautiful. They are well organized and clearly named.

We don’t have access to the reporting database in Project Online. Instead we have OData (which is also available to on premise installations). OData is certainly great for developers. Pass in a URL, get data back. Nice. With OData, you can specify fields, filters, etc. (As a side note, if you need to work with OData, go get a copy of LinqPad (www.linqpad.net). It’s a fantastic GUI tool to help you build out the URLs..)

So, how do you actually use an OData feed from Project Online? SSRS is out, as it doesn’t support OData feeds (Perhaps it can through an xml hack, but you can’t host your reports on project online. If you try hosting them elsewhere, you’ll quickly run into authentication issues as SSRS doesn’t support authenticating with Office 365 sites.) The main option that we have is Excel. And, at first glance Excel seems to work just fine. You can quite easily retrieve a list of projects and display them in Excel. You can even use slicers to filter the data. Nice.

But how about a slightly more complicated scenario. How about a report that lists overdue tasks along with data about the assignment for all active projects for a given project manager? With the reporting database, this is a relatively simple query, but here’s the process for doing it with OData:

  1. Add data connections for each of the following:
    1. Projects
    2. Tasks
    3. Assignments
    4. Resources
  2. Wait for each data connection to bring in ALL of the data for the specified entity. (If you have a small number of projects, you’ll hopefully be able to avoid any out of memory errors)
  3. Build out your pivot table
  4. Publish to the reports folder in Project Online
  5. Explain to users that the data will be out of date when they view it, and show them how to refresh the data connections (how long this takes will depend on the number of projects you have. For me, this process took 3-5 minutes.)

This is why most of the demos of this feature use the Projects entity. Again, when you try to build a report that uses Assignments, it downloads ALL rows from the assignments table into Excel. Now, you can certainly customize the URL of the oData connection to specify a filter. But that filter cannot be dynamic. So, you can specify start and end dates, but those dates will be hardcoded (and there’s no “today” keyword we can use). Another example: you can easily set up the oData connection for Projects to only return “active” projects. But how do we specify that the tasks and assignments should only be for those active projects? We don’t have access to “join” capabilities. So again, the solution is to bring ALL data local (to Excel), and perform filtering there.

A word about data refresh: You can configure data connections in Excel to refresh on an interval, but that applies to while the Excel file is open in Excel, not while it’s sitting in a SharePoint library. So, if users want current data, they will have to refresh the connections. This is a not-great but workable solution if you’re listing projects (which load relatively quickly), but this is just silly if the user wants to view a report that contains info for tasks/assignment, and has to wait multiple minutes each time they want to view the report.

edit:

A couple more details:

  • Power View fully supports setting up joins based on the GUID fields that exist in the OData feeds.
  • Power Pivot does not support setting up joins based on the GUID fields. (I have no explanation for this, but this seems to be the case based on repeated testing).
  • If you go the OData route, realize that the OData feeds will only send the first x records, along with a link for the OData url to get the next batch.

</rant>

Get the Site Url for a Report running in SharePoint Integrated Mode

Subtitle: Using the XML Data Source to Query a SharePoint Web Service from within SSRS

The Challenge

Get the URL of the current site from within an SSRS report. So, given a report that is being run from:

http://server/sites/site/subsite/DocLibrary/folder1/report1.rdl

The goal is to return:

http://server/sites/site/subsite

Of course, the report could be deployed to any site/subsite/folder, so a simple string manipulation won’t cut it.

The Problem

SSRS provides a global field called ReportServerUrl, that normally returns, well, the report server url. In SharePoint integrated mode, this still returns the URL of the Report Server, which is likely to be something like this:

http://servername:port/ReportServer

Not what I’m after. Luckily, there’s another Global called ReportFolder, which will return the path down to the document library / folder. In this example, this Global would have a value of:

http://server/sites/site/subsite/DocLibrary/folder1

Close, but I still needed to get the URL of the site. The following forum post recommended using a .net assembly to get the correct value:

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/f6fde96f-2966-4e1d-a338-9e5de8764b45

The above works, but I’d prefer to try to get the value without having to deploy additional assemblies.

Solution

Webs.asmx is a web service that ships with SharePoint. One of its methods is: “GetWebUrlFromPageUrl”, and it does exactly what I need: it returns a site url given a file or directory url.

Steps:

  • Create an xml data source that points to the webs.asmx service
  • Create a DataSet that calls the GetWebUlFromPageUrl method

Create the XML Data Source

  1. Create a new data source in the report, give it a good name, and and specify a connection type of “XML”
  2. For the connection string, specify the following:
    1. http://server/_vti_bin/webs.asmx
  3. On the credentials tab, specify “use Current user” (or hard code user credentials, just don’t leave it on “do not use credentials”)
  4. Click ok

Create the dataset

  1. R-click on the data source, and select “Add Dataset”
  2. Give it a good name
  3. Paste the following into the query textbox:
<Query><SoapAction>http://schemas.microsoft.com/sharepoint/soap/WebUrlFromPageUrl</SoapAction><Method Name="WebUrlFromPageUrl" Namespace="http://schemas.microsoft.com/sharepoint/soap/"><Parameters><Parameter Name="pageUrl"></Parameter></Parameters></Method><ElementPath IgnoreNamespaces="true">*</ElementPath></Query>

  1. Note that the above XML contains a parameter, but it has no value.
  2. Click on the Parameters “tab” (ignore the warning about not being able to generate a list of fields)
  3. Click add to add a parameter
    1. Parameter Name: pageUrl (yes, it’s case sensitive)
    2. Parameter Value:
      1. Click the fx button
      2. Type in “=Globals!ReportFolder” (without the quotes)
      3. Click ok

  1. Click back on the query tab, and click “Refresh Fields”
  2. A box will come up that prompts you for the pageUrl value. Paste in a valid sample URL
  3. Click OK

That’s it! You should now have a dataset that contains two fields, one of which is WebUrlFromPageUrlResult!

SSRS 2012 Licensing

UPDATE

The licensing for SSRS has been updated as of the Product Use Document from April 2013.

Here’s the Additional Software section from the Jan 2013 Product Use Doc:

SSRS_Licensing_Jan_2013

And, here’s the same section of the April 2013 Product Use doc:

SSRS_Licensing_Apr_2013

So, the licensing is now like it was for SQL 2008. The server where SSRS is installed must be licensed for SQL (that’s SSRS itself, not the Reporting Services add-in).

(Both docs used in the above screenshots were downloaded from: http://www.microsoftvolumelicensing.com/DocumentSearch.aspx?Mode=3&DocumentTypeId=1)

The licensing model for running SQL Reporting Services in SharePoint integrated mode has changed from how it was in 2008.

In SQL 2012, if you have a 2 server SharePoint farm, where ServerA is SQL and ServerB is SharePoint (with reporting services components), ServerB does NOT have to be licensed for SQL.*

In SSRS 08, whichever server you install the SSRS components requires a SQL license. This means if you install the 2008 SSRS components on your SharePoint WFE, the WFE has to be licensed for SQL. (See this previous post for details on 2008 licensing). But now, in SQL 2012, the licensing has been changed to allow you to run SSRS on the SharePoint WFE for no additional licenses.

To verify this, see the “MicrosoftProductUseRights” from http://www.microsoftvolumelicensing.com/DocumentSearch.aspx?Mode=3&DocumentTypeId=1

In the English doc, page 137 lists the “Additional Software” for SQL 2012. By “Additional Software”, it means “You may run or otherwise use any number of instances of the corresponding additional software listed in Appendix 3 in physical or virtual OSEs on any number of devices. ” (The definition is from pg 40 of the doc).

*Of course, I’m not a licensing expert, so please call Microsoft to confirm.