Configuring BI Dashboards

After you have identified the content sources that provide your business intelligence data, you will proceed with creating dashboard pages that can be customized to create relationships between the data sources and allow filtering for users to see the slice of information that they need. This business intelligence scenario allows for users to see information in context so that they can take action.

Creating dashboard pages

The dashboard content type provides a page template that is designed to host business intelligence components and connectors. The page creation process prompts you to add or create KPI lists and is configured with two Excel Web Access Web Parts and a filter zone in the top of the left navigation page that efficiently uses the dashboard space. You can insert business intelligence components in any Web Part page, but the dashboard page provides a starting platform.

Adding the dashboard content type

If you create a library from the Reports Library template, you will create a library configured to store reports and dashboards. In addition, you can add the capability to create and store dashboards to other document libraries. For each document library in which you want to store dashboards, you need to add the dashboard content type to that library. To add the dashboard content types, follow these steps:

1.	Navigate to the document library to which you want to add the dashboard content type.
2.	Select Document Library Settings from the Settings menu.
3.	If you do not see the content type section, click Advanced Settings. Select Yes for Allow management of content types, and then click OK.
4.	In the content type section, click Add from existing site content types.
5.	Select Business Intelligence from the Select site content types from drop-down menu as shown in Figure 14.8. Figure 14.8. Adding the dashboard content type [View full size image]
6.	Select Dashboard Page and click Add.
7.	Click OK.

Creating a dashboard page

To create your dashboard page, follow these steps:

1.	Browse to your report library or other library with the dashboard content type.
2.	From the New menu in the top navigation bar, select Dashboard Page.
3.	Enter the file name and title of your dashboard page in the File Name and Page Title fields. Enter a description of your dashboard page if you want to display descriptive information at the top of your dashboard page.
4.	If you initiate the dashboard creation when in the reports library or document library, the location is automatically entered. If you initiated it from somewhere else in your Report Center site, select the document library and folder for your dashboard.
5.	For Create Link in Current Navigation Bar, select whether you want a link inserted into the Quick Launch navigation and select the section it will be in from the pull-down menu.
6.	Select your page layout in the Dashboard Layout section.
7.	In the Key Performance Indicators section, select whether you want to create a new KPI list, use an existing KPI list, or include no KPI list. You can add the KPI to your dashboard later if you decide to include KPIs as part of your dashboard.
8.	Click OK.

After you have created your dashboard page, populate it with Web Parts for the business data sources that you identified. The Web Parts should have a similar BI focus so that the page provides a perspective focused on a central topic.

For each portion of your dashboard, you can configure the action for that piece of data. In some cases, these actions do not require specific actions—presenting the data by itself is enough to cause a user action (like presenting popular add-ons for a customer service representative to suggest based on the item that the customer service representative has selected). For other business actions, you can configure a URL to process that action. These URLs will be configured to handle the action and associated results:

• An InfoPath forms page that interacts with business data in the database of the business application server.

• A custom dynamic page, such as a dynamic ASPX page, that you have designed to perform complex data interaction through Web services or other method of writing to the data source.

• A SharePoint list that is the source of the business data so that the user can update a value(s).

• A static page that displays a fixed result.

Adding filters to your dashboard

The capability to filter your dashboard pages allows you to provide information relevant to the user. This is necessary because there are many different perspectives that make up organizational intelligence depending on role, region, division, and so on, and purposes for using and reusing your corporate data.

There are several filter Web Parts provided with MOSS. These Web Parts either allow a user to select a value (such as a date, date range, or list value) or gather context information from the user and then provide these values to the other Web Parts on the page in which they are connected. Filters can not only be used to filter information but also to pass parameters or default values to and from Web Parts. The following context filters, or filters that are not visible on the page but calculate the value based on page, URL, or user attributes, can be configured to apply any of the following data types to the Web Parts that are configured to accept the value:

• Current user: Applies the current user value.

• Query string parameter: Applies the query string configured by the site owner to the Query String (URL) Filter Web Part.

• Page field value: Applies the page field value to the Page Field Filter Web Part so that the page is filtered based on the columns in the page list for the dashboard or any other Web Part page.

The filter Web Parts that are available to filter based on a user-entered value are as follows:

• Text Filter: Web Part that filters based on a user-entered text value.

• Date Filter: Web Part that filters based on user-entered date(s).

• Business Data Catalog Filter (the whole Web Part name): Filters based on values of properties that are in either SQL Server Reporting Services or the BDC.

