• Article

Lesson 3: Adding a Matrix, Variables, and Indicators as KPIs (SSRS)

In this lesson, learn to add report items to the Employee_Sales_Summary_2008R2 report that help answer the following question for an Adventure Works salesperson:

  • Am I meeting my overall sales quota?

  • In which product categories am I meeting my quota?

Employee_Sales_Summary_2008R2 is a master report that displays sales data summaries. You will add a drillthrough action on the sales order number that a user can click to open a separate report that shows all the details for a specific sales order.

What You Will Learn

In this tutorial you will to do the following tasks:

  • Add a matrix with row groups based on product category and sales order number.

  • Add an expand/collapse toggle for rows associated with the child group based on sales order number.

  • Add a boolean parameter that enables a user to control whether all toggle rows display or hide when the report first runs. By default, you will configure the toggle state to be collapsed.

  • Add a drillthrough action on the sales order number. You will create the target report in a later tutorial. For more information, see Creating the Sales_Order_Detail_2008R2 Report (SSRS).

  • Add a dataset with a calculated field to define sales quota amounts for each category, and a multivalue parameter that is populated by this dataset.

  • Add the following two indicators to the table to represent KPIs (Key Performance Indicators):

    • Quota met for all categories. The indicator is scoped at the matrix level and displays whether sales in all categories exceed the quota for the month.

    • Quota met for each individual category. The indicator is scoped at the category group level and displays a state based on whether sales fall within a percentage range of the goal.

    You can interactively change category quotas by changing the parameter values to see how the state of each indicator changes.

  • Define report and group level variables to aid in writing complex expressions.

  • As a design and debugging aid, display multiple calculation results in a text box. By setting the text box visibility based on a parameter, you enable a user to control whether to display this optional information.

  • Add a rectangle that contains multiple report items to the tablix corner cell.

  • Add a bookmark link from the scenario text to the matrix.

  • Add explanatory text that describes the purpose of the matrix.

Estimated time to complete this tutorial: 30 minutes.

Requirements

For information about requirements, see Prerequisites for AdventureWorks 2008R2 Sample Reports (SSRS).

This lesson assumes that you have completed Creating the Report Server Project and the AdventureWorks2008R2_Base Report (SSRS) to create the report project, shared data source, and shared datasets, and Lesson 2: Adding Line and Sparkline Charts (SSRS).

Tips

Review the tips about viewing the hierarchical relationships among report items by using Document Outline pane. For more information, see Report Design Tips (Report Builder 3.0 and SSRS).

To open the project and report

  1. In Business Intelligence Development Studio, open the report server project AdventureWorks 2008R2 Sample Reports that you created in the previous lesson.

  2. Open the Employee_Sales_Summary_2008R2 report.

Add the Matrix

To design a relatively complex matrix, work with the matrix directly on the report design surface to simplify selecting and configuring matrix properties. In a later step, you will add the matrix to a rectangle container.

To add a matrix to display sales orders

  1. Add a Matrix to the report design surface.

  2. In the matrix, do the following:

    1. Hover over the Rows cell, click the field tag, point to Datasets, point to EmployeeSalesDetail2008R2, and click Category.

    2. In the Report Data pane, expand Datasets, and then expand EmployeeSalesDetail2008R2.

    3. Drag SalesOrderNumber to below Category in the Row Groups pane. A child row group is created.

    4. In the Data cell, click the field tag, and then click Sales. Format the text box in this cell as currency.

  3. Right-click the Category cell, point to Add Total, and then click After.

  4. Format the matrix as needed.

To create a parameter to control the expand/collapse toggle

  1. In the Report Data pane, add a parameter named ExpandAllTableRows.

  2. In the Report Parameters Property dialog box, do the following:

    1. Change Prompt to Expand all table rows?

    2. In Data type, select Boolean.

    3. In Default Values, set the value to false.

To create an expand/collapse toggle for the child row group

  1. In the Grouping pane, open the Group Properties for SalesOrderNumber.

  2. On the Visibility page, click Show or Hide based on an expression, and enter the following expression: =NOT Parameters!ExpandAllTableRows.Value.

  3. Select Display can be toggled, and from the drop-down list, select the name of text box that has the group expression for the parent group: Category.

A text box is automatically named based on the first field that is added to it.

  1. In the matrix, right-click SalesOrderNumber, and open Text Box Properties.

  2. On the Action page, click Go to report.

  3. In Specify a report, type Sales_Order_Detail_2008R2.

    Note

    Because the target report does not yet exist, you must accurately type the report name and parameter names manually. If a target report does exist in the project, you can choose from a drop-down list of report names and the corresponding report parameters.

  4. Click Add.

  5. In Name, type SalesOrderIDStart.

  6. In Value, type [SalesOrderID].

  7. Click Add.

  8. In Name, type SalesOrderIDEnd.

  9. In Value, type [SalesOrderID].

  10. On the Font page, set Color to Blue and Effects to Underline.

