Consuming SSRS As a PowerPivot Data Source

In order to use SQL Server Reporting Services as a data source for PowerPivot, the first thing we need is a SQL Server Reporting Services report. For the example to follow, I'm using the SQL Server 2008 sample databases available from www.codeplex.com. We're going to use a very simple report from the AdventureWorks database. The report stored as a report definition language file or RDL file is available in the book's example download file.

Verifying Report Access

First we need to verify that we can actually access SQL Server Reporting Services. Troubleshooting connections among business intelligence products can be troublesome. It'll save time to verify access and connectivity up front. For this chapter's examples, I will be using a SQL Server Reporting Services instance named BlueMountain in native mode. The URL for the reporting server report management home page is in the form HTTP://BlueMountain/reports. Opening the URL for the Reporting Services report page from a browser should yield a result similar to Figure 9-1.

Figure 9-1. Reporting Services report page

It is probably worth taking some time to understand the features available from this page as SQL Server Reporting Services is integral to the examples. At the top of the page, the highlighted menu bar contains icons representing many of the key operations that can be performed from this location. Immediately below the menu bar is the listing of reports available. Finally in the upper right-hand corner of the page are links to return to the home page, manage report subscriptions, go to site settings options, and go to online help.

Clicking the Purchase Order Totals report link will execute the report and render the results in the browser. The Purchase Order Totals report will produce a result similar to Figure 9-2. Additionally, if the current user did not have the required privileges to execute the report, a prompt for credentials will occur. Executing this report in this manner ensures our ability to use the report from within PowerPivot for Excel.

Figure 9-2. SSRS report execution

Note all that has happened here is the simple report, without parameters, executed its query to the database and has rendered the results in the browser. However, this test execution of the report will be valuable as we use the SSRS report as a data source for PowerPivot. Clicking the Advance to Final Page arrow, just to the left of the zoom level (set to 100%), will display the final page of the report, including grand totals, as pictured in Figure 9-3.

Figure 9-3. Report grand totals

Configuring the Connection

To set up a connection between the Purchase Order Totals report and a PowerPivot solution, begin in the PowerPivot window of a new, blank Excel worksheet. From the Get External Data section of the Home ribbon, use the From Report option, as illustrated in Figure 9-4.

Figure 9-4. PowerPivot Get External Data From Report menu

Clicking the From Report menu item will begin the Table Import Wizard. The dialog will prompt for a friendly connection name. Choose a name that will allow you to easily reuse the connection definition within PowerPivot. Additionally, the path to the SSRS report definition file (.rdl) will be required. The URL for the sample report will be a little different from the Report Manager page used to execute and view the report. Instead of the Report Manager (http://reportserver/reports), the URL for the SSRS web service must be used. For our example, the URL for the SSRS web service is http://bluemountain/reportserver. In order to enter the URL, press the Browse button, which will result in a dialog similar to Figure 9-5.

Figure 9-5. Reporting Services web service URL entry

After entering the reporting service URL, pressing the Open button will open a list of the reports, for the current user, from the SSRS server. The list from our example server contains the single report we are using, the Purchase Order Totals report. In a dialog similar to Figure 9-6, double-click the desired report in order to advance to the next step of the Table Import Wizard.

Figure 9-6. Browsing SSRS reports

The Table Import Wizard will trigger execution of the report, and the contents will be available for browsing in the central pane of the dialog, similar to Figure 9-7.

Figure 9-7. Report execution in Table Import Wizard

Pressing the Next button from the report preview will render the list of all tables within the report available for import, as shown in Figure 9-8. Our example report contains a single table, already named PurchaseOrders within the SSRS report definition language. PowerPivot's table import process has already used the source table name as the friendly name. As illustrated in Figure 9-7, the value of the friendly name can be changed by clicking in the text box and entering a new name.

Figure 9-8. Entering a friendly table name

Clicking the Finish button will import all of the rows in the report. Alternatively, the Preview & Filter button can be used to access the data to be imported and optionally create filters. For this initial example, we will use all of the rows in the source report. The final dialog of the table import process will reveal the total rows imported. Clicking the Close button will reveal the new table, sourced from the SSRS report, within the PowerPivot environment, similar to Figure 9-9.

Figure 9-9. Completed SSRS import

Note there are two additional columns that were not immediately apparent in the original report. PowerPivot has named the columns Textbox8 and Textbox9, adding them as the final two columns in the PurchaseTotals table. These columns have been sourced from the grand totals for the OrderQty and TotalDue columns in the original report. The values in the Textbox8 and Textbox9 columns match with the grand totals from Figure 9-3. The first three columns in the PurchaseTotals table map directly to the three columns in the body of the original SSRS report: purchase order ID, order quantity, and total due.

The new table, from the SSRS report data, can now be related to data from other sources supported by PowerPivot. Just as any other PowerPivot table, the data in the PurchaseTotals table can be surfaced in PivotTables and PivotCharts as well as generate slicer values.

The real value in the technique I've just described can be realized in organizations with a robust library of Reporting Services reports. In addition to ensuring a consistent application of the business rules within the report logic, an organization's PowerPivot development is assured of using data that is synchronized with the existing SSRS reports. Furthermore, because the data source (SQL Server Reporting Services) is available online, when the PowerPivot for Excel solution is deployed to a SharePoint PowerPivot Gallery, the data refresh schedule can be managed as outlined in Chapter 7.