To keep track of your quarterly sales data stored in an Excel workbook, you can create a dashboard with a Text Filter Web Part. A dashboard is a feature in Microsoft Office SharePoint Server 2007 that shows business intelligence data in a dynamic manner. To make sure that your Excel workbook is the definitive source for sales data, you can publish the workbook as a report in a Report Center. The Report Center is the central location to store, retrieve, and change shared reports. This article describes how to create both the dashboard and the Report Center.
As a sales manager, you are constantly looking at and updating your sales data. Your sales data might be stored in a Excel workbook on your computer. The data for each quarter is in a worksheet in the workbook. Since you maintain sales data workbook, you spend lots of time responding to requests for sales data from other people. Additionally, when you get new data, you have to update your workbook and then tell other people that you updated the workbook.
Instead of having to update and distribute the sales data, you can create a Report Center in a site collection on a server that is running Office SharePoint Server 2007. Publishing your workbooks as reports in a Report Center consolidates the business intelligence data in a central location. With all your reports in a Report Center, you can show the business data in one or more personalized dashboards.
You can learn more about the Report Center in the article, Working with a Report Center site.
After you publish your workbook as a report in the Report Center, you can then create a dashboard to show the data in this report. The dashboard uses a Text Filter Web Part to show the sales data for each quarter. You type the quarter you want to see and the dashboard displays the sales data for that quarter.
The Type Quarter to View box is a Text Filter Web Part that shows the sales data for that quarter.
To complete this scenario, you will need the following:
- A server that is running Office SharePoint Server 2007 with Excel Services enabled.
- Permissions to contribute to a site collection on that server. Permissions are usually set by the site administrator.
- Microsoft Office Excel 2007 that is included with Microsoft Office Professional Plus 2007 or Microsoft Office Enterprise 2007.
You will do the following:
- Create the Report Center — you will create a Report Center that will store the Excel workbook with the sales data. The Report Center will be the central location to store, retrieve, change the business intelligence data. The dashboard you will create in a later step and any other dashboard will get business data from the Report Center. If you already have an existing Report Center, you can go to the next step.
- Create the Report — if you do not have an Excel workbook with your sales data, you will create the workbook in this step. If you have an existing workbook, you will configure it in this step by creating names for ranges of cells. The ranges of cells will appear in the dashboard that you will create in a later step. If you already have an existing workbook with names for cell ranges, you can go to the next step.
- Publish the Report to the Report Center — you will publish this workbook as a report in the Report Center.
- Create the Dashboard — you will create a dashboard that contains an Microsoft Office Excel Web Access Web Part and a section for Filter Web Parts. The dashboard will show the sales data in the report you published to the Report Center in an earlier step. After you create the dashboard, you will configure the Microsoft Office Excel Web Access Web Part to show the sales data report.
- Add the Text Filter Web Part — you will add this Web Part to the dashboard. The Text Filter Web Part lets you specify the name of the range of cells in the report to show in the dashboard. After you add this Web Part to the dashboard, you will connect it to the Microsoft Office Excel Web Access Web Part that shows the report.
- Use the Dashboard — you will learn how to show the different views of the report.
Step 1: Create the Report Center
Click View All Site Content, and then click Create on the All Site Content page.
Tip In most cases, you can use the Site Actions menu instead to complete this step.
- Under Libraries in the Create page, click Report Library.
- In the Name box in the Name and Description section of the New page, type Sales Report Center. This name will be the title of the Report Center.
- Click Yes for Display this document library on the Quick Launch. This options adds a link to the Quick Launch section of the Adventure Works site so that anyone can easily browse to the Report Center.
- Click Yes for Create a version each time your edit a file in this document library. This option will require anyone who modifies a report in the Report Center to first check out a report. You will then know who modified this report.
- Click OK.
Step 2: Create the Report
Create an Excel workbook with the data that you want to show in the dashboard. After you create the workbook, create names for ranges of cells in the workbook that you want to show in the dashboard..
For example, you can define a range of cells in each worksheet that shows the sales data by quarter. This article uses the following names for cell ranges in each worksheet:
||Sheet in Workbook
Here are two examples of the four cell ranges with names in the workbook used in this article. Each cell range is in a different worksheet in the workbook.
A range of cells in the Q1 worksheet has the name First.
Name of the range
Range of cells in the worksheet
Worksheet in the workbook
A range of cells in the Q2 worksheet has the name Second.
Name of the range
Range of cells in the worksheet
Worksheet in the workbook
To create a name for a range of cells:
- Select the cell, range of cells, or nonadjacent selections (nonadjacent selection: A selection of two or more cells or ranges that don't touch each other. When plotting nonadjacent selections in a chart, make sure that the combined selections form a rectangular shape.) that you want to name.
- Click the Name box at the left end of the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.).
- Type the name that you want to use to refer to your selection. Names can be up to 255 characters long.
- Press ENTER.
- Repeat these steps to create names for other ranges of cells.
Note You cannot name a cell while you are changing the contents of the cell.
To learn other ways to create a name for a range of cells, see Define and use names in formulas.
Step 3: Publish the Report to the Report Center
- Click the Microsoft Office Button, point to Publish, and then click Excel Services under Distribute the document to other people.
- In the File name box of the Save As dialog box, enter the path of the server and the file name. For example, to save the file Sales Data.xlsx to the Reports Library in the Reports Center site, in the AdventureWorks top-level site, type: http://www.adventure-works01.com/Reports/ReportsLibrary/Sales Data.xlsx.
Important If you copy and paste the URL for the Report Center directly from the browser into the file name box, you must remove the extra characters that prevent you from copying a file to the destination library. In this example, the extra characters in the URL are Forms/current.aspx.
- Click Excel Services Options.
- On the Show tab of the Excel Services Options dialog box, select the check boxes for the worksheets that you want to show in the dashboard and then click OK. In the example workbook, the sales data for each quarter is in a worksheet so that you would select the Q1, Q2, Q3, and Q4 check boxes.
- In the File name box, type the name of the workbook. Select the Open in Excel Services check box.
- Click Save.
- In the Document Type list of the Choose Document Type dialog box, click Report, and then click OK.
The workbook appears in a browser.
Step 4: Create the Dashboard
- Go to the home page of your site collection.
- In the Quick Launch section of the Report Center, click Dashboards.
- Click the Open arrow next to New and then click Dashboard Page.
- In the New Dashboard page, type the following in the specified box:
||Type this text
||The URL of the dashboard page.
||Appears as the title of the page.
||Quarterly breakdown of sales by type
||Appears as the description of the page in the Reports Library
- In the Key Performance Indicators section, select Do not add a KPI list to this dashboard and then click OK.
- On the Microsoft Office Excel Web Access Web Part, click Click here to open the tool pane.
The tool pane for the Microsoft Office Excel Web Access Web Part opens so that you can specify which report in the Report Center that you want to show in this Web Part.
- In the tool pane for the Microsoft Office Excel Web Access Web Part, under Workbook, click Select a Link .
- In the Select a Link -- Webpage Dialog page, browse to the Reports Library folder, click the report, and then click OK.
- In the tool pane for the Microsoft Office Excel Web Access Web Part, click Apply.
The report you selected will appear in the Microsoft Office Excel Web Access Web Part.
Step 5: Add the Text Filter Web Part
- In the Filter Zone on the left side of the dashboard page, click Add a Filter.
- In the Add Web Parts -- Webpage Dialog page, open All Web Parts.
- Under Filters, click Text Filter, and then click Add.
The Text Filter Web Part appears in the Filter Zone on the left side of the dashboard page.
- On the Text Filter Web Part, click the Edit button , and then click Modify Shared Web Part.
The tool pane opens so that you can configure the properties of this Web Part.
- In the Filter Name box, type Type Quarter to View.
- In the Title box, type Filter: Filter by Quarter.
- Click Apply.
- To connect the Text Filter Web Part to the Microsoft Office Excel Web Access Web Part, click the Edit button on the Text Filter Web Part, point to Connections, point to Send Filter Values To, and then click Sales by Quarter.
- In the Connection Type list, click Get Named Item From, and then click Finish.
- Click Save and Stop Editing.
- Click Publish.
Step 6: Use the Dashboard
In the Text Filter Web Part, type a name for a range of cells in the report published to the Report Center and then press ENTER. The Microsoft Office Excel Web Access Web Part will show the range of cells with that name.
Top of Page