Today we have a guest post from Dan Parish, who is a program manager on the Excel Services team. Dan is going to explain a bit more on how to use Excel Services to set up dashboards that are driven by parameters.
Back in the original posts regarding Excel Services, Dave mentioned that Excel Services is targeted at consumers and explorers of workbooks. As such, Excel Services doesn’t allow you to simply type into any cell or create new workbooks on the fly. Dave also mentioned that we do understand that in some scenarios some cells need to be edited, and this post discussed the idea of ‘parameters’, those being specifically marked cells that can be edited on the server. Letâ€™s take a closer look.
Why use parameters?
In the firts public beta, we only allowed you to mark single cell named ranges that were either blank, or contained a value (i.e. no formulas) as parameters. With the most recent beta build, however, we also added in the ability to mark PivotTable Report Filters as parameters. Since PivotTable and OLAP Formulas are the only way to refresh external data with Excel Services this release, this allows you to be able to drive your external data queries using web parts other than the Excel Services web part (called Excel Web Access or EWA for short).
What are some examples of where this would be useful? Well, let’s say that you are creating a dashboard and want to display several different charts of data related to a stock chosen by the user. You can create these charts in Excel and display them using Excel Services, but you probably wouldn’t want the user to have to select the stock in each web part separately. Instead you’d want the user to be able to pick the stock once, and have all of the other web parts update.
Using SharePoint’s Choice or SQL Server Filter, and Excel Services, you can do just that. Another example might be that you have a report that you want to filter based on the region a particular SharePoint user is in. Using SharePoint’s Current User filter and Excel Services, you can do that too. The possibilities really are varied and numerous.
Creating a dashboard using Excel Services with Parameters and SharePoint Filters
So now I want to provide a bit of a walkthrough as to how you can create one of these dashboards yourself. In this case I have a simple workbook that contains a PivotTable of all my store’s sales data, and an associated PivotChart. I want to put both of these in a dashboard and have them be filtered by one central control that contains a list of the items I sell, so that if I select one or more items from the list, both the PivotTable and PivotChart will update to show me sales information related only to the selected items.
Creating the workbook and using a Data Connection Library
The first step is obviously to create the workbook. I have all of my data stored in an OLAP database, and I’ve exported my ODC file to a new document library in SharePoint this release called a Data Connection Library (DCL). This blog has talked about DCL’s before, but at a high level a DCL is a document library specifically made to store Excel and InfoPath external data connection files. By storing my ODC file here and referencing it directly from my workbook, I can have Excel Services also use it and by simply changing the ODC file in this one location, I can update the connection information automatically for every other workbook that uses it as well.
Defining a PivotTable Report Filter Parameter
Once I’ve got my workbook created, I need to define a parameter. In this case, I want to filter my PivotTable Report Filter that contains the list of items. Parameters have to be named ranges, so I first give a name to the PivotTable Report Filter (the actual cell that contains the filter). Now, to publish the workbook to the server and define a parameter at the same time, I choose Office Button > Publish > Excel Services. On the dialog that appears I can select where I want to save my workbook, and then I can click the Excel Services Options button. Here there is a Parameters tab where I can add in the named range that I just created as a parameter. Only named ranges that are valid to become parameters will show up as possible choices here.
Laying out the dashboard page
Once I’ve created my parameter and saved the workbook to the server, all I need to do is setup my dashboard the way that I want it to look. I can do all of this in my web browser without writing a line of code: itâ€™s all point and click. In this case, I’ve added two Excel Web Access web parts, and one SharePoint SQL Server Filter control.
Configuring the Excel Web Access web parts
First I’m going to setup the two EWA web parts. When I click the link within the leftmost EWA web part to open the tool pane, I can then either enter the path to the workbook that I just saved directly, or I can click on the “…” to the right of the text box to launch a file picker and select the file that way. I can also specify the name of the object that I want to display. I simply do this for both EWA web parts specifying ‘PivotTable1′ to display for the first, and ‘Chart 1′ to display for the second.
Configuring the SQL Server filter
The next thing I need to do is to configure the SQL Server filter. To do that, I click the link to open its configuration tool pane, and then browse to the same ODC file that my Excel workbook is using. Automatically, the filter displays all of the dimensions in the cube. If I select the same dimension my Report Filter is using, I’ll see all of the hierarchies in that dimension. After I do that, I can then select the same level that the Report Filter is using, give the web part a display name, and I’m good to go.
Connecting the filter control to the EWA web parts
The final thing that I need to do is to connect the SQL Server filter to each of my EWA web parts. To do that, I select the â€œeditâ€ dropdown at the top right of the filter control, and then select Connections > Send Filter Values To > (name of my EWA web part). A dialog will pop up, and if I select â€œGet Filter Values Fromâ€ and then click Configure, I’ll see a list of all of the parameters in the workbook that the EWA is currently displaying. I then select the parameter I want to send the filter value into, and click OK. That’s it. I do the same for the second EWA and I’m done. Note that you can also configure connections going the other way, by selecting Connections > Get Filter Values From on the EWA web parts.
Now, if I select a different item in the filter control, both of my EWA web parts will update.
This is just a simple example. SharePoint ships with many different filter controls including ones that can pass the current user or any known properties about them, one that can take parameters from the query string and pass them in, one that lets you enter a hard coded list of items filter (which is a great way to do data validation with Excel Services), and many more. This example was just one way that you can incorporate the business logic that you have in Excel into a dashboard and really integrate it into the whole experience.