Mastering OLAP Reporting: Reporting with Analysis Services KPIs - Page 7

May 15, 2006

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.

MSSQL Server Reporting Services
Introducing the Tablix Data Region: Basic Grouping Concepts
Introducing the Tablix Data Region in Reporting Services 2008
100% Stacked Column Chart for Analysis Services Data
XY (Scatter) Chart for Analysis Services Data
Simple Doughnut Chart for Analysis Services Data
Exploded Pie Chart for Analysis Services Data
Stacked Bar Chart for Analysis Services Data
Line Chart for Analysis Services Data
Stacked Column Chart for Analysis Services Data
A More Advanced Pie Chart for Analysis Services Data
Simple Pie Chart for Analysis Services Data
Simple Bar Chart for Analysis Services Data
Simple Column Chart for Analysis Services Data
Introducing Reporting Services Charts for Analysis Services
Mastering OLAP Reports: Parameterized Grouping
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I
Support Parameterization from Analysis Services - Parameter Defaults
Parameterization from Analysis Services - Cascading Picklists
Support Parameterization from Analysis Services
Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
Reporting Services: Customize Automatically Created Parameter Support Objects
Snapshot Reports II: SQL Server Management Studio Perspective
Snapshot Reports I: Report Manager Perspective
Report Execution Caching II: Report Manager Perspective
Report Execution Caching I: SQL Server Management Studio Perspective
Report Session Caching in Reporting Services 2005
Black Belt Administration: Reporting Services Configuration Manager
Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I
Mastering OLAP Reports: Extend Reporting Services with Custom Code
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II
Black Belt Components: Support Simple Navigation with a Document Map
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I
Black Belt Components: Interactive Sorts within a Matrix Data Region
BlackBelt Authoring: Conditional Drillthrough to Multiple Reports
Mastering OLAP Reporting: Prototype KPIs in Reporting Services
BlackBelt Administration: Linked Reports in SQL Server Management Studio
BlackBelt Administration: Linked Reports in Report Manager
Mastering OLAP Reporting: Reporting with Analysis Services KPIs
Report Builder: Creating a Report Model
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1
Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header
Interactive Sorting Within Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist
MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX
MSSQL Server Reporting Services: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting
MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives
MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters
MSSQL Server Reporting Services: Black Belt Administration: "Governor" Capabilities: Report Execution Timeout
MSSQL Server Reporting Services : Black Belt Administration: Execution Log Performance and Audit Reports
MSSQL Server Reporting Services: Black Belt Administration: Prepare the Execution Log for Reporting
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports
MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports
MSSQL Server Reporting Services: Reporting Services Basics: Create a Reusable Template Report
MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart
MSSQL Server Reporting Services: Master Chart Reports: Pie Charts in Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data
MSSQL Server Reporting Services: Managing Reporting Services: Report Execution and Standard Subscriptions
MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads
MSSQL Server Reporting Services: The Authoring Phase: Overview Part II
MSSQL Server Reporting Services: The Authoring Phase: Overview Part I
MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers