Featured Database Articles
Posted May 15, 2006
Mastering OLAP Reporting: Reporting with Analysis Services KPIs - Page 6
By William Pearson
31. In the Columns list in the
bottom half of the Groups tab, select the middle group (matrix1_Sales_Territory_Group).
32. Click the Delete button, as shown in Illustration 47.
Illustration 47: Deleting
a Column Group
Properties dialog - Groups tab appears, with our modifications, as depicted
in Illustration 48.
Illustration 48: Matrix
Properties Dialog Groups Tab with Modifications
33. Click OK to accept our
changes, and to return to the Layout tab.
34. Right-click the textbox for the Rows
group, currently containing the expression "=Fields!Sales_Reason.Value."
35. Select Expression from the
context menu that appears, as shown in Illustration 49.
Illustration 49: Modifying
the Expression for the Rows Group Textbox
36. Replace the current occupant of
the Expression box, atop the Expression Editor that appears next,
with the following:
Editor, with our new expression, appears as depicted in Illustration 50.
Illustration 50: Expression
Editor New Rows Group Textbox Expression
37. Click OK to accept the new
expression and to dismiss the Expression Editor.
38. Right-click the column label
textbox, currently labeled Sales Reason, above the Rows group textbox.
39. Select Expression ... from
the context menu, once again.
40. Replace the current occupant of
the Expression box, again atop the Expression Editor that appears
next, with the following:
=""& vbcrlf & vbcrlf &" Product Subcategory"
Editor, with our new expression, appears as shown in Illustration 51.
Illustration 51: Expression
Editor New Textbox Label Expression
41. Click OK to accept the new
expression and to dismiss the Expression Editor.
We are now ready to bring the KPI values into the
matrix from Datasets window, where they currently appear as fields.
42. Open the Datasets window
with the tab to the left of the Layout tab, as depicted in Illustration
Illustration 52: Open
the Datasets Window
NOTE: If the tab is not in its default position, open the
window by selecting View ---> Datasets,
as shown in Illustration
Illustration 53: Select
View --> Datasets to Resurrect the Datasets Window, if
43. Within the Datasets window,
expand the ProductData Report Dataset, so that all four Internet
Sales KPI members are exposed.
44. Drag the Internet Revenue
Status field from the Datasets window to the empty Data field
within the matrix data region, on the canvas in Layout view, as
depicted in Illustration 54.
Illustration 54: Adding
the KPI Field to the Matrix Data Region
45. Drag the Internet Revenue Trend
field from the Datasets window to the immediate right of the Data
field into which we dropped the Internet Revenue Status field in the
last step. The right border of the b field will assume a white bracket shape
when the positioning is correct to drop the field and create a new column, as
shown in Illustration 55.
Illustration 55: The
Right Border Indicates that the Drop Point is Correct for a New Column ...
A new Data
field / column is created when we drop the Internet Revenue Trend field to the immediate right of
the Internet Revenue Status field.
46. In like manner, drag the Internet
Sales Amount field from the Datasets window to the immediate right
of the Data field into which we dropped the Internet Revenue Trend field.
47. Finally, drag the Internet
Revenue Goal field from the Datasets window to the immediate right
of the Internet Sales Amount field.
48. Right-click the left most Data
column heading in the matrix data region (currently labeled Internet
Total Product Cost).
49. Select Expression... from the
context menu that appears, as depicted in Illustration 56.
Illustration 56: Changing
the Pre-Existing Column Heading ...
50. Within the expression box at the
top of the Expression Editor, replace the existing expression with the
Internet Revenue Status
51. Click OK to accept the
modification and dismiss the Expression Editor.
52. Holding down the SHIFT key,
click the Data column heading for each of the four new Data
fields, to select them simultaneously.
53. Click the Center alignment
button in the toolbar, as depicted in Illustration 57.
Illustration 57: Centering
the Column Labels ...
54. Finally, right-click the Internet
Sales Amount Data field (the Data field to the immediate left
of the far right field).
55. Select Properties from the
context menu that appears, as shown in Illustration 58.
Illustration 58: Select
Properties from the Context Menu ...
Properties dialog opens defaulted to the General tab.
56. Click the Format tab.
57. Type the following into the Format
code box in the upper left corner of the tab:
Properties dialog Format tab appears as depicted in Illustration
Illustration 59: Textbox
Properties-Format Tab, Format Code Setting
58. Click OK to accept the
change and to close the Textbox Properties dialog.
59. Perform the same Format
setting accomplished in the last few steps for the far right Data field,
Internet Revenue Goal.
60. Holding down the SHIFT key,
once again, click the Row group label textbox (containing "=Fields!Subcategory.Value"
) and the data textboxes for each of the four new Data fields, to select
them simultaneously, as we did earlier with their column heading textboxes.
61. Select 9 in the Font
Size selector, on the left side of the Report Formatting toolbar
(which we accessed earlier for the Center button, when aligning the
column headings), atop the design environment, as shown in Illustration 60.
Illustration 60: Slightly
Reducing the Font for the Data Fields ...
The Analysis Services KPIs report, in Layout view, with our KPI and column
label additions, appears as depicted in Illustration 61.
Illustration 61: Analysis
Services KPIs Report Layout View
62. Click the Preview tab.
The report is executed, and appears as partially shown in Illustration
Illustration 62: Generated
Report in Preview (Partial View)
A cursory review of the report's pages reveals that the
sample data appears to match Internet Sales Amount with Internet
Revenue Goal in every case where we have a non-zero number. This is not
surprising for a sample data set, but we are not impaired in our immediate
pursuits, as we have occurrences of -1, 0, and 1 throughout the report. We will
not delve into the KPIs themselves at this point we have what we need
to move to the next (and concluding) section.
63. Return to the Layout tab.
MS SQL Archives