The Sales_Order_Detail_2008R2 report displays details for each sales order in a range of sales order numbers. By setting the target report parameters to the same sales order number, the report displays details for a single sales order.

Add a Dataset to Provide Initial Quotas and Ranges

To add quota data

  • In the Report Data pane, add a dataset named CategoryQuotas. Use the existing shared data source, specify an embedded dataset, and use the following query:

    SELECT 1 as ID, 'Accessories' as Category, 
   1000 as Quota, 25 as PercentDeviation
UNION SELECT 2 as ID, 'Bikes' as Category, 
   70000 as Quota, 5 as PercentDeviation
UNION SELECT 3 as ID, 'Clothing' as Category, 
   2500 as Quota, 20 as PercentDeviation
UNION SELECT 4 as ID, 'Components' as Category, 
   20000 as Quota, 10 as PercentDeviation

The Quota field represents currency. The values for Quota will populate the initial values for a report parameter named @CategoryQuota that you will create in a later step. To represent a number that is not an integer, the report parameter must be data type Float. In the next step, you will create a calculated field based on Quota that is type Float.

To create a calculated field

  1. In the Report Data pane, right-click the dataset named CategoryQuotas, and then click Add Calculated Field.

  2. Click Add.

  3. In Field Name, type QuotaF.

  4. In Field Source, set the value to the following expression:

    =CDbl(Fields!Quota.Value)

Add a Parameter to Enable a User to Specify Quota Values

To add a multivalue parameter @CategoryQuota

  1. In the Report Data pane, add a new parameter.

  2. On the General page, do the following:

    1. Set Name to CategoryQuota.

    2. Set Prompt to Category quotas (Accessories, Bikes, Clothing, Components):

    3. Set Data type to Float.

    4. Select Allow multiple values.

  3. On the Default Values page, do the following:

    1. Select Get values from query.

    2. For Dataset, select CategoryQuotas.

    3. For Value field, select QuotaF.

  4. Run the report to verify that the parameters display correctly and that each parameter has a default value.

To enable the user to interactively change category quotas, do not specify available values for the dataset. Available values limit the choices for a parameter.

Define a Complex Expression as a Report Variable

Variables provide a way to define a complex expression once and then add a reference to it in other expressions. When the report is processed, a report variable is evaluated once. A group variable is evaluated once per group instance.

To create a report variable

  1. To create a report variable, open Report Properties.

  2. On the Variables page, do the following:

    1. Click Add.

    2. In Name, type SumofAllParameterThresholds.

    3. In Value, type =CDbl(Parameters!CategoryQuota.Value(0)+Parameters!CategoryQuota.Value(1)+Parameters!CategoryQuota.Value(2)+Parameters!CategoryQuota.Value(3))

This expression calculates the total quota as the sum of the category quotas. You will use this variable when you configure the indicator scoped to the matrix.

Define a Complex Expression as a Group Variable

To create a group variable

  1. Select the matrix to display the row groups in the Grouping pane.

  2. Right-click the Category group, and open Group Properties.

  3. Click Variables, and do the following:

    1. Click Add.

    2. In Name, type IndextoCategoryQuotas.

    3. In Value, type =Lookup(Fields!Category.Value,Fields!Category.Value,Fields!ID.Value,"CategoryQuotas")

      This retrieves the ID for the group value Category, which is 1, 2, 3, or 4. These correspond to the categories listed in alphabetical order: Accessories, Bikes, Clothing, Components.

    4. Click Add.

    5. In Name, type PercentDeviation.

    6. In Value, type =.01 * CDbl(Lookup(Fields!Category.Value,Fields!Category.Value,Fields!PercentDeviation.Value,"CategoryQuotas"))

      This retrieves the percent deviation field for the group value Category, which is 25%, 5%, 20%, 10%. For a category like Bikes, there is a smaller percentage deviation for the quota because sales are expected to be large. For a category like Accessories, sales are smaller so the percentage deviation for the quota is smaller.

You will use the group variable when you configure the indicator that is scoped to the category group.

In the next step, you will add conditionally hidden text to display parts of the expression that calculate the highest and lowest value in the range for each category quota.

Add a Group Header Row and Display Calculation Results

You can calculate and display expressions to help understand property values at run-time. Use conditional visibility to hide these values when they are not needed.

To add a group header row for Category

  1. Right-click the text box that contains SalesOrderNumber, press ESC if necessary to select the text box and exit edit mode, point to Insert Row, and click Outside Group - Above.

  2. In the Sales column, click the field tag for the cell in the row that you just added, and then click Sales. Format the text box as currency.

A header row that is associated with the Category group is added.

