Mastering OLAP Reporting: Prototype KPIs in Reporting Services - Page 6

August 21, 2006

Add KPIs in the Report Layout

In this section, we will add a matrix data region, along with data, to the report canvas on the Layout tab. While our focus is the creation of basic KPIs, and not a "traditional" report, we will find that adding the supporting data to the matrix, and then adding the KPIs (which are based upon the data), will leave the data in place to allow for easy verification of KPI accuracy. (We can always remove the data after the KPIs are in place and verified as to accuracy of presentation.)

We will add a matrix data region to the Layout tab first, which will serve to house our data and KPI calculations.

1.  Click the Layout tab.

2.  From the Toolbox, drag a matrix data region to the Layout tab, as depicted in Illustration 44.


Illustration 44: Adding a Matrix Data Region ...

NOTE: If the Toolbox is not visible, in the View menu, select Toolbox.

The matrix appears on the report canvas, as shown in Illustration 45.


Illustration 45: The Matrix Appears on the Report Canvas

3.  Within the Datasets window, expand the RS032_KPI_Support Dataset by clicking the "+" sign to its immediate left.

NOTE: If the Datasets window is not visible, select Datasets in the View menu.

The data fields within the RS032_KPI_Support Dataset appear in the Datasets window, as depicted in Illustration 46.


Illustration 46: The Data Fields Appear within the Datasets Window

4.  From the Datasets window, drag the Sales_Territory_Country field into the bottom left corner of the new matrix data region (the box watermarked "Rows"), as shown in Illustration 47.


Illustration 47: Dragging the First Data Field into the Matrix Data Region

5.  Drag the Fiscal_Year field into the top right corner of the new matrix data region (the box watermarked "Columns").

6.  Drag the Internet_Gross_Profit_Margin field into the bottom right corner of the new matrix (the box watermarked "Data").

7.  Drag the Growth_in_Customer_Base field to the right side of the bottom right corner box, where we dropped the Internet_Gross_Profit_Margin field in the previous step, dropping the field when the white "bracket" figure appears, as depicted in Illustration 48.


Illustration 48: Drop the Data Field when the "Bracket" (Circled Above) Appears ...

8.  Click the gray bar atop the leftmost column of the matrix, to highlight the column.

9.  Holding down the SHIFT key, click the bars atop the second and third columns, so as to highlight all columns.

10.  Select "8" in the Font Size selector in the toolbar, atop the design environment, as shown in Illustration 49.


Illustration 49: Highlight All Four Columns, and Change the Font to "8"

11.  Right-click the bottom right box, currently containing the expression =Sum(Fields!Growth_in_Customer_Base.Value).

12.  Select Add Column from the context menu that appears, as depicted in Illustration 50.


Illustration 50: Adding a Column to the Matrix ...

13.  Right-click the new bottom right box, the one we added in the last step (currently unoccupied).

14.  Select Add Column from the context menu that appears, once again, to add another empty column to the matrix.

15.  Click the gray bar atop the first new column of the matrix, to highlight the column.

16.  Holding down the SHIFT key, click the bar atop the second new column, so as to highlight both new, blank columns.

17.  In the Properties window, select the Width box, within the Layout properties group.

NOTE: If the Properties window is not visible, select Properties Window in the View menu.

18.  Set the Width property to .5, as shown in Illustration 51.


Illustration 51: Narrowing the Width of the New Columns ...

19.  Click outside the Width property box to accept the new setting.

Both columns narrow. These will be our KPI columns.

20.  Click the bottom row in the first (leftmost) of the two newly added columns, simply to select the box.

21.  Holding down the SHIFT key, click the bottom row in the second of the two newly added columns (the bottom right box in the matrix), so as to highlight the bottom row in both blank columns.

22.  Expand the BorderWidth property group (within the Appearance group) within the Properties window.

23.  Set the individual BorderWidth properties as presented in Table 1.

Physical Dimension

Width Setting

Default

1

Left

20

Right

20

Top

7

Bottom

7


Table 1: BorderWidth Properties Settings

24.  In the BorderStyle property setting (just above the BorderWidth property group, which we have just set, in the Properties window), select Solid.

25.  In the BorderColor property setting (just above the BorderStyle property group setting in the Properties window), select White.

The BorderColor, BorderStyle, and BorderWidth settings, with our input, appear as depicted in Illustration 52.


Illustration 52: Border Properties Settings in the Properties Window

26.  Right-click the heading box (the box above the box for which we changed width properties above) in the first (leftmost) of the two newly added columns.

27.  Select Expression from the context menu that appears, as shown in Illustration 53.


Illustration 53: Select Expression for the Left KPI Heading ...

The Expression Editor appears.

28.  Type the following into the upper input box of the Editor, as heading for the Internet Gross Profit Margin KPI:

="GP"& vbcrlf &"Margin"

The relevant portion of the Expression Editor appears, with our input, as depicted in Illustration 54.


Illustration 54: Expression Editor with Our Input

29.  Click OK to accept our input, and to dismiss the Expression Editor.

30.  Right-click the heading box for the second new column (to the immediate right of the box in which we placed the title in the last step), to select it.

The Expression Editor appears, once again.

Type the following into

31.  Type the following into the upper input box of the Editor, as heading for the Growth in Customer Base KPI:

="Cust"& vbcrlf &"Base Growth"

The relevant portion of the Expression Editor appears, with our input, as shown in Illustration 55.


Illustration 55: Expression Editor with Our Input

32.  Click OK to accept our input, and to dismiss the Expression Editor.

33.  Select both heading boxes (the ones we populated in our last few steps).

34.  Select Center in the Text Align setting of the Properties window (a member of the Appearance group), as depicted in Illustration 56.


Illustration 56: Centering the New Headings ...

We are now ready to add the KPIs. Since the client representatives have stated that they simply wish to see "color indicators," our task is even more simple than if they wished to see images that reflected status of the indicators, and so forth, as we presented in Mastering OLAP Reporting: Reporting with Analysis Services KPIs. We have only to vary the colors of our specially sized matrix boxes. We can accomplish this effect rapidly with the BackGroundColor property setting of the respective KPI box, as we shall see in the steps that follow.

35.  Click the Gross Profit Margin KPI box (in the bottom row underneath the heading box in which we have typed ="GP"& vbcrlf &"Margin"), to select it.

36.  Click <Expression...> from the dropdown selector obtained by clicking the BackGroundColor setting in the Properties window (the first selection in the Appearance group, atop the window), as shown in Illustration 57.


Illustration 57: Select <Expression...>, Instead of A Color ...

The Expression Editor appears, once again.

37.  Replace any existing contents, by typing (or cutting and pasting) the following into the upper input box of the Editor:

=IIF(SUM(Fields!Internet_Gross_Profit_Margin.Value)
< .41, "Red", IIF(SUM(Fields!Internet_Gross_Profit_Margin.Value)
>= .42, "Green", "Yellow"))

In effect, we are conditionally formatting the Background Color of the specially sized box, based upon the magnitude of the Gross Profit Margin value for the respective Territory Sales Country. In accordance with the specifications we have obtained from the representatives of the Controller's Group, we have directed, via our expression, that a Gross Profit Margin of less than 41 percent be represented by a red indicator, that a Gross Profit Margin equal to, or greater than, 42 percent be represented by a green indicator, and that a Gross Profit Margin between the lower and upper thresholds (equal to, or greater than, 41 percent, but less than 42 percent) be represented by a yellow indicator.

The relevant portion of the Expression Editor appears, with our input, as depicted in Illustration 58.


Illustration 58: Expression Editor with Our Input

38.  Click OK to accept our input, and to dismiss the Expression Editor.

Now all that remains in our series of steps for KPI creation is the addition of the second KPI, Growth in Customer Base KPI.

39.  Click the Growth in Customer Base KPI box (the bottom row underneath the heading box in which we have typed ="Cust"& vbcrlf &"Base Growth"), to select it.

40.  Click <Expression...> from the dropdown selector obtained by clicking the BackGroundColor setting in the Properties window (the first selection in the Appearance group, atop the window), as we did after selecting the first KPI earlier.

The Expression Editor appears, as before.

41.  Replace any existing contents, by typing (or cutting and pasting) the following into the upper input box of the Editor:

=IIF(SUM(Fields!Growth_in_Customer_Base.Value)
< 4, "Red", IIF(SUM(Fields!Growth_in_Customer_Base.Value) >= 5,
"Green", "Yellow"))

After the manner that we followed with the first KPI, we are conditionally formatting the Background Color of the specially sized box for the second KPI. This time, we are basing color upon the magnitude of the Growth in Customer Base value for the respective Territory Sales Country. In accordance with the specifications we have obtained from the representatives of the Controller's Group, we have crafted our expression to direct that a Growth in Customer Base value of less than 4 be represented by a red indicator, that a Growth in Customer Base value equal to, or greater than, 5 be represented by a green indicator, and that a Growth in Customer Base value between the lower and upper thresholds (equal to, or greater than, 4, but less than 5) be represented by a yellow indicator.

The relevant portion of the Expression Editor appears, with our input, as shown in Illustration 59.


Illustration 59: Expression Editor with Our Input

42.  Click OK to accept our input, and to dismiss the Expression Editor.

Having completed the steps of adding and formatting our basic KPIs, we will verify their operation in the next section.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers