Procedure
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 KPIs – KPIs 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:
RS032_KPI_Matrix
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:
RS032_KPI_Support
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.
» 
Comment and Contribute
