Microsoft Office Tutorials and References
In Depth Information
Working with SQL Server Analysis Services
Before you can publish the workbook to the server, you must define a workbook parameter that you
can use to filter the workbook on the dashboard. You also need to hide the actual Report Field on the
PivotTable so that the only way users can change the date is through the filter on the dashboard.
Try It Out Defining a Filter Parameter
To define a parameter to filter the workbook on the dashboard, follow these steps:
In the workbook you created in the previous Try It Out, hide the row that contains the PivotTable
report field and the row below it. Assuming you placed your PivotTable on A1, these should be
rows 1 and 2.
Open the Publish dialog box by selecting Office
Click the Excel Services Options button.
In the Excel Services Options dialog box, select Show Items In This Workbook.
Select the Internet Sales PivotTable.
Switch to the Parameters tab.
Click the Add button to add a parameter.
Select the Date parameter and click OK to close the Add Parameter dialog box.
Click OK to close the Excel Services dialog box.
Locate the report library in the Sales Report Center that you created earlier and save the
workbook there. Name the workbook Adventure Works Sales.xlsx .
Click the Save button to save the workbook.
Leave the Document Type as Report and click OK.
The workbook should open in the browser (assuming you kept the option for opening with
Excel Services checked). Depending on the settings in on your server, you may be prompted to
allow the external data query to occur. If so, click OK. (This happens as soon as the workbook
opens on the server because you set it to refresh on open.)
You now have a workbook that connects to and queries Analysis Services data on the server. You should
be able to drill and filter the data in the browser. Each time you do this, Excel Services queries Analysis
Services and the PivotTable is updated. Next, you place this workbook in a dashboard and hook it up to
a filter part.
Building a Data-Bound Dashboard
The dashboard you are going to build will include the workbook you authored in the previous section, a
KPI that takes data from the same Analysis Services cube, and a filter part for filtering both the KPI and
the workbook based on the data period. In the first Try It Out in this section, you create the dashboard
in the Sales Report Center site that you have been using and add the workbook to it.