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.
» 
Comment and Contribute
