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 Feb 21, 2006

Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1 - Page 8

By William Pearson

We will now conclude the first half of our procedure by performing a preliminary grouping within the matrix, to ascertain that the values returned are consistent with the original data region. (We will continue our steps in reaching the ultimate requirements in Part 2.)

41.  Click the upper left box in the matrix (where we placed the title and image files) to select it.

The gray header bars (or "handles") appear for the matrix.

42.  Right-click the upper left hand corner of the gray outline around the matrix, as shown in Illustration 37.

Click for larger image

Illustration 37: Getting to the Matrix Properties ...

43.  Select Properties from the context menu that appears (the gray outline has now become a light border around the matrix), as depicted in Illustration 38.

Click for larger image

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

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

44.  Click the Groups tab.

45.  Click the Add button to the right of the Columns group list, in the bottom half of the dialog, as shown in Illustration 39.

Click for larger image

Illustration 39: Click the Add Button for Column Groups ...

The Grouping and Sorting Properties dialog opens.

46.  In the top row of the Expression box, within the Group on section, select "=Fields!Sales_Territory_Group.Value," as depicted in Illustration 40.

Illustration 40: Performing Column Grouping on Sales Territory Group ...

47.  Click OK to accept our input and close the Grouping and Sorting Properties dialog.

We return to the Groups tab.

48.  With the new column group selected, click the Up button to the right of the Columns group list, to raise the new group above the pre-existing static group.

The Groups tab appears, with the new group, as shown in Illustration 41.

Illustration 41: The New Group in Place ...

49.  Click OK to accept our input and to close the Matrix Properties dialog.

We return to the Layout tab, where we see the new group appear within the columns of the matrix.

50.  Select the top two, of the three, rows of the matrix, by holding down the SHIFT key and clicking the gray borders to their left.

51.  Modify the Height property within the Properties pane to .5in, as depicted in Illustration 42.

Illustration 42: Modify the Height of the Top Two Rows to .5 Inches ...

Our new matrix is now ready to test. While we remain a few steps from meeting the complete business requirement of the information consumers, it is important to test our results – in this case against the conveniently placed, pre-existing data region that is assumed to return the same data – before proceeding with the more involved steps that we undertake in Part 2.

52.  Adjust the objects in the upper left corner box of the matrix, as necessary, to align them fully inside the box.

53.  Select File --> Save All to save our work to this point.

54.  Click the Preview tab to execute the report.

Our report executes with the default parameter selection, and returns the two data regions closely aligned for easy comparison. We see that the values we have delivered within the new matrix agree with those that appear within the original data region, as shown in Illustration 43.

Illustration 43: The Two Regions Appear, with Comparable Results ...

We now have a matrix data region that accurately and completely replicates the results of the pre-existing data region. In our next article, we will parameterize the territories, and then engage the more unusual challenge to build in the automatic generation of separate data regions for each territory selected at run time.

55.  Save RS026_Sales_Reason_Comp_Matrix.rdl in a safe location for access in Part 2.

56.  Exit Reporting Services when ready.


In this article, we conducted the first half of an examination of a scenario where the dynamic nature of the Reporting Services matrix data region makes it the "object of choice" for enabling us to meet the expressed needs of a hypothetical group of information consumers. Part of the requirement was to replace a somewhat limited pre-existing matrix data region with a new matrix data region that returned identical data. We accomplished this step, verifying comparability between results displayed in each of the data regions, in concluding our preparation to take on the more non-intuitive steps that we will examine in Part 2.

As a rapid way of preparing the matrix data region to meet the needs of the information consumers, while providing an immediate means of verifying the accuracy of the new data region, we built the new matrix side-by-side with the existing data region. We began by adding a matrix data region to the report file, which we accessed within the Sample Report Server Project that is available with an MSSQL Server 2005 installation. We ascertained connectivity of the Shared Data Source, and opened the existing sample OLAP report that we sought to replicate, overviewing its capabilities and limitations. We then duplicated the data returned by the existing sample OLAP report within our new matrix data region, where we added grouping to present the data in a way that afforded "apples-to-apples" comparability to the data in the original matrix data region.

In our next article, we will continue our efforts with the matrix data region, and will deliver a solution that completely meets the expressed requirements of the client consumer group. We will add parameterization (with multivalue input capabilities) for territorial regions, using a multivalue parameter, and then make further structural changes to the report, to meet the business requirements for presenting independent matrices based upon a geographical parameter to be selected by the consumers at runtime. We will discuss the results obtained within the development techniques that we exploit throughout the steps of our practice exercise, as we have done to this point, and will conclude with a preview of the report to ascertain the effectiveness of our solution.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

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