Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 19, 2007

Mastering OLAP Reports: Extend Reporting Services with Custom Code - Page 4

By William Pearson

15.  From our current position within the Data tab, select Report -> Report Parameters from the main menu of the design environment, as shown in Illustration 9.

Illustration 9: Select Report -> Report Parameters from the Main Menu

16.  Select the sole Report Parameter (“ProductCategory”) listed within the Parameters pane of the Report Parameters dialog, which appears next.

17.  Click Remove to delete the ProductCategory Report Parameter, as depicted in Illustration 10.

Illustration 10: Click Remove to Delete the Parameter ...

18.  Click OK, to accept our removal of the Report Parameter, and to dismiss the Report Parameters dialog.

This completes our modifications to the report from the Data tab. We will execute the query for the remaining dataset, once more, to ascertain that all is in working condition.

19.  Click the Execute Query button (!) in the toolbar, as we did earlier, to run the query once again.

The Results pane is populated once more, and appears as partially shown in Illustration 11.

Illustration 11: The Result Pane is Populated (Partial View)

We will now transit to the Layout tab, where we will wrap up our preparatory modifications.

20.  Click the Layout tab.

21.  Click the left value textbox within the Matrix data region, which contains =Sum(Fields!Internet_Order_Quantity.Value)”, and whose Background Color is Lavender.

22.  Press the Delete key on the keyboard, to delete the Internet Order Quantity measure (depicted in Illustration 12), along with the column containing it, from the matrix.

Illustration 12: Deleting the Internet Order Quantity Measure ...

The column disappears, leaving two remaining columns.

23.  Click the right value textbox remaining within the Matrix data region, which contains =Sum(Fields!Internet_Total_Product_Cost.Value)”, and whose Background Color is Transparent (and for which,therefore, the White background color appears).

24.  Press the Delete key on the keyboard, to delete the Internet Total Product Cost calculated value, along with the column containing it, from the Matrix, as we did for the measure before.

The modified Matrix data region, now presenting a single measure column, appears on the Layout tab as shown in Illustration 13.

Illustration 13: The Modified Data Region

Next, we will modify grouping within the Matrix data region.

25.  Click a point within the Matrix data region, to give it the focus, and to cause its gray handles to appear.

26.  Right-click the upper left-hand corner of the Matrix data region (the gray square).

The gray column and row bars disappear, as a light, patterned outline forms around the Matrix data region, and the context menu appears.

27.  Select Properties from the context menu, as depicted in Illustration 14.

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

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

28.  Click the Groups tab.

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

30.  Click the Edit button, as shown in Illustration 15.

Illustration 15: Editing the Matrix1_Sales_Territory_Group Column Group ...

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

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


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

33.  Select =Fields!Sales_Territory_Country.Value within the selector, as depicted in Illustration 16.

Illustration 16: Replacing the Existing Group Expression ...

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

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

36.  Once again on the Layout tab, within the Matrix data region, right-click the textbox appearing above the Internet Sales column heading (it currently contains the expression “=Fields!Sales_Territory_Group.Value”).

37.  Select Properties on the context menu that appears, as shown in Illustration 17.

Illustration 17: Modifying Properties for the Column Value ...

38.  On the Textbox Properties dialog that next appears (defaulted to the General tab), change the existing Name to the following:


39.  Within the Value selector, modify the existing expression to the following:


Our modifications within the Textbox Properties dialog – General tab, appear as depicted in Illustration 18.

Illustration 18: Our Modifications within the Textbox Properties Dialog – General Tab

40.  Click OK to accept our modifications, and to close the Textbox Properties dialog.

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

41.  Click the Preview tab.

The Report is being generated message briefly appears, and then the report displays. The modified report appears as shown in Illustration 19.

Illustration 19: The Modified Report

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

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



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