Using PowerPivot As an SSRS Data Source

The flow of data from SSRS to PowerPivot can almost be reversed completely, by using PowerPivot as a data source for an SSRS report. PowerPivot for Excel solutions can be stored in locations that may be only occasionally attached to a network, and therefore would have unpredictable availability. Examples of these occasionally connected locations include external USB hard disks, flash “keychain” drives, and even local disk drives. In these cases, PowerPivot for Excel solutions are not supported as a data source for SSRS. This is not the case in a PowerPivot for SharePoint solution. Deployment to SharePoint ensures the data is online, available for network access, and therefore available for connection to a SQL Server Reporting Services server.

The business case for using PowerPivot for SharePoint as an SSRS data source is similar to that for using SSRS as a data source for PowerPivot. This technique is most useful in a situation where business rules and data acquisition logic have been already established in a PowerPivot solution and those PowerPivot solution elements are required in an SSRS report. Furthermore, using either of the techniques covered in this chapter can serve to consolidate and reuse logic already created and tested in a new manner.

In order to create a connection from SSRS to PowerPivot, you will require access to an SSRS server. The SSRS server will provide a development environment for the SSRS reports (Report Builder), storage of the reports definitions (.rdl files), and, most importantly, an execution environment for the report.

Creating the PowerPivot Source Worksheet

In order to use PowerPivot as a data source for an SSRS report, the first requirement is a PowerPivot for SharePoint solution that contains the data to be surfaced in SSRS. For this example, we will utilize a simple PowerPivot for SharePoint workbook containing data from the AdventureWorks sample database. Since this worksheet requires a connection to your specific instance of the AdventureWorks database, it is more efficient to create it rather than include it in the example files.

Begin creation of the example source worksheet by opening Excel and creating a connection to the AdventureWorks sample database. As illustrated in Figure 9-10, select the From Database selection of the external data section of the Home ribbon. From the resulting drop-down menu, select the From SQL Server option in order to establish a connection with the SQL Server example database.

Figure 9-10. Configuring a SQL Server database connection

After clicking the From SQL Server option, PowerPivot will begin the Table Import Wizard. The first step of the wizard is a dialog similar to Figure 9-11. Complete the “Server name” text box with the location of your AdventureWorks sample databases. Additionally, the “Database name” pulldown should read “AdventureWorks”. Press the Next button to advance the Table Import Wizard to the next step, defining the tables or query containing the required data.

Figure 9-11. Table Import Wizard, data connection

For this example, we are using all of the data from three tables in the AdventureWorks database. Therefore, select the radio button option labeled “Select from a list of tables and views to choose the data to import”, and press the Next button to advance to the next step in the Table Import Wizard.

The subsequent task of the Table Import Wizard is to gather the tables that will be imported into PowerPivot. A dialog similar to Figure 9-12 is presented for this operation. Select the Product, ProductCategory, and ProductInventory tables, as illustrated in Figure 9-12. Pressing the Finish button will end the Table Import Wizard by importing the data from the selected tables into PowerPivot.

Figure 9-12. Table import selection

The final Table Import Wizard dialog consists of a list of the tables imported, and the row counts from each table. The final import success or failure dialog will be similar to Figure 9-13. Press the Close button to exit the Table Import Wizard and begin working with the data in PowerPivot for Excel.

Figure 9-13. Import row counts

At the conclusion of the Table Import Wizard, the data has been imported into the local, in-memory instance of SSAS specifically for usage by PowerPivot for Excel. From this point, creating a PowerPivot report is the same, irrespective of the data source. For our PowerPivot as a data source example, we need only create a PowerPivot report and deploy the Excel worksheet to a PowerPivot for SharePoint Gallery.

To complete the first task, creating a PowerPivot report, first ensure you are in an Excel window and not PowerPivot. From the PowerPivot ribbon, select PivotTable to add a new PivotTable to the worksheet. Select the Product Name column from the Product table as the row labels by dragging the column to the Row Labels area of the PowerPivot Field List. Similarly, add the Quantity column from the ProductInventory table as the sole column in the Values area of the PowerPivot Field List. When finished, your report and worksheet should resemble Figure 9-14.

Figure 9-14. PowerPivot report

The final task before configuring the new PowerPivot solution as an SSRS data source is deploying the solution to a PowerPivot for SharePoint Gallery. From the File ribbon menu from within Excel, use the Save & Send selection to browse for and select a PowerPivot for SharePoint Gallery destination and file name. When the upload of the file to SharePoint is complete, ensure the .xlsx file is checked in and available for others to utilize. When finished with this step, you should be able to browse the PowerPivot Gallery and both see the new file and open the file via the browser. Figure 9-15 illustrates the new Product Inventory.xlsx file in the PowerPivot Gallery on the server named “powerpivotbook”.