To add multiple parts of an expression to a text box

  1. Select the empty text box above SalesOrderNumber that is in the group header row that you just added. The scope for data in this cell is the Category group.

  2. To provide mode space to work, expand the row height and column width for the cell with the rectangle.

  3. In the text box, insert a Rectangle named rectCategoryQuotas. By using a rectangle container, you can set the border style and color to be consistent with cells in the matrix.

  4. To the rectangle, add a text box named tbCategoryQuotas with the following text:

    1. Quota:

    2. Create a Placeholder named Quota with the following value formatted as Currency: =CDbl(Parameters!CategoryQuota.Value((Variables!IndextoCategoryQuota.Value)-1))

    3. On the next line, enter a minus sign (-), a Placeholder named PercentDeviation, a colon (:), and a Placeholder named MinimumThreshold.

    4. Change the Placeholder value for PercentDeviation to the following expression: =FormatPercent(Variables!PercentDeviation.Value,0)

    5. Change the Placeholder value for MinimumThreshold to the following expression: =(1.0-Variables!PercentDeviation.Value) * CDec(Parameters!CategoryQuota.Value((Variables!IndextoCategoryQuota.Value)-1))

    6. On the next line, enter a plus sign (+), a Placeholder named PercentDeviation, a colon (:), and a Placeholder named MaximumThreshold.

    7. Change the Placeholder value for PercentDeviation to the following expression: =FormatPercent(Variables!PercentDeviation.Value,0)

    8. Change the Placeholder value for MaximumThreshold to the following expression: =(1.0+Variables!PercentDeviation.Value) * CDec(Parameters!CategoryQuota.Value((Variables!IndextoCategoryQuota.Value)-1))

    9. Format the text as needed. For example, right-justify all the text.

    10. On the Visibility page, click Show or hide based on an expression, and enter the following expression: =NOT Parameters!ShowAll.Value

    11. In the text box, right-justify the text.

  5. Run the report.

For Accessories, the following text appears:

Quota: $1,000

-25%: $750

+25%: $1,250

Notice that the border style of the rectangle does not match the rest of the matrix.

To specify Border properties for a nested rectangle

  1. Click the text box named tbCategoryQuotas, and press ESC until the Rectangle properties for rectCategoryQuotas appear in the Properties pane.

  2. In the Properties pane, do the following:

    1. Change BorderColor to LightGrey.

    2. Change BorderStyle to Solid.

  3. Run the report.

The matrix cell borders now match.

Add an Indicator for Sales in All Categories

Report items that are added to corner cell of a matrix are scoped to the data in the matrix. You can merge multiple text boxes in the corner, add a rectangle container, and then add multiple report items to it. The corner cell is a good area to display values scoped to the data in the matrix. You can control the visibility of each item in the rectangle independently.

For more information see Understanding Tablix Data Region Areas (Report Builder 3.0 and SSRS).

To add an indicator scoped to the matrix data

  1. The corner cell is the cell in the first row and first column, and contains the text Category. The data scope for items in the corner cell is all the data in the matrix after filters are applied. You will add a text box, an indicator, and a text box with conditional visibility to show the calculated overall quota for category sales.

  2. Right-click the text box, point to Insert, and click Rectangle. The rectangle replaces the text box.

    Change the name of the rectangle to rectMatrixCorner.

  3. Expand the row height and column width for the corner cell to provide more room to work.

  4. To the rectangle, add a text box. Enter the following text on two lines: Met Quota?

  5. Next to the text box, insert an Indicator. In Directional, use the Tooltip to click 3 Up/Down Triangles.

  6. Align the top of the indicator with the top of the text box.

  7. Open Indicator Properties.

  8. On the Value and States page, do the following:

    1. In Value, select [Sum(Sales)].

    2. In States Measurement Unit, select Numeric.

    3. In Indicator states, delete the middle state.

      At the matrix scope, limit the display to two states: met or did not meet the quota.

    4. For the first state, in Start, type 0.

    5. In End, type the following expression: =Variables!SumParameterThresholds.Value

    6. For the second state, in Start, copy the same expression that you used for End in the first state.

    7. In End, type the following expression: =Max(Sum(Fields!Sales.Value))

  9. Below the indicator, add a text box named tbMatrixQuotas with the following text:

    1. Quota:

    2. Create a Placeholder named Quota with the following value formatted as Currency: =Variables!SumofAllParameterThresholds.Value

    3. Total:

    4. Create a Placeholder named Total with the following value formatted as Currency: =Sum(Fields!Sales.Value).

    5. On the Visibility page, click Show or hide based on an expression, and enter the following expression: =NOT Parameters!ShowAll.Value

    6. In the text box, right-justify the text.

  10. Quota:

  11. Place the cursor next to the text you just added, right-click, and then click Create Placeholder.

  12. In Label, type MAX.

  13. In Value, type =Variables!SumParameterThresholds.Value

  14. On the Number page, in Category, click Currency. Set Decimal places to 0.

  15. To the rectangle, add a text box with the following text: Total:

  16. Place the cursor next to the text you just added, right-click, and then click Create Placeholder.

  17. In Label, type Total.

  18. In Value, type Sum(Fields!Sales.Value).

  19. On the Number page, in Category, click Currency. Set Decimal places to 0.

  20. Open Text Box Properties. On the Visibility page do the following:

    1. Click Show or hide based on an expression.

    2. Use the following expression: =NOT Parameters!ShowAll.Value

  21. Format the rectangle and its contained report items as needed. For example, do the following:

    1. Set the Fill color for the text boxes and the gauge panel to match the row header background color.

    2. Set the BorderColor and BorderStyle properties for the rectangle to match the matrix cells.

    3. Set the BorderStyle property for the gauge panel to None.

