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 Oct 20, 2005

MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX - Page 8

By William Pearson

The information consumers have also requested that the Product Name appear in the report; we will now add another group, subordinate to the Product Brand Name group, while we are "under the hood" with regard to the matrix properties.

42.  Click the Add button to the right of the rows list on the Groups tab.

The Grouping and Sorting Properties dialog appears, once again.

43.  Type the following into the Name box of the dialog.

BrandSales_Product_Name

44.  Click the top row in the Group on list, just beneath the Expression label, to expose the selector.

45.  Using the selector, select the following from the picklist provided to populate the top row of the list:

=Fields!Product_Product_Name.Value

The selector list, with our selection circled, appears as depicted in Illustration 42.

Click for larger image

Illustration 42: Selecting the Product Name Field as an Additional Group for the Report ...

The Grouping and Sorting Properties dialog, General tab, with our input, appears as shown in Illustration 43.


Illustration 43: Selecting the Product Name Field as an Additional Group for the Report ...

46.  Click the Sorting tab.

47.  Click the top row in the Sort on list, just beneath the Expression label, to expose the selector.

48.  Using the selector, select the following from the picklist provided to populate the top row of the list:

=Fields!Product_Product_Name.Value

The Grouping and Sorting Properties dialog, Sorting tab, with our input, appears as depicted in Illustration 44.


Illustration 44: Product Name Field as Sort Criteria ...

49.  Click OK to accept our input.

The Grouping and Sorting Properties dialog closes, returning us to the Groups tab of the Matrix Properties dialog, which now appears as shown in Illustration 45.


Illustration 45: The Groups Tab with Our Changes

50.  Click OK to accept changes and to close the Matrix Properties dialog.

We return to the Layout tab, where our report appears similar to that depicted in Illustration 46.


Illustration 46: Modified Report Layout ...

Let's rename the report, and eliminate all except the specified measure, Store Sales.

51.  Right-click the upper left textbox in the matrix, where we currently see the title FoodMart Sales in a white background.

52.  Select Expression... from the context menu that appears, as shown in Illustration 47.


Illustration 47: Modifying the Report Title ...

The Edit Expression dialog appears.

53.  Replace FoodMart Sales, within the Expression pane, with the following new title:

Product Summary - by Brand

The Edit Expression dialog appears as depicted in Illustration 48.


Illustration 48: Replacing the Existing Expression ...

54.  Click OK to accept the new expression and to close the Edit Expression dialog.

55.  With the textbox containing our new title still selected, replace the current font size with "16," via the selector on the Report Formatting toolbar, located between the Report Designer and the main menu, as shown in Illustration 49.


Illustration 49: Changing the Font Size in the Report Formatting Toolbar ...

NOTE: If the Report Formatting toolbar does not appear, summon it by selecting View --> Toolbars --> Report Formatting from the main menu, as partially depicted in Illustration 50.


Illustration 50: Summoning the Report Formatting Toolbar (Partial Menu View) ...

56.  Click the Store Profit measure expression, under the Store Profit label, once to select it, as shown in Illustration 51.


Illustration 51: Select with a Single Click ...

57.  Press the Delete key on the PC to delete the Store Profit measure column entirely.

58.  Perform the same select and delete process for the Store Cost measure.

The Layout tab for the report appears similar to that depicted in Illustration 52.


Illustration 52: The Layout Tab with Our Changes ...

59.  Open the Fields list for the ProductData Dataset we have defined. (If it is not docked in the development environment, open it by simply selecting View --> Fields from the main menu, as shown in Illustration 53).


Illustration 53: Select View --> Fields, as Required ...

60.  Right-click the Store Profit calculated field (it appears at the bottom of the fields list).

61.  Select Delete from the context menu that appears, as depicted in Illustration 54.


Illustration 54: Deleting the Store Profit Calculated Field ...

Let's take a look at the results of our handiwork so far.

62.  Click the Preview tab to execute the report.

RS022_MDX_DRILLTHROUGH executes briefly, and then returns data. Our report appears similar to that shown in Illustration 55.


Illustration 55: RS022_MDX_DRILLTHROUGH Appears in Preview (Partial View)

NOTE: Widen the columns as desired to prevent crowding / word wrapping in the data fields.

63.  Select File --> Save All to save all work to the present point.

We have now completed preparation of the primary report, which we will enhance in the section that follows to allow for drillthrough to a targeted detail report, which we will also create and align with the primary report.



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