Figure 9-15. PowerPivot Gallery with new Product Inventory file

Opening Report Manager

Report Manager is a web application that is installed as a component of SSRS. While configurations vary, the usual URL for Report Manager is in the form http://<reportserver>/reports, where your server name is substituted for <reportserver>. In the case of this example, the SSRS server is located on a server named BlueMountain, and we are using the default port (80); therefore the URL for Report Manager is http://bluemountain/reports. Opening the URL in Internet Explorer will render a window similar to Figure 9-16.

Figure 9-16. Report Manager window

Developing the Example SSRS Report

In order to launch the Report Builder application, click the Report Builder icon shown in Report Manager in Figure 9-16. The click-once deployment will then start the process of downloading the Report Builder application to your workstation. After the rapid download and quick installation process completes, you should see a dialog on top of the Report Builder main window similar to Figure 9-17, containing a number of menu selections for creating reports and data sources.

Figure 9-17. Report Builder 3.0

The default, highlighted selection, New Report, is our desired item. We could also, starting with the left-hand navigation and working down, select one of the following options:

• New Dataset: Creates a source data definition to be used by perhaps multiple reports
• Open: Opens an existing report that has been saved previously
• Recent: Acting as a most recently used file list, allows for the reopening of a previous file

We are concerned with developing a new SSRS report using a PowerPivot solution as the source. Selecting the New Report option will allow us to choose between several report options. In order to continue creating the report, choose the “Table or Matrix Wizard” selection by single-clicking the sub-menu selection.

Report Builder will respond with the next step in the Report Wizard, creating or choosing a dataset, represented by a dialog similar to Figure 9-18. As we have no existing datasets, this is a fresh Reporting Services server installation; choose the option to “Create a dataset” near the bottom of the screen, and press the Next button.

Figure 9-18. Report Builder dataset selection

The first step in creating a dataset is establishing a connection to the data source. Report Builder responds to the “Create a dataset” selection by rendering a dialog similar to Figure 9-19. Again, in the event of a fresh Reporting Services installation, no data source connections will exist. However, even if connections were available, this is the point at which we will define the connection to PowerPivot for SharePoint.

Figure 9-19. Data source connection

Select “New…” from the lower portion of the dialog in Figure 9-19 to create a connection to PowerPivot for SharePoint. The first step of the data connection is to identify a name for use in the ensuing dialog illustrated in Figure 9-20. The default name of “DataSource1” is fine for our purposes, but I am renaming the data source to “PowerPivot_ProductInventory”. Be aware SSRS data source names must not contain spaces. Also, the data source name may contain numbers and the underscore character, but the first character must be a letter.

Figure 9-20. Data source connection definition

After establishing the name of the data source, the next set of radio buttons determine if this data source will reuse an existing, shared connection or use a connection that is embedded in the report. Choose the “Use a connection embedded in my report” option, as illustrated in Figure 9-20. Because we are using PowerPivot for SharePoint, the next element of the configuration, the connection type, should be set to Microsoft SQL Server Analysis Services. Finally, click the “Build…” button to create the connection string to PowerPivot for SharePoint. This will render a dialog similar to Figure 9-21.

Figure 9-21. Connection properties server name

Notice that the server name text box in Figure 9-21 contains a URL for the source PowerPivot workbook. Instead of connecting to the underlying SSAS instance for PowerPivot, we are using the PowerPivot web service to load the requested workbook, if necessary. By using the PowerPivot web service, we also let the PowerPivot service application for SharePoint execute any necessary load balancing, finding a server either by health or round-robin assignment, by which our request will be serviced. Finally, the web service access also impacts user credential configuration. Press OK to complete the connection properties; this should return the Data Source Properties dialog, Figure 9-22, to the focus.

Within the Data Source Properties dialog, select the Credentials panel by clicking the label in the left-hand navigation list. This will render a dialog similar to Figure 9-22. This panel of the data source configuration allows for customization of the user credentials that authenticate access to the underlying data. For our example, it is sufficient to use the “Prompt for credentials” selection, ensuring the “Use as Windows credentials” check box is in a checked state, similar to Figure 9-22. Activating the “Use as Windows credentials” check box will cause SSRS to prompt for a user ID and password at each execution of the report, and in turn utilize the user-entered information as a Windows login to the data source.

Figure 9-22. Data source credential options

At this point, we can now test the data source connection to verify the ability for SSRS to access the data in our PowerPivot for SharePoint solution. Select the General item from the left-hand navigation to return to the connection options panel illustrated in Figure 9-22. Press the Test Connection button. This will cause Reporting Services to respond with a prompt for Windows credentials. Entering credentials with access to the PowerPivot for SharePoint solution file will result in a successful connection message. If a connection error is generated, verify the connection properties and user credential privileges. Otherwise, press the OK button to save the data source. This will cause the next step in the Report Wizard, a window similar to Figure 9-23, to be rendered.

Figure 9-23. Data source complete

With a data source configured and saved, the next step in SSRS report development is creating the dataset containing information to be surfaced in the report. Pressing the Next button on the Report Wizard screen illustrated in Figure 9-23 will continue the dataset specification process. If you are prompted for Windows credentials, simply enter the user ID and password of an account used to test the data source configuration. Upon successful authentication, a Query Designer window similar to Figure 9-24 will be produced.

Figure 9-24. Dataset Query Designer

Similar to the Query Designer used in Chapter 8, this tool allows for creation of a dataset from the PowerPivot data source via a drag-and-drop interface. All of the tables contained in the Product Inventory.xlsx PowerPivot for SharePoint solution are enumerated in the left-hand navigation menu. Additionally, measures contained in the solution are listed under the Measures grouping in the metadata navigator. Dragging a dimension or measure to the central panel places that element in the dataset. Additionally, the dataset can be constrained or filtered based on attributes placed in the panel immediately above the dataset design surface. Drag the ProductID and Name attributes from the Product table onto the dataset design surface. Finally, drag the Sum of Quantity attribute from the ProductInventory measure group to the design surface. When finished, the Query Designer window should be similar to Figure 9-25.

Figure 9-25. Report Wizard Dataset Query Designer

Press the Next button at the bottom of the Query Designer window to save the new dataset. This will also advance to the next step of the Report Wizard, laying out the report elements.

After completing query design, the Report Wizard will render a screen similar to Figure 9-20. This area of the wizard will allow for configuration of the report layout, specifying measure values and column and row groupings. The Values area of the layout form specifies the elements of the report that will be described by row and optionally column headers. This is very similar to the PowerPivot Field List in behavior. Measures that are typically aggregated are rendered in the Values section. Based on elements in the Row group and/or Column group areas, the Values are aggregated and grouped. For our example report, drag the three elements of our dataset to the Row and Values areas, as illustrated in Figure 9-26.

Figure 9-26. Report layout

Pressing the Next button from the field layout will advance the Report Wizard to the specification of the total and sub-total layout. The defaults are illustrated in Figure 9-27. For the purposes of our example, accept the defaults and press the Next button to advance the Report Wizard to the next step.

Figure 9-27. Total and sub-total specification

The next and final step in the Report Wizard will configure the settings for the report style. Pick any color scheme and click the Finish button to complete the report design. The Report Wizard will complete, leaving a fully configured SSRS report as the primary window of Report Builder, similar to Figure 9-28. In the example illustrated, the title of the report has been changed to “PowerPivot for SharePoint Example”.

Figure 9-28. Completed report definition

At this point, the report is completely finished and ready for execution. Similar to most modern integrated development environments (IDE), Report Builder allows for execution from within the development tool. Pressing the F5 key or clicking the Run button in the upper left-hand corner will execute the new report. This should result in a window similar to Figure 9-29.

Figure 9-29. Report execution within Report Builder

After successfully executing the report, the definition in the form of a report definition language (.rdl) file can be saved to the SSRS server. Clicking either the disk icon, in the upper left-hand corner of the Report Builder title bar, or the ribbon “pearl” and selecting Save from the options will prompt for a name for the new report in a dialog similar to Figure 9-30. Note the location to which the report will be saved is the URL to the SSRS server. I have named the report “PowerPivot for SharePoint Example,” but you can choose any name for the report.

Figure 9-30. Report Builder Save dialog

Clicking the Save button will deploy the RDL to the SSRS server. Upon refreshing the Report Manager window, illustrated in Figure 9-16 earlier, you should see the new report listed, similar to Figure 9-31.

Figure 9-31. Deployed example report

Clicking the report name in Report Manager will cause the report to execute. Additionally, from the pulldown menu in Report Manager, the report can be opened in Report Builder (as illustrated in Figure 9-31).