• Article

Tutorial: Adding a KPI to a Report

A key performance indicator (KPI) is any measurable value in your report that has business significance, for example, Total Sales. If you want to display the current state of a KPI on a report, you can use one of three different approaches:

  • Highlight cell values using a background color that shows the state of the KPI.

  • Replace cell values with an image that shows the state of the KPI.

  • Insert a gauge.

In order to define the current state of a KPI, we will create a business requirement based on the AdventureWorks sample database. Suppose Line Total is our KPI. When Line Total goes lower than a value of 30, our requirement is to draw attention to the KPI as a possible source of concern. When Line Total goes lower than a value of 20, our requirement is to draw attention to the value as a source of concern that must be addressed immediately.

Requirements

To use this tutorial, the system must have the following installed:

  • SQL Server Business Intelligence Development Studio.

    Note

    Business Intelligence (BI) Development Studio is not supported on Itanium-based computers. However, support for BI Development Studio is available for x64-based computers. If the SQL Server sample databases have been deployed on an Itanium-based computer, use BI Development Studio on either an x86-based or x64-based computer to modify and run the samples.

  • SQL Server 2008 with the AdventureWorks sample database. SQL Server samples and sample databases must be downloaded and installed before you can view or work with them. For more information, see Considerations for Installing SQL Server Samples and Sample Databases. To read about support for SQL Server sample databases and sample code for SQL Server Express, see Databases and Samples Overview on the CodePlex Web site.

  • You must have completed all lessons in the Tutorial: Creating a Basic Table Report.

Estimated time to complete this tutorial: 10 minutes.

To open an existing project

  1. Click Start, point to Programs, point to Microsoft SQL Server 2008, and then click Business Intelligence Development Studio.

  2. On the File menu, point to Open, and then click Project.

  3. Navigate to Tutorial.sln. This is the tutorial that was created in Tutorial: Creating a Basic Table Report.

  4. Click OK to open the project. The Tutorial project is displayed in Solution Explorer with one report called Sales Orders.rdl.

  5. Double-click on the Sales Orders.rdl file to open this report.

To display the present state of a KPI using background colors

  1. In the table, right-click the cell that contains the field [LineTotal], and select Text Box Properties.

  2. In Fill, type the following expression:

    =IIF(Sum(Fields!LineTotal.Value) >= 30, "Transparent", IIF(Sum(Fields!LineTotal.Value) < 20, "Red", "Yellow"))

This will color the background to red for each cell that contains a value for Line Total less than 20. All values between 20 and 30 will be colored using Yellow and values over 30 will not be highlighted.

To display the present state of a KPI using a gauge

  1. In the table, right-click the cell that contains the field [LineTotal], point to Insert Column and select After to insert a new column to the right of [LineTotal].

  2. Click the Design tab.

  3. In the Toolbox, click Gauge, and then click on the design surface outside the table. The Select a Gauge Type dialog appears.

    Note

    The Toolbox may appear as a tab on the left side of the Report Data pane. To open the Toolbox, move the pointer over the Toolbox tab. If the Toolbox is not visible, from the View menu, click Toolbox.

  4. Click Linear. The first linear gauge is selected.

  5. Click OK.

  6. Drag the LineTotal field from the Report Data pane to the gauge.

When you drop the field onto the gauge, the field is aggregated using the built-in SUM function. For more information, see Adding Data to a Gauge Data Region.

  1. Drag the gauge inside the table into the column that was inserted in Step 1.

    Note

    You may need to resize the column so that the horizontal linear gauge fits into the cell. To resize the column, click on a column header and use the handles to resize the cells horizontally and vertically. For more information, see Working with Tablix Data Regions.

  2. Click Preview.

  3. (Optional) Add a maximum pin to handle overflow so that any value over the scale maximum always points to the maximum pin:

    1. Open the Properties pane.

    2. Click on the scale. The properties for the scale are displayed in the Properties pane.

    3. In the Scale Pins category, expand the MaximumPin node.

    4. Set the Enable property to True. A pin appears after the maximum value on the scale.