Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 21, 2006

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

By William Pearson


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.

1.  Click the Preview tab.

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

2.  Select File --> Save All on the main menu, to save our work to this point.

3.  Select File --> Exit, to leave the Business Intelligence Development Studio, when ready.

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM