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 Nov 20, 2006

Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I - Page 4

By William Pearson

28.  Select Properties from the context menu, as depicted in Illustration 15.

Illustration 15: Select Properties from the Context Menu...

The Matrix Properties dialog appears, defaulted to the General tab.

29.  Click the Groups tab.

30.  In the Columns section of the Groups tab (in the lower half of the tab), select the middle group, named matrix1_Sales_Territory_Group.

31.  Click the Edit button, as shown in Illustration 16.

Illustration 16: Editing the Matrix1_Sales_Territory_Group Column Group...

The Grouping and Sorting Properties dialog appears, defaulted to its General tab.

32.  Atop the General tab, change the existing Name to the following:


33.  Click the row containing the Expression value (currently the expression is "Fields!Sales_Territory_Group.Value"), within the Group on list, to enable the selector.

34.  Select =Fields!Month.Value within the selector, as depicted in Illustration 17.

Illustration 17: Replacing the Existing Group Expression...

35.  Click OK, to accept our modifications, and to close the Grouping and Sorting Properties dialog.

36.  Click OK on the Groups tab, to close the Matrix Properties dialog.

37.  Once again on the Layout tab, within the Matrix Data Region, right-click the value appearing underneath the Internet Orders column heading (the leftmost of the two remaining value cells).

38.  Select Properties on the context menu that appears, as shown in Illustration 18.

Illustration 18: Modifying Properties for the Count Value...

39.  On the Textbox Properties dialog that next appears, click the Format tab.

40.  Replace the existing Format code setting (in the upper left corner of the Format tab) with the following string:


Here we are simply changing the existing format to one more appropriate for a count value. The Format code appears on the Format tab of the Textbox Properties dialog as depicted in Illustration 19.

Illustration 19: Replacing the Existing Format Code...

41.  Click OK to accept our modifications, and to dismiss the Textbox Properties dialog.

We next need to change the column heading associated with the Group we modified earlier, so that it reflects Months, versus the previous grouping criteria of Sales Territory Group.

42.  Right-click the text box containing the column label (immediately above the two measure values’ column labels) in the Layout tab (the text box currently contains the expression =Fields!Sales_Territory_Group.Value).

43.  Select Expression... from the context menu that appears, as shown in Illustration 20.

Illustration 20: Modifying the Primary Column Label

The Expression Editor opens.

44.  Replace the expression in the upper portion of the Editor with the following:


Recall that the intent is to label the column to reflect the nature of the Group we have put in place: Months instead of Sales Territory Group.

45.  The Expression Editor appears, with our modification, as depicted in Illustration 21.

Illustration 21: The Expression Editor with Our Substitution in Place

46.  Click OK to accept our modification, and to dismiss the Expression Editor.

47.  Click the column heading for which we have just changed the expression, if necessary, simply to select the textbox.

48.  Change the Font Size, in the Format toolbar atop the design environment, from 12pt to 10pt, to make the presentation more compact, as shown in Illustration 22.

Illustration 22: Change the Font to 10pt...

All that remains to complete our preparatory steps is to remove the Report Parameter that remains. Recall that one of our earlier steps was to remove the Product entry from the Filter pane (the sample report from which our report was cloned came equipped with a parameterized Product filter). This entry had been established with the Parameter checkbox selected, a common way to create a parameterized filter, as we shall see. Because parameterization was selected for the Product filter, it follows that a Report Parameter might well have been created at the time the entry was made. We will now remove the remaining Report Parameter.

49.  From the main menu, select Report -> Report Parameters..., as shown in Illustration 23.

Illustration 23: Select Report -> Report Parameters...

The Report Parameters dialog opens, and we see a single Report Parameter listed.

50.  Ensure that the ProductCategory Parameter is selected.

51.  Click the Remove button, as depicted in Illustration 24.

Illustration 24: Removing the Residual Report Parameter...

52.  Click OK to accept our modifications, and to dismiss the (now empty) Report Parameters dialog.

We will execute the report to ascertain that our modifications are complete, and that we have a working report for the practice session that follows.

53.  Click the Preview tab, as shown in Illustration 25.

Illustration 25: Click the Preview Tab to Execute the Report

The Report is being generated message briefly appears, and then the report displays. The modified report appears as partially depicted in Illustration 26.

Illustration 26: The Modified Report (Partial View)

Our report is now ready for the practice session, which we began in the next section.

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