• SQL Server 2005 Analysis Services Filter: Filters based on values from AS cubes or status indicators. These filters allow for hierarchical selection of data for easy use on a large, nested grouping of data.

• Choice Filter based on manually entered list: This filter is populated using the advanced filter properties of any filter Web Parts.

• SharePoint List Filter: Select a column from a SharePoint list to provide filter values.

The Web Parts that are able to accept filters are

• Excel Services: The Excel Services Web Part can accept filters and parameters provided by your filtered Web Part.

• KPI list: The KPI List Web Part can accept filters, but because KPIs are largely calculations or manually entered information, a logical filter relationship is hard to figure out. The most common scenario is to use an Analysis Services filter to modify a KPI that is based on that Analysis Services cube so that you will be filtering by a dimension that relates to your data.

• BDC Item and List: BDC data sources can be a source for a filter by using the BDC Filter Web Part and the BDC Item Web Part, and the BDC list Web Part can be connected to filters on the page as well.

• SharePoint List: SharePoint List and Library Web Parts can be connected to one filter Web Part. The list column that you want to filter on must be present in the SharePoint view.

• Report Viewer Web Part: A report that has parameters can be connected to as many filter Web Parts as appropriate to input those parameters.

• Data View Web Part: Filtering contributes an interesting dimension to the Data View Web Part, adding to the data access and configuration functionality this Web Part already provides. The Data View Web Part can accept multiple filters or parameters provided by connected filter Web Parts. This allows a wide range of functionality for any data source, but particularly with SharePoint list and library data because the native library and list Web Parts can accept only one filter.

Adding the Current User filter

The Current User filter allows you to pass the user credentials or a profile property to other Web Parts on the page. The current user credentials provide good filtering options if you want to customize views to items that were created by, modified by, or assigned to that alias, especially for SharePoint lists. For example, you can use this filter to create a Tasks list or document list that only shows the user their items. The profile properties allow you to target information based on information stored in the SharePoint profile so that you can use options such as Title, Office, or Manager to generalize the filtering to roles or locations.

To add the Current User filter to your dashboard, follow these steps:

1.	Navigate to your dashboard page in a browser and select Edit Page from the Site Actions menu in the top-right corner.
2.	Select Add a Filter in the Filter Zone in the top of the left navigation column.
3.	Select Current User Filter and click Add.
4.	From the edit menu on the filter Web Part, select Modify Shared Web Part.
5.	Select whether you would like to use the current user name or a profile field for that user in the Select value to provide field. If you use a SharePoint profile value, select the value from the list of profile values such as Office, Region, Manager, or Title.
6.	Click OK.

Adding the Query String (URL) filter

The Query String filter allows you to define fields that will filter values passed in the URL. For example, if you define a query string for bill rate, you can pass the value you want to use for bill rate in the URL in the format http://<servername>/<sitename>/<libraryname>/<pagename>?billrate=275. The Web Parts that consume that filter will use 275 for the bill rate, as shown in Figure 14.9.

This is a very useful context filter because you can send any parameter value that you want to the list without changing anything on the dashboard page or a list/location that hosts the values. However, you will need to customize the referrer URL to send the right value.

To add the Query String filter to your dashboard, follow these steps:

1.	Navigate to your dashboard page in a browser and select Edit Page from the Site Actions menu in the top right corner.
2.	Select Add a Filter in the Filter Zone in the top of the left navigation column.
3.	Select Query String (URL) Filter and click Add.
4.	From the edit menu on the filter Web Part, select Modify Shared Web Part.
5.	Enter the name for your parameter in the Query String Parameter Name field. This is the name you will append to the URL with the value (see Figure 14.10). Figure 14.10. Configuring the parameter name for the Query String filter
6.	Enter the value you want to use if no parameter is passed in the Default Value field.
7.	Select the behavior you want to use in the Advanced Filter options. Select Send Empty if there are no values if you want to send a NULL value if the parameter is not attached. If you do not have a default value configured and no parameter is attached to the URL, this option returns only items with a NULL value (if any). If you do not have a default value and do not select this option, the filter Web Part will not send anything to the connected Web Parts. In the When handling multiple values field: a. Select Send only the first value if you want the filter to ignore any values after the first. b. Select Send all values. c. Select Combine all values into a single value and delimit with. If you select either of the options that send more than one value, the Web Parts that you filter must be able to handle more than one value. For example, a SharePoint list Web Part cannot handle multiple values and will only return the items that match the first value submitted.
8.	Click OK.

