• Article

Tutorial: Adding a KPI to Your Report (Report Builder 2.0)

A key performance indicator (KPI) is a 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:

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

  2. Use a gauge to display the state of the KPI.

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

In this tutorial, you will learn to add a KPI by using approaches 1 and 2.

In order to define the current state of a KPI, we will create a business requirement based on the AdventureWorks2008 sample database and add it to the report that was created in Tutorial: Creating a Basic Table Report (Report Builder 2.0).

Suppose the KPI is based on the sales order total, which is the aggregate for Line Total for each order. The KPI is designed to highlight an order total in the follow way:

  • An order total over 100 requires immediate handling.

  • An order total over 25 but under 100 requires special handling.

  • An order total under 25 requires no extra attention.

Requirements

You must have the following prerequisites to complete this tutorial:

Estimated time to complete this tutorial: 10 minutes.

To open an existing report

  1. Click Start, point to Programs, point to Microsoft SQL Server 2008 Report Builder, and then click Report Builder 2.0.

  2. From the Report Builder button, click Open.

  3. Navigate to Sales Order on the report server.

Next, you will set the background color of a cell in the table to an expression that is based on the value that is displayed in the cell when the report is processed.

To display the present state of a KPI by using background colors

  1. In the table, right-click two cells down from the Product cell (in the subtotal row that displays the total for each order), and the click Text Box Properties.

  2. In Fill, click the fx button, then type the following expression in the Set expression for: BackgroundColor field:

    =IIF(Sum(Fields!LineTotal.Value) >= 100, "Violet", IIF(Sum(Fields!LineTotal.Value) < 25, "Transparent", "Cornsilk"))

    This changes the background color to violet for each cell that contains an aggregated sum for Line Total greater than or equal to 100. All values between 25 and 100 are colored using the shade of yellow named "Cornsilk". Values under 25 are not highlighted.

  3. Click OK.

  4. Click Run to preview the report.

    In the subtotal row that displays the total for each order, the background color of the cell changes depending on value of the aggregated sum.

Next, you will add a column and a gauge to display the state of a KPI.

To display the present state of a KPI using a gauge

  1. Switch to Design view.

  2. In the table, right-click the column handler for the cell that you changed in the previous procedure, point to Insert Column, and then click Right. A new column is added to the table.

  3. On the Insert tab, in the Data Regions group, click Gauge, and then click on the design surface outside the table. The Select Gauge Type dialog appears.

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

  5. Click OK.

    A gauge is added to the design surface.

  6. From the Report Data pane, drag LineTotal to the gauge.

    When you drop the field onto the gauge, the field is aggregated using the built-in SUM function.

  7. Right-click the gauge, point to Gauge, and then click Pointer Properties.

  8. In Pointer Type, select Bar. This changes the pointer from a marker to a bar that will be more visible when the gauge is added to the table.

  9. Click Pointer Fill. In Secondary Color, pick Yellow. The gradient fill pattern will change from white to yellow.

  10. Drag the gauge inside the table into the third cell of the column that you inserted.

    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.

  11. Click Run to preview the report.

    The horizontal length of the bar in the gauge changes depending on the value of the KPI.

  12. (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 linear 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.

    5. Set BorderColor to Lime.