Add an Indicator for Sales in Each Category

To add an indicator scoped to the Category row group

  1. Right-click the handle for the last column header in the matrix, point to Insert Column, and then click Outside Group - Right.

  2. In the new column, in the group row associated with Category, insert a Rectangle named rectCategoryIndicator.

  3. In the rectangle, insert an Indicator. In Symbols, use the Tooltip to click 3 Symbols (Uncircled).

    The data for this cell is scoped to the data for each product category.

  4. The indicator that is added is a Gauge Panel report item. Adjust the width and height of the Gauge in the Gauge Panel as needed.

  5. Open Indicator Properties to the Value and States page.

  6. In Value, select [Sum(Sales)].

  7. In States Measurement Unit, select Numeric.

  8. In the first state, do the following:

    1. In Icon, verify that an X appears.

    2. In Color, select Yellow.

    3. In Start, type 0.

    4. In End, type the following expression: =(1.0-Variables!PercentDeviation.Value) * CDec(Parameters!CategoryQuota.Value((Variables!IndextoCategoryQuota.Value)-1))

  9. In the second state, do the following:

    1. In Icon, change the symbol to a √.

    2. In Color, select Lime Green.

    3. In Start, copy the same expression that you used for End in the first state.

    4. In End, type the following expression: =(1.0+Variables!PctDeviation.Value) * CDec(Parameters!CategoryQuota.Value((Variables!QuotaIndex.Value)-1))

  10. In the third state, do the following:

    1. In Icon, verify that an ! appears.

    2. In Color, select Dark Violet.

    3. In Start, copy the same expression that you used for End in the second state.

    4. In End, type the following expression: =Max(Sum(Fields!Sales.Value))

  11. Set the column header text to Met Category Quota?

Add a Rectangle Container for the Matrix

Add a rectangle container for the matrix and the text that explains the purpose of the matrix.

To add a rectangle container

  1. In the report Body, insert a Rectangle name rectMatrix.

  2. To the rectangle, add a text box with the following text: Compare sales to quotas by category.

  3. Format as needed.

  4. Select the matrix. Drag the upper left hand corner of the matrix to the rectangle below the text box. The rectangle expands as needed.

  5. Select the rectangle. In the Properties pane, set Bookmark to QuotasRectangle.

  6. Use the Layout toolbar to align the tops of the rectangles named rectCharts and rectMatrix.

Add a Link from Scenario Text to the Matrix

To add a link from text to a bookmark

  1. Select the text in the text box that contains scenario text.

  2. With the mouse, highlight the word matrix.

  3. Right-click and open the Text Properties dialog box.

  4. On the Font page, set Color to Blue and Effects to Underline.

  5. On the Action page, click Go to bookmark.

  6. In Select bookmark, type QuotasRectangle.

Change the Default Value of ReportMonth

To verify the group indicator settings, use data that falls into all three ranges for the indicator.

To set the default value for @ReportMonth

  1. In the Report Data pane, open the Report Properties for @ReportMonth.

  2. On the Default Values page, in Values, enter 11.

Sales data for November is used.

Preview the Report

To preview and verify the report

  1. Preview the report.

  2. Verify the following:

    1. The two main rectangle containers appear side by side.

    2. By default, the matrix rows for sales orders are collapsed.

    3. Change the value for @ExpandAllTableRows to expand and collapse all rows in the matrix when the report runs.

    4. The indicator in the table corner is green.

    5. The category indicators display the following images:

      Accessories   A yellow mark (X)

      Bikes   A green checkmark (√ )

      Clothing   A yellow mark (X)

      Components   A purple exclamation point (!)

    6. You can show or hide the calculation information based on the @ShowAll parameter value.

    7. Type in different values for quotas and verify that the indicator displays the correct state.

  3. If you plan to export the report to a .pdf file or other print-compatible format, use the report viewer toolbar Print Layout button to verify that the report renders as expected. If there are unexpected multiple horizontal pages, remove white space and adjust the container widths as needed. For more information, see Report Design Tips (Report Builder 3.0 and SSRS).