Connect Filter Web Parts to Excel Web Access
Applies to: Microsoft Office SharePoint Server 2007
Print
Similar to other Web Parts that enable connections, you can use Filter Web Parts to pass values to the Microsoft Office Excel Web Access Web Part, and then change the view of data in the workbook based on the value. For example, you can select from a list of top-selling products in a Filter Web Part, and then simultaneously display the current inventory level as a Microsoft Office Excel table in one Microsoft Office Excel Web Access Web Part, a bar chart of sales revenue over the past five years in a second Excel Web Access Web Part, and a sales summary over the past five years in a PivotTable report in a third Excel Web Access Web Part. And all this based on the value that you selected from the Filter Web Part.
What do you want to do?
- Learn more about Filter Web Parts
- Connect a Choice Filter Web Part to an Excel Web Access Web Part
- Connect a SQL Server 2005 Analysis Services Filter Web Part to an Excel Web Access Web Part
Learn more about Filter Web Parts
The Filter Web Parts are very powerful and flexible, and enable you to do the following:
- Filter by a fixed value by entering text, numbers, or dates, or by selecting an item from a list of values.
- Derive the values to filter by manual data entry or from an external data source, such as a Windows SharePoint Services list, the Business Data Catalog, Microsoft SQL Server 2005 Analysis Services, or another Web Part on the same page.
- Filter a PivotTable report, in an Excel Web Access Web Part, that is based on data in a SQL Server 2005 Analysis Services dimension and hierarchy.
- Filter data based on a value in the Windows SharePoint Services profile properties for the current user, such as the current user's login name or the name of the person to whom the user reports.
- Display brief information about the current Filter Web Part connections on the Web Part in Edit Mode.
For more information, see the article Work with Filter Web Parts.
Top of Page
Connect a Choice Filter Web Part to an Excel Web Access Web Part
The following procedure uses the Choice Filter Web Part to connect to the Excel Web Access Web Part, to pass a parameter value, and to dynamically change the view of data in the workbook.
Preparation
Before you begin, you must do the following:
- Create an Excel workbook that has one or more cells, each defined as a named range, designed to accept values, such as a mortgage calculator that calculates a monthly payment based on the length of the loan in years.
- Publish the workbook to Excel Services, and make sure that you define a parameter for each cell that accepts values.
For more information, see the article Change workbook parameters in Excel Services.
Procedure
- To modify the Web Part Page, click Site Actions, and then click Edit Page.
The Web Part Page is displayed in Edit Mode.
- Add the Choice Filter Web Part and the Excel Web Access Web Part to a Web Part Page.
For more information, see the article Add or remove a Web Part.
- On the opening screen of the Choice Filter Web Part, click the link, Open the tool pane.
The Choice Filter Web Part enters Edit Mode, and the Web Part Tool Pane is displayed.
- Do the following:
- Enter a name in the Filter Name box to identify the Web Part.
- Enter the list of values that you want a user to be able to select from in the scrolling list and an optional description. For example, if you have a workbook that calculates a mortgage payment, and the cell that contains the length of the loan in years is defined as a parameter, you can enter 10, 15, 20, 25, and 30 as the list of values, each value on a separate line.
Tip You also have the option to add a description for each value on each line. For example:
10; 10 years
15; 15 years
and so on.
- Optionally, enter a default value in the Default Value box under the Advanced Filter Options section.
- To save the changes, click OK at the bottom of the Web Part Tool Pane.
- On the opening screen of Excel Web Access, click the link, Click here to open the tool pane.
The Excel Web Access Web Part enters Edit Mode, and the Web Part Tool Pane is displayed.
- Enter the location of the workbook that contains the Mortgage calculation in the Workbook text box.
To easily locate a workbook in a SharePoint library, click Select a Link , and use the Select a Link dialog box.
- From the Excel Web Access Web Part, click the Web Part menu , point to Connections, point to Get Filter Values From, and then click the name of the Choice Filter Web Part.
- Click Configure.
- In the Configure Connection dialog box, select the parameter from the Filter Parameter list, and then click Finish.
- To exit Web Part Page Edit Mode and view the page, at the top of the Web Part Page, under the Site Actions drop-down menu, click Exit Edit Mode.
- To dynamically change the workbook results in the Excel Web Access Web Part, in the Choice Filter Web Part, click Filter , click a value, and then click OK.
Note A Choice Filter Web Part has a limit of 1,000 items.
Top of Page
Connect a SQL Server 2005 Analysis Services Filter Web Part to an Excel Web Access Web Part
The following procedure uses the SQL Server 2005 Analysis Services Filter Web Part to connect to the Excel Web Access Web Part, to pass a parameter value, and to dynamically change the view of data in the workbook through a report filter.
Preparation
Before you begin, you must do the following:
- In an Excel workbook, create a PivotTable report that connects to a cube in a SQL Server 2005 Analysis Services database.
- Add a report filter to the PivotTable report that you created, and then create a defined name for the cell where the report filter is located.
For more information, see the article Change workbook parameters in Excel Services.
- Save the connection information as an Office Data Connection (ODC) file (.odc) to a SharePoint Data Connection Library that has been defined as trusted by an administrator.
- Publish the workbook to Excel Services, and make sure that you define parameters for each report filter.
For more information, see the Microsoft Office Excel Help system, and the article Roadmap for publishing an Excel workbook as "one version of the truth".
Procedure
- To modify the Web Part Page, click Site Actions , and then click Edit Page.
The Web Part Page is displayed in Edit Mode.
- Add the SQL Server 2005 Analysis Services Filter Web Part and the Excel Web Access Web Part to a Web Part Page.
For more information, see the article Add or remove a Web Part.
- In the SQL Server 2005 Analysis Services Filter Web Part, click the link, Open the tool pane.
The SQL Server 2005 Analysis Services Filter Web Part enters Edit Mode, and the Web Part Tool Pane is displayed.
- Do the following:
- Enter a name in the Filter Name box to identify the Web Part.
- Under Pick a data connection from, select A SharePoint Data Connection Library.
Note You can also select a data connection from a Key Performance Indicator (KPI) List Web Part on the page by selecting A Web Part on this page, and then by entering the KPI List Web Part name and its ODC file in the KPI List Web Part and Office Data Connection file boxes.
- For more information on KPIs, see the article Create and publish Key Performance Indicators (KPIs).
- Click the Browse button, and then in the Select a Link dialog box, locate the library and connection file that you previously saved.
- Select a dimension and hierarchy from the Dimension and Hierarchy boxes.
Important You must select the same Dimension and Hierarchy that are used in the report filter. For more information, see the Microsoft Office Excel Help system.
Note Optionally, you can select Encode selected values as a set. Selecting this check box passes the member unique names of the selected values delimitted by a semicolon (;) as a single string for use with Excel formulas that use the Cube functions. For more information, see the Office Excel Help system.
- To save the changes, click OK at the bottom of the Web Part Tool Pane.
- On the opening screen of Excel Web Access, click the link, Click here to open the tool pane.
The Excel Web Access Web Part enters Edit Mode, and the Web Part Tool Pane is displayed.
- Enter the location of the workbook that contains the PivotTable report in the Workbook text box.
To easily locate a workbook in a SharePoint library, click Select a Link , and use the Select a Link dialog box.
- From the Excel Web Access Web Part, click the Web Part menu , point to Connections, point to Get Filter Values From, and then click the name of the SQL Server 2005 Analysis Services Filter Web Part.
- Click Configure.
- In the Configure Connection dialog box, select the parameter that you defined for the report filter from the Filter Parameter list, and then click Finish.
- To exit Web Part Page Edit Mode and view the page, at the top of the Web Part Page, under the Site Actions drop-down menu, click Exit Edit Mode.
- To dynamically change the workbook results in the Excel Web Access Web Part, in the SQL Server 2005 Analysis Services Filter Web Part, select an item from the filter menu.
Note A SQL Server 2005 Analysis Services Filter Web Part has a limit of 1,000 items.
