Preview the Report
and Verify Accuracy of KPIs
next take a look at the results of our handiwork. Recall that we left the
actual values, upon which the conditional formatting behind our KPIs is
based, within the matrix data region, to provide a means of easy
verification at this point within our practice session.
Click the Preview
The Analysis Services KPIs report executes
momentarily, and then returns our KPIs as expected, as depicted in Illustration 60.
Illustration 60: Basic KPIs
at Work in the Analysis Services KPIs Report ...
We can agree the results
of the KPIs for the respective country / value combinations easily, and
gain comfort that our straightforward conditional Background Color
formatting is working properly in providing the indications specified by the
Controller's Group representatives. Having ascertained that the KPIs
are accurate, we might develop the report further by eliminating the two
measures, by designing in additional KPIs to present other critical
performance indicators, and so forth. In prototyping KPI solutions for
clients using similar approaches, I have taken these concepts much further,
including the addition of values to the matrix boxes (conditionally
formatted themselves to change to a color that contrasts best with the
respective Background Color), the addition of logic to various
conditional formatting schemes to do nested IIF statements that vary the
results based on parameter selections, and many other variations upon these
Moreover, I've added
drilldown / drillthrough features into KPI presentations, and so forth,
although I often find myself reminding clients that it is easy to lose sight of
the general purposes of Key Performance Indicators if we become lost in
chasing the virtually unlimited options that we can accomplish within a tool as
versatile as Reporting Services. Typically, once we have a working
prototype and / or have selected the KPIs (with underlying logic) that
we want to display in dashboards or even elaborate reports, I recommend the
creation of KPIs within Analysis Services 2005 (or calculated
members within Analysis Services 2000 implementations), or perhaps
other approaches, upon which we might base the ultimate KPIs at the
presentation layer, for obvious performance, and other design, reasons.
--> Save All on
the main menu, to save our work to this point.
--> Exit, to
leave the Business Intelligence Development Studio, when ready.
In this article, we concentrated upon the generation of "prototype"
KPIs, housed solely within Reporting Services, to meet the
business requirements of a hypothetical client. After discussing reasons that
prototyping KPIs in this fashion might be useful, we detailed a simple KPI
objective, and set about meeting the requirements in a hands-on practice
exercise. We prepared for our practice exercise by creating a new Analysis
Services Project within a Business Intelligence Development Studio solution,
so as to make our entire solution freestanding (perhaps to ease further
independent exploration); by ascertaining Connectivity of the Relational
Data Source within the Analysis Services Project; and then by
deploying the Analysis Services Project.
we concluded preparation by adding a new Reporting Services Project to
the solution, within which we added an Analysis Services Shared Data Source to
access the cube we had deployed within the Analysis Services Project.
We then created a basic OLAP Report within which to work with our sample
We added measures and
associated KPIs to the report, performing minimal formatting along the
way to enhance our demonstration of the prototype we had constructed. Finally,
we verified the correct operation of our prototype KPIs within 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.