Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1 - Page 8February 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 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 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 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.
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.
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.
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.
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
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
