dcsimg

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

May 15, 2006

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

The Matrix 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:

=Fields!Subcategory.Value

The Expression 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"

The Expression 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 52.


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


Illustration 53: Select View --> Datasets to Resurrect the Datasets Window, if Required

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 following:

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

The Textbox 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:

C

The Textbox Properties dialog – Format tab appears as depicted in Illustration 59.


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


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.








The Network for Technology Professionals

Search:

About Internet.com

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