Adding the Page Field filter

The Page Field filter is a context filter that passes an attribute that is set on the page to the connected Web Parts. For example, if you are creating a BI page for sales in every region, you can create a Region column in the document library, set the appropriate region for each page, and then pass that filter to the Web Parts that have similar fields so that you have a regional view of sales.

To add the Page Field filter to your dashboard, follow these steps:

1.	Navigate to your dashboard page in a browser and select Edit Page from the Site Actions menu in the top right corner.
2.	Select Add a Filter in the Filter Zone in the top of the left navigation column.
3.	Select Page Field Filter and click Add.
4.	From the edit menu on the filter Web Part, select Modify Shared Web Part.
5.	In the Select a column from the list in which the current page is stored field, select the column that contains the page value you want to pass to the Web Parts that will be filtered.
6.	Click OK.

Adding the Business Data Catalog filter

To add the Business Data Catalog filter to your dashboard, follow these steps:

1.	Navigate to your dashboard page in a browser and select Edit Page from the Site Actions menu in the top-right corner.
2.	Select Add a Filter in the Filter Zone in the top of the left navigation column.
3.	Select Business Data Catalog Filter and click Add.
4.	From the edit menu on the filter Web Part, select Modify Shared Web Part.
5.	Enter a name for your Business Data Catalog filter in the Filter Name field so that users know what type of selection they are choosing.
6.	Pick or enter your BDC entity in the Entity field.
7.	Select the column that provides the value options from the Value Column drop-down as shown in Figure 14.11. Figure 14.11. Selecting your entity and value field for a BDC filter
8.	Select the column that provides the descriptions from the Description Column dropdown.
9.	Select how you want the list to appear. For most BDC applications, you will display the list as a Search dialog box unless you have defined additional find methods.
10.	Click OK.

Adding the Choice filter

The Choice filter allows you to enter the values you want presented to users as filter choices in the filter Web Part configuration. This gives you flexibility to customize the values on your page; however, only users with permissions to design the page will be allowed. If you want a filter to which you can grant permissions to update values without allowing page design, you can create a SharePoint list with the values and use the SharePoint List Filter Web Part.

To add the Choice filter to your dashboard, follow these steps:

1.	Navigate to your dashboard page in a browser and select Edit Page from the Site Actions menu in the top right corner.
2.	Select Add a Filter in the Filter Zone in the top of the left navigation column.
3.	Select Choice Filter and click Add.
4.	From the edit menu on the filter Web Part, select Modify Shared Web Part.
5.	Enter a name for your Choice filter in the Filter Name field so that users know what type of selection they are choosing.
6.	Enter your choices values in the Type each choice on a separate line field. If you want to display a description instead of the value, you can enter the description following the value separated by a semicolon, as shown in Figure 14.12. Figure 14.12. Entering Choice filter values In the case of the choices entered in Figure 14.12, the choices will appear, as shown in Figure 14.13 but will send the value Northwest if the user picks the description WA, CA, ID, OR, and AK. Figure 14.13. Filter value display for a choice entered with a description
7.	Expand the Advanced Filter section and select whether you want to require users to pick a value by selecting the Require user to choose a value field. If desired, enter a default value in the Default Value field. Select Show “(Empty)” value if you want to present users with the option not to send a NULL value. Select Allow multiple selections if you want users to be able to pick more than one value. If you allow this option, the Web Parts that you filter must be able to handle more than one value. For example, a SharePoint list Web Part cannot handle multiple values and will only return the items that match the first value submitted.
8.	Click OK.

Adding the Date filter

The Date filter sends a date value to the connected Web Parts and allows users to either enter the date or pick from a calendar control. For connected Web Parts that have multiple date fields defined, like a report that defines both a start date and end date parameter, add multiple date filters to your page and connect to the associated parameter fields. To add the date filter to your dashboard, follow these steps:

1.	Navigate to your dashboard page in a browser and select Edit Page from the Site Actions menu in the top right corner.
2.	Select Add a Filter in the Filter Zone in the top of the left navigation column.
3.	Select Date Filter and click Add.
4.	From the edit menu on the filter Web Part, select Modify Shared Web Part.
5.	Enter a name in the Filter Name field so that users know what type of date they are entering.
6.	Pick the type of default value that you would like to show—none, a specific date, or a date that is offset days before or after the date the filter is used. This field is helpful so that you can suggest relevant values to your users.
7.	Click OK.

