I’ve had a few discussions in the past week about not only including Excel spreadsheets on PerformancePoint dashboards, but specifically about how to pass values from Filters to the spreadsheet. I did a few demonstrations to show the capability to some customers. Immediately after a few of the demonstrations I received a emails asking me to explain how I accomplished passing the filters to the spreadsheet. I would explain that I added a parameter to the spreadsheet before I saved it to SharePoint. Each time the customer would say, I did not know that was possible. As a result, I have decided to document the steps in this blog.
The first step is to build the spreadsheet. I have created a very simple sheet that is connected to an SSAS Tabular model. On the sheet is a Pivot table and a single filter, which can be seen in the following image:
Now the most important part is to name the Filter, which will act as the parameter, and to name the Pivot table. Naming the Pivot table is optional. However, if you don’t want to display the filter in the dashboard it is necessary. You can also simple hide row on which the filter resides. Completely up to you.
To name either you need to click FORMULAS from the Excel ribbon. Click in the cell that contains the values of the filter and select Define Name from the Defined Names section of the ribbon and the New Name window will appear. In the textbox labeled Name enter a value. Ensure that Workbook is selected in the Scope drop down list and click OK. The window should resemble the following:
You can repeat these steps for the Pivot table if you don’t want to display the filter. Now all you need to do is save the workbook in a document library in SharePoint. Select File | Save As. From the Save As options select SharePoint and browse to the location where the file will be saved. The Save As window will open. Click the Browser View Options button located towards the bottom of the window and a Browser View Options window will open, which can be seen below:
Select Items in the Workbook from the drop down list. If you have named the Pivot Table select it from the All Named Ranges item list. Next click on the tab labeled Parameters. Click on the button labeled Add and the Add Parameters window will appear:
Click the checkbox next to the parameter and click OK twice. Then save the workbook.
Finally, you should use PerformancePoint dashboard Designer to build a dashboard that will display the Pivot Table. Prior to creating the dashboard you should create a filter that contains the same data as the parameter (the filter that you added to the Pivot table) and you must create a report that displays the excel pivot table that you deployed to SharePoint. Once that is done, create a dashboard and add both the filter and the report to the dashboard. Once they are both there you need to create a connection on the report to the filter. To do so, click the drop down arrow located to the right of the report and select Create Connection:
Select the item that contains the filter.
Next, click the tab labeled Values. Select your parameter name from the drop down list labeled Connect to. Select Member Unique Name from the drop down list labeled Source value and click OK. Finally, right-click on the dashboard in the Workspace Browser, which is located on the left of the Dashboard Designer and select Deploy to SharePoint from the context menu that appears. When the deployment completes the dashboard will open in a web browser. Select a value from the filter and the spreadsheet values will update based on the selection.
As always, if you have any questions or concerns regarding this post, please feel free to email me at pleblanc@sqllunch.com.
Talk to you soon,
Patrick LeBlanc
