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 5

By William Pearson


Prototype KPIs in Reporting Services

Add a New, Blank Report

Let's create a basic OLAP report that contains data upon which we will base examples of KPIsKPIs we can generate quickly and solely within Reporting Services, to fit the hypothetical business requirement we have outlined. We won't spend a great deal of time with formatting and other nuances of presentation – the point here is to illustrate a conceptual option for generating KPIs on a limited scale.

1.  In Solution Explorer, right-click the Reports folder, within the RS032_Simple_KPIs project that we added earlier

2.  Select Add from the context menu that appears.

3.  Select New Item ... from the context menu that cascades from the first, as depicted in Illustration 32.

Illustration 32: Select Add --> New Item ...

The Add New Item dialog appears.

4.  Click Report in the Templates pane, as required, to select it.

5.  Type the following into the Name box at the foot of the dialog:


6.  Click the Add button in the bottom right corner of the dialog, which should appear as shown in Illustration 33.

Illustration 33: Creating a New, Blank Report

RS032_KPI_Matrix.rdl, currently a blank canvas, opens within the design environment, and appears in the Solution Explorer, as depicted in Illustration 34.

Illustration 34: The New Report Appears within Solution Explorer

Create a Dataset to Support KPIs

Let's create a basic Dataset, upon which to base our new OLAP report.

1.  Click the Data tab within Report Designer, to open the Data view.

2.  Using the "down arrow" button to the right of the Dataset selector, atop the Data tab, click <New Dataset...>, as shown in Illustration 35.

Illustration 35: Adding a New Dataset ...

The Dataset dialog appears.

3.  Type the following into the Name box at the foot of the dialog:


The Dataset dialog appears, as depicted in Illustration 36.

Illustration 36: The Dataset Dialog for Our New Dataset

4.  Click OK to accept the new Dataset definition, and to dismiss the Dataset dialog.

The MDX Query Designer opens in Design view.

5.  Within the Metadata pane to the left of the design area, expand the Sales Territory dimension by clicking the "+" sign to its immediate left.

6.  Expand the Sales Territory hierarchy that appears underneath the dimension (bottom item within the dimension).

The Sales Territory hierarchy expands, exposing its members within the Metadata tree, as partially shown in Illustration 37.

Illustration 37: The Expanded Sales Territory Dimension and Hierarchy ...

7.  Drag the newly exposed Sales Territory Country into the Results pane of the Dataset design area, as depicted in Illustration 38.

Illustration 38: Adding Sales Territory Country to the Dataset ...

Sales Territory Country appears as a column heading in the design area. Let's add additional data fields that we will need in our query.

8.  Within the Metadata pane, once again, expand the Date dimension.

9.  Expand the Fiscal folder that appears within the Date dimension.

10.  Expand the Fiscal hierarchy that appears within the Fiscal folder (bottom item within the folder).

11.  Drag the newly exposed Fiscal Year into the Results pane area, dropping it to the right of the Sales Territory Country column, as shown in Illustration 39.

Illustration 39: Adding Fiscal Year to the Dataset ...

Fiscal Year now appears as a second column heading in the Results pane area. Let's add the measures we need to support the required KPIs.

12.  Within the Metadata pane, expand Measures.

13.  Expand the Internet Sales folder that appears within Measures.

14.  Drag the following two measures (actually calculations), in the order shown, into the Results pane area:

Internet Gross Profit Margin
Growth in Customer Base

Drop each measure, one after the other, to the right of the columns already in place, as depicted in Illustration 40.

Illustration 40: Adding Two Measures ...

15.  In the Filter pane, located in the upper right corner of the Query Designer, select Date via the Dimension column selector button.

16.  Select Date.Fiscal Year in the Hierarchy column, to the immediate right of the Dimension column, within the Filter pane.

17.  Select Equal in the Operator column.

18.  Select FY 2004, via the checkbox within the selector for the Filter Expression column, as shown in Illustration 41.

Illustration 41: Filtering by Fiscal Year 2004 ...

19.  Click OK to accept the setting for the Filter Expression and to close the selector.

The Query Designer appears, with our settings, as depicted in Illustration 42.

Illustration 42: The Query Designer with our Settings

NOTE: If the Results pane has not populated, as shown in the illustration above, click the Execute Query ("!") button in the toolbar atop the Query Designer, shown circled in Illustration 43.

Illustration 43: The Execute Query Button in the Toolbar

We now have the 2004 data we need to support our immediate KPI requirement in the steps that follow. While our requirement for this practice session is, of course, quite simple, we are aware that we can support more elaborate requirements by adding parameterization and other features, both here and elsewhere within Reporting Services.

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