For Web Parts that are not configured for multiple parameters such as SharePoint lists, the date field provides a value that will be exactly matched to the connected field.

Adding the SharePoint List filter

The SharePoint List filter allows you to reuse values in a SharePoint list. This is very helpful because you can distribute management of filter values to all users that have permissions on the SharePoint list, and also reuse that list as a lookup field on other lists.

To add the SharePoint List filter to your dashboard, follow these steps:

1.	Navigate to your dashboard page in a browser and select Edit Page from the Site Actions menu in the top right corner.
2.	Select Add a Filter in the Filter Zone in the top of the left navigation column.
3.	Select SharePoint List Filter and click Add.
4.	From the edit menu on the filter Web Part, select Modify Shared Web Part.
5.	Enter the name in the Filter Name field so that users know what type of choice they are making.
6.	Enter the URL or browse to the SharePoint list in the List field. If you are using the browse tool, you may need to page between lists by using the incredibly small caret icon next to the number of items displayed at the top of the selection list, as shown in Figure 14.14. Figure 14.14. Control for paging between select items [View full size image]
7.	Select the view on which you would like the choices to be based from the View field drop-down menu.
8.	Select the column that contains the filter values from the Value field dropdown. The field must be a single line of text field.
9.	Select the columns that contain the description values in the Description field drop-down if you want to present the description name as the option name to the user instead of the value. The field must be a single line of text field.
10.	Click OK.

Adding the SQL Server 2005 Analysis Services filter

The Analysis Services filter provides options for you to filter Analysis Services data in Web Parts and KPIs that are based on the AS cube. To add the SQL Server 2005 Analysis Services filter to your dashboard, follow these steps:

1.	Navigate to your dashboard page in a browser and select Edit Page from the Site Actions menu in the top-right corner.
2.	Select Add a Filter in the Filter Zone in the top of the left navigation column.
3.	Select SQL Server 2005 Analysis Services Filter and click Add.
4.	From the edit menu on the filter Web Part, select Modify Shared Web Part.
5.	Enter a name for the filter in the Filter Name field so that users know what type of choice they are making.
6.	In the Pick a data connection from section, select whether you will use a data connection that is already being used on the page, or specifying a data connection from a data connection library. If you choose a data connection for a Web Part on the page, SharePoint automatically connects the Web Parts when you save the AS filter.
7.	Pick the dimension from the Dimension dropdown and the hierarchy from the Hierarchy drop-down list, as shown in Figure 14.15. Figure 14.15. Selecting your dimension and hierarchy for the AS filter
8.	Expand the Advanced Filter Options section and select whether you want to require that the users pick a value by selecting the Require user to choose a value field. If desired, enter a default value in the Default Value field.
9.	Click OK.

Adding the Text filter

The very versatile Text filter allows the user to enter a single line of text to filter the contents of the page. This flexibility means that it does not do any value checking. The text filter can be used for entering numbers as well. To add the text filter to your dashboard, follow these steps:

1.	Navigate to your dashboard page in a browser and select Edit Page from the Site Actions menu in the top right corner.
2.	Select Add a Filter in the Filter Zone in the top of the left navigation column.
3.	Select Text Filter and click Add.
4.	From the edit menu on the filter Web Part, select Modify Shared Web Part.
5.	Enter a name for the filter in the Filter Name field so that users know what type of text they are entering.
6.	Expand the Advanced Filter Options section and specify the maximum number of characters for the Maximum number of characters text field. Select whether you want to require that users pick a value by selecting the Require user to choose a value field. If desired, enter a default value in the Default Value field.
7.	Click OK.

Adding the Filter Actions Web Part

If you are adding your filters on a page that is not a dashboard page, you should add the Filter Actions Web Part so that users can select the Apply Filters option when they have selected their filter values. To add the Filter Actions Web Part, follow these steps:

1.	Open the page on which you want to connect the filter Web Part and select Edit Page from the Site Actions menu in the top-right corner.
2.	In the zone that you want to add the filter actions, select Add Web Part.
3.	Select Filter Actions and click Add.
4.	From the edit menu on the filter Web Part, select Modify Shared Web Part.
5.	Select how you want the Apply Filters button to align in the Button Alignment drop-down menu, as shown in Figure 14.16. Figure 14.16. Configuring the button alignment for the Filter Actions Web Part
6.	Select whether you want users to be able to save their filter values as default values in the Allow users to save filter choices field.
7.	Click OK.

Connecting filters to your data sources

For the filters to be useful, they must be connected to the Web Parts on the page they will pass filter parameters to, or get default values to or from. Filters can be connected to multiple Web Parts, although some Web Parts, such as a SharePoint list, are not able to receive more than one connection.

To connect the filter Web Part(s), follow these steps. In our example, we are connecting a date filter to a Report Viewer Web Part.

1.	Open the page on which you want to connect the filter Web Part and select Edit Page from the Site Actions menu in the top right corner.
2.	Select Connections Send Filter Values To [Your Target Web Part] from the edit menu of the filter Web Part, as shown in Figure 14.17. Figure 14.17. Configuring the filter Web Part connections [View full size image]
3.	Select the filter parameter that you want to connect to from the Configure Connection dialog box, as shown in Figure 14.18. Figure 14.18. Select the report parameter to which the filter is connected.
4.	Click Finish.
5.	Exit Edit Mode for your page.

Creating advanced filter relationships

When you are connecting a filter within SharePoint, you only have the ability to connect fields that match. There is no flexibility within that interface to create complex relationships, like connecting two date filters to a SharePoint list, so that users can enter a start date and end date for which they want to see entries in the list returned. To create an advanced relationship like that, you need to configure that logic within the source you are using for the data.

The method for creating the logic varies based on the data source. For reporting services reports, you build the report parameters into the query and define the parameters in the report definition. In the case of SharePoint lists, you can create a Data View Web Part to show the list data and filter parameters. The Data View Web Part can accept multiple Web Part connections so that you work around the limitation of a SharePoint list being able to accept only one connection.

For an example of creating an advanced relationship, we will use the scenario of creating a Data View Web Part for a list and defining the parameters within that Web Part. To create this functionality, follow these steps:

1.	Open the dashboard page on which you want to insert the Data View Web Part and advanced filter relationship in SharePoint Designer.
2.	Select the Web Part zone in which you want to add your Data View Web Part.
3.	From the Data View menu, select Insert Data View.
4.	From the Data Source Library in the right task pane, click the list that you want to use for the data view and select Show Data.
5.	Select the fields that you want to insert in your data view. You can press and hold Ctrl and select multiple fields.
6.	At the top of the right task pane, select Multiple Items View from the Insert Selected Fields as menu, as shown in Figure 14.19. Figure 14.19. Inserting the data view fields
7.	Click the > icon at the right of the Data View Web Part and select Filter from the Common Data View Tasks menu.
8.	Select the field name for which you want to create the filter relationship and the comparison method in the Filter Criteria dialog box. Choose Create a new parameter in the value field drop-down list, as shown in Figure 14.20. Figure 14.20. Adding filter criterion
9.	In the Data View Parameters dialog box, enter the name for your parameter. as shown in Figure 14.21. Click OK. Figure 14.21. Naming your filter parameter [View full size image]
10.	Enter any additional filter criterion as shown in Figure 14.22. Click OK when all filter criteria have been added. Figure 14.22. The filter dialog with filter criteria
11.	Save your dashboard page and exit SharePoint Designer.
12.	Using your Internet browser, navigate to the dashboard page with the Data View Web Part and select Edit Page from the Site Actions menu in the top right corner.
13.	Add the filters that you want to connect to your Data View Web Part by clicking Add Filters in the Filter Web Part zone, select your filter(s) from the list, and click Add. For our example, we will add two Date Filter Web Parts.
14.	From the Edit menu on your filter Web Parts, select Modify Shared Web Part and configure the filter name and default value. For our example, we name our filters Start Date and End Date and configure the Start Date to be 30 days before [Today] and the End Date to be [Today]. Click OK.
15.	Connect your Web Part(s) by selecting Connections Send Filter Values To <Data View Web Part Name>.
16.	Select Get Parameters From in the Choose Connection dialog box.
17.	Select the Data View Web Part parameter name that you want to connect the filter to, as shown in Figure 14.23. Click Finish. Figure 14.23. Selecting the Data View Web Part parameter to match to the filter Web Part
18.	Repeat step 17 for any additional Web Parts that you want to connect.
19.	Click Publish on the dashboard page to exit edit mode.
20.	Enter the date parameters that you want and click Apply Filters to test the Web Part connections. The result should appear like the dashboard page shown in Figure 14.24. Figure 14.24. Dashboard page with Data View Web Part of a SharePoint list connected to two date Web Parts [View full size image]