dcsimg

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

February 21, 2006

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.

Conclusion...

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.

MSSQL Server Reporting Services
Introducing the Tablix Data Region: Basic Grouping Concepts
Introducing the Tablix Data Region in Reporting Services 2008
100% Stacked Column Chart for Analysis Services Data
XY (Scatter) Chart for Analysis Services Data
Simple Doughnut Chart for Analysis Services Data
Exploded Pie Chart for Analysis Services Data
Stacked Bar Chart for Analysis Services Data
Line Chart for Analysis Services Data
Stacked Column Chart for Analysis Services Data
A More Advanced Pie Chart for Analysis Services Data
Simple Pie Chart for Analysis Services Data
Simple Bar Chart for Analysis Services Data
Simple Column Chart for Analysis Services Data
Introducing Reporting Services Charts for Analysis Services
Mastering OLAP Reports: Parameterized Grouping
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I
Support Parameterization from Analysis Services - Parameter Defaults
Parameterization from Analysis Services - Cascading Picklists
Support Parameterization from Analysis Services
Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
Reporting Services: Customize Automatically Created Parameter Support Objects
Snapshot Reports II: SQL Server Management Studio Perspective
Snapshot Reports I: Report Manager Perspective
Report Execution Caching II: Report Manager Perspective
Report Execution Caching I: SQL Server Management Studio Perspective
Report Session Caching in Reporting Services 2005
Black Belt Administration: Reporting Services Configuration Manager
Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I
Mastering OLAP Reports: Extend Reporting Services with Custom Code
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II
Black Belt Components: Support Simple Navigation with a Document Map
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I
Black Belt Components: Interactive Sorts within a Matrix Data Region
BlackBelt Authoring: Conditional Drillthrough to Multiple Reports
Mastering OLAP Reporting: Prototype KPIs in Reporting Services
BlackBelt Administration: Linked Reports in SQL Server Management Studio
BlackBelt Administration: Linked Reports in Report Manager
Mastering OLAP Reporting: Reporting with Analysis Services KPIs
Report Builder: Creating a Report Model
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1
Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header
Interactive Sorting Within Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist
MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX
MSSQL Server Reporting Services: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting
MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives
MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters
MSSQL Server Reporting Services: Black Belt Administration: "Governor" Capabilities: Report Execution Timeout
MSSQL Server Reporting Services : Black Belt Administration: Execution Log Performance and Audit Reports
MSSQL Server Reporting Services: Black Belt Administration: Prepare the Execution Log for Reporting
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports
MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports
MSSQL Server Reporting Services: Reporting Services Basics: Create a Reusable Template Report
MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart
MSSQL Server Reporting Services: Master Chart Reports: Pie Charts in Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data
MSSQL Server Reporting Services: Managing Reporting Services: Report Execution and Standard Subscriptions
MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads
MSSQL Server Reporting Services: The Authoring Phase: Overview Part II
MSSQL Server Reporting Services: The Authoring Phase: Overview Part I
MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers