Drive
Indicators with KPI Values in the Report
I
mentioned earlier that KPIs typically wind up in graphical form to
summarize, at a glance, a status, trend, or other condition (usually in a
balanced scorecard, digital dashboard, or similar presentation backdrop). We
have been able to pull in the numerical values of the KPIs, but Analysis
Services does not expose a direct means of presenting the KPI graphics that
exist within Analysis Services (that is, there are no report items that
are included to do this, etc.)
We can
still obtain an identical presentation effect in Reporting Services. We
just have to supply our own graphics. Working with Cognos and other enterprise
BI suites over the years, particularly at the point, and beyond, where all were
venturing into web presentation and delivery, I often used the easily
accessible image files included with the applications to achieve my own ends (I
often simply wanted to replace them with my own or client images, such as
corporate logos, etc., to customize the appearance of the reports and other
displays we presented via the internet / intranets). We can do something
similar with the images that are included to support Reporting Services'
KPIs. We can simply use conditional logic to turn all those 1's, 0's,
and -1's into a corresponding image.
Let's put
the same reasoning to work in our Analysis Services KPIs report by
taking the following steps:
1.
Drag an Image
report item from the Toolbox into the Data field with the column
label Internet Revenue Status, as depicted in Illustration 63.
Illustration 63: Placing
an Image Item into a KPI Data Field
The Welcome
to the Image Wizard page of the Image Wizard appears, as shown in Illustration
64.
Illustration 64: Welcome
to the Image Wizard ...
2.
Click the Next
button to proceed with the Image Wizard.
The Select
the Image Source page appears next.
3.
Select the Embedded
radio button, to direct the Image Wizard to embed the image within the
report file.
The Select
the Image Source page appears, with our setting, as depicted in Illustration
65.
Illustration 65: Choosing
to Embed the Image ...
4.
Click the Next
button to continue.
The Choose
the Embedded Image page appears.
5.
Click the New
Image button.
The Import
Image dialog appears.
6.
Navigate to
the KPI image files that were stored on the system with the installation
of Analysis Services 2005.
The image files were put in place to support the KPI
Browser in Analysis Services, and will serve as a handy source of
image files for this practice session. (We can certainly use other images, if
they are accessible, and of an acceptable format, size, and so forth to be used
by Reporting Services.) The Images folder is installed, by
default (and, therefore, subject to be installed in a different location on our
individual machines), in the following location:
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\
DataWarehouseDesigner\KPIsBrowserPage\
7.
From inside
the Import Image dialog, right-click the Images folder within the
KPIsBrowserPage folder.
8.
Select Copy
from the context menu that appears, as shown in Illustration 66.
Illustration 66: Copying
the KPI Images Folder ...
9.
Navigate back
to the RS029 MSAS KPIs in ReportServices folder, which contains the Analysis
Services and Reporting Services project clones we created earlier.
10. Right-click the area within the
folder, as we did earlier.
11. Select Paste from the
context menu that appears, as depicted in Illustration 67.
Illustration 67: Pasting
the Sample Folder in the RS029 MSAS KPIs in ReportServices Folder
The Images
folder appears in the new location.
12.
Right-click
the Images folder.
13.
Select Rename
from the context menu that appears, as we have done before.
14.
Rename the
folder to the following:
KPI Image Collection
The KPI
Image Collection folder appears, via the Import Image dialog,
in the new location, as shown in Illustration 68.
Illustration 68: KPI Image Collection Folder in Its New Location ...
15.
Click the KPI Image Collection folder to open it.
16. Select the Stoplight_Multiple0.gif
(the "red light") file, as depicted in Illustration 69.
Illustration 69: Selecting
a KPI Image for the Report
17.
Click Open to
add the specified image file to our report.
The Import
Image dialog closes, and we see the image file rendered within the viewer
of the Choose the Embedded Image page.
18.
Click the New
Image button again.
19. Select the Stoplight_Multiple1.gif
(the "yellow light") file.
20.
Click Open to
add the specified image file to the report.
We see
the second image rendered within the viewer of the Choose the Embedded Image
page.
21.
After the same
fashion with which we have added the two images above, add the following
additional images to the report.
-
Stoplight_Multiple2.gif
-
Arrow_Status_Asc0.gif
-
Arrow_Status_Asc2.gif
-
Arrow_Status_Asc4.gif
Once our additions are complete, the acquired images
appear within the viewer of the Choose the Embedded Image page.
22.
Ensure that
the first image we added, Stoplight_Multiple0.gif (the "red light") is selected.
Our selection (circled), atop the other KPI images,
appears within the viewer of the Choose the Embedded Image page as
shown in Illustration
70.
Illustration 70: New
Image Acquisitions Appear in the Choose the Embedded Image Page (Partial
View)
23.
Click Next to
accept our image choice, and to continue with the Image Wizard.
We
arrive at the Completing the Wizard page, which affords us an
opportunity to confirm our selection, as depicted in Illustration 71.
Illustration 71: The Completing the Wizard Page
24.
Click Finish to place the image in the Data
field, and to dismiss the Image Wizard.
"Grab" the bottom border of the bottom row in
the matrix data region, using the cursor on the bottom left corner of
the gray area, and increase the height of the row (I gave it a height of 2
in.) enough to easily accommodate the image we have placed in the data
field, as shown in Illustration
72.
Illustration 72: Increasing
Row Height to Accommodate the Image ...
Previewing
the report at this juncture would mean we see the "red light" in
every data field in the column. This is where conditional formatting comes in.
25.
Click the
image to select it.
26.
Select <Expression
...> in the selector for the Value property, within the Properties
window for the image, as depicted
in Illustration 73.
Illustration 73: Select <Expression...> to Replace the Value Property
in the Properties Window
The Expression
Editor opens.
27.
Replace stoplight_multiple0,
which currently appears in the Expression box of the Editor, with
the folowing expression:
=IIF(Fields!Internet_Revenue_Status_.Value = -1,
"stoplight_multiple0",
IIF(Fields!Internet_Revenue_Status_.Value = 0,
"stoplight_multiple1",
"stoplight_multiple2")
)
The Expression Editor, with our expression in
place, appears as shown
in Illustration 74.
Illustration 74: Expression
Editor with Conditional Expression in Place ...
28.
Click OK to
accept the expression and to dismiss the Editor.
Now, let's
perform the same image insertion and conditioning steps for the Internet
Revenue Trend Data field.
29.
Drag an Image
report item from the Toolbox into the Data field with the column
label Internet Revenue Trend.
30.
Click the Next
button on the Welcome page of the Image Wizard, to proceed with
the Image Wizard.
The Select
the Image Source page appears next.
31.
Select the Embedded
radio button, as before, to direct the Image Wizard to embed the image
within the report file.
32.
Click the Next
button to continue.
The Choose
the Embedded Image page appears.
33.
Ensure that Arrow_Status_Asc4.gif (the "green,
upward-pointing arrow") is selected.
Our selection (circled), underneath the other KPI
images, appears within the viewer of the Choose the Embedded Image page
as partially depicted
in Illustration 75.
Illustration 75: Our New
Image Selection in the Choose the Embedded Image Page
(Partial View)
34.
Click Next to
accept additions and continue with the Image Wizard.
We
arrive at the Completing the Wizard page, which affords us an
opportunity to confirm our selection, once again.
35.
Click Finish on the Completing the Wizard
page to add the image to the report file, and to dismiss the Image Wizard.
Adding
the conditional expression is all that remains:
36.
Click the
image to select it.
37.
Select <Expression
...> in the selector for the Value property, within the Properties
window for the image, as we did with the first image.
The Expression
Editor opens.
38.
Replace arrow_status_asc4,
which currently appears in the Expression box of the Editor, with
the following expression:
=IIF(Fields!Internet_Revenue_Trend.Value = -1,
" arrow_status_asc0",
IIF(Fields!Internet_Revenue_Trend.Value = 0,
" arrow_status_asc2",
" arrow_status_asc4")
)
The Expression Editor, with our expression in
place, appears as shown
in Illustration 76.
Illustration 76: Expression
Editor with Conditional Expression in Place ...
39.
Click OK to
accept the expression and to dismiss the Editor.
Now let's
take a look at the results of our handiwork.
40. Click the Preview tab.
The Analysis Services KPIs report executes, and
returns our KPIs as expected, as depicted in Illustration 77.
Illustration 77: KPIs at
Work in the Analysis Services KPIs Report ...
We could certainly have obtained
artwork that was more fitly sized, and perhaps a data set with a bit more
diversity, which might have produced the full spectrum of Status and Trend
KPI values. Regardless, the concepts are the same, particularly in any
scenario where we undertake to generate graphics to represent Analysis
Services KPIs in Reporting Services, rather than remaining limited
to their use within the Analysis Services KPI Browser.
1.
Select File
--> Save All on the main menu, to save our work
to this point.
2.
Select File
--> Exit, to leave the Business
Intelligence Development Studio, when ready.
Conclusion ...
In this article, we
introduced another powerful new tool that makes its debut with MSSQL Server
Analysis Services 2005, Key Performance Indicators ("KPIs"). Key Performance Indicators close yet another "out-of-the-box"
capabilities gap between the Microsoft integrated business intelligence
solution and the formerly dominant enterprise BI suites. We discussed the
purpose and uses of KPIs, and stated that, although these indicators
originate in Analysis Services, and are typically viewed therein via an
internal browser, we are not limited to accessing and using KPIs
within Analysis Services. The focus of this article was to show how we
can flexibly present KPIs within Reporting Services to support
decision making, analysis and reporting.
After initially discussing Analysis Services KPIs,
we shifted to the focus of our hands-on practice session, surrounding the use of
the indicators within a report we created in Reporting Services. In
preparation, we created a new Analysis Services Project within the Business
Intelligence Development Studio Solution, to which we added a new Reporting
Services Project. We ensured connectivity with the respective data sources
involved within the projects, and then modified a sample OLAP report to
work with KPIs found in the sample cube. We next added KPI values
to the report. Finally, we demonstrated an approach for using KPIs to
drive graphical indicators in the report. Throughout the steps of our
practice session, we discussed, at appropriate junctures, various settings and
techniques involved in achieving our objectives.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.
» 
Comment and Contribute
