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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 20, 2004

MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports - Page 7

By William Pearson

19.  Click the Edit button to the right of the highlighted group.

The Grouping and Sorting Properties dialog - General tab appears.

20.  Replace the contents of the Name box atop the tab, BrandSales_Brand_Name, with the following:

Product_Product_Name

21.  In the Group On list, use the selector button to the right of the top line in the list, to modify the existing expression, =Fields!Brand_Name.Value, to the following expression:

=Fields!Product_Product_Name.Value

Illustration 25 depicts scrolling down on the selector to select the replacement expression.

Click for larger image

Illustration 25: Select the New Row Group Expression ...

The Grouping and Sorting Properties dialog - General tab appears, as shown in Illustration 26.


Illustration 26: Grouping and Sorting Properties Dialog - General Tab

Within the Matrix Properties dialog - Group tab, we note that the bottom row group now reflects the name change we made in the Grouping and Sorting Properties dialog.

22.  Click OK to accept modifications, and to close the dialog.

23.  Click OK to close the Matrix Properties dialog.

We return to the Layout tab. We now need to make another modification to continue with the drill-down-related changes requested by the information consumer group.

24.  Right-click the textbox fourth column from the left / right (currently showing "= Fields!Brand_Name.Value" in the Layout view.

25.  Select Properties from the context menu that appears.

The Textbox Properties dialog appears.

26.  Replace "Brand_Name" in the Name box with the following:

Product_Name

27.  Select the following by clicking the selector for Value, just below Name, to replace the current "= Fields!Brand_Name.Value" :

=Fields!Product_Product_Name.Value

The Textbox Properties dialog appears (modifications circled) as depicted in Illustration 27.


Illustration 27: Textbox Properties Dialog - Modifications Circled

28.  Click OK to close the Textbox Properties dialog.

We return to the Layout tab. We have another set of minor modifications to make to complete the structural changes that the intended audience has requested: to remove all except the lowest two current drilldown levels, Product Subcategory and Product Name, which the intended audience wants to see visible together in the new report. For this, we will need to return to the Matrix Properties dialog, Group tab, where we were earlier.

29.  Click anywhere within the Matrix data region to activate the gray column and row headers.

30.  Right-click the upper right corner of the Matrix data region.

31.  Select Properties from the bottom of the context menu that appears, as we did earlier.

The Matrix Properties dialog - General tab appears.

32.  Click the Groups tab.

33.  Click the BrandSales_Product Category item that appears within the Rows group list, as shown in Illustration 28.


Illustration 28: Select BrandSales_Product Category Row Group ...

34.  Click the Delete button to the right of the highlighted group.

The BrandSales_Product Category group disappears.

35.  Click the BrandSales_Product Department item that appears atop the Rows group list.

36.  Click the Delete button to the right of the highlighted group.

The BrandSales_Product Department group disappears.

37.  Click the BrandSales_Product Subcategory item within the Rows group list.

38.  Click the Edit button to the right of the highlighted group.

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

39.  Click the Visibility tab to access it.

40.  Under Initial visibility, select the Visible option by clicking the radio button to its immediate left.

41.  Uncheck Visibility can be toggled by another report item by clicking the checkbox to its immediate left.

The Grouping and Sorting Properties dialog - Visibility tab appears, as shown in Illustration 29.


Illustration 29: Grouping and Sorting Properties Dialog - Visibility Tab

42.  Click OK.

We return to the Matrix Properties dialog - Groups tab.

43.  Click the Product_Product Name item within the Rows group list.

44.  Click the Edit button to the right of the highlighted group.

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

45.  Click the Visibility tab to access it.

46.  Under Initial visibility, select the Visible option by clicking the radio button to its immediate left.

47.  Uncheck Visibility can be toggled by another report item by clicking the checkbox to its immediate left.

48.  Click OK.

We return to the Matrix Properties dialog - Groups tab.

49.  Click OK, again.

We return to the Layout tab in Report Designer. We are now ready to test the report to preview the results of our structural changes.

NOTE: Ensure that only the Product Subcategory and Product Name columns remain, from what was previously the Product hierarchy drilldown, before proceeding.

50.  Click the Preview tab.

51.  Select Food from the parameter picklist that appears atop the report on the Preview tab.

52.  Click the View Report button to execute the report.

The report runs and returns the data associated with the Food Product Family that resides in the MSAS Sales cube. At this point, we see that the new lowest level of the row axis, Product Name, appears. We note, too, the appearance of Product Subcategory level, and that the other levels previously in place have disappeared, as the information consumers had requested.

A portion of our view of the report at this stage appears in Illustration 30.


Illustration 30: The AdHocCondFormat_Foodmart Sales Report (Partial View) with Layout Modifications



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


















Thanks for your registration, follow us on our social networks to keep up-to-date