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

August 21, 2006

Verification

Preview the Report and Verify Accuracy of KPIs

We will 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.

The Analysis Services KPIs report executes momentarily, and then returns our KPIs as expected, as depicted in Illustration 60.

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 themes.

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.

Conclusion ...

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.

Next, 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 cube data.

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.