Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II

Monday Mar 20th 2006 by William Pearson
Share:

Generate duplicate data regions for each of multiple business territories, based upon selections chosen by the consumer at run time. In this article, Architect Bill Pearson leads hands-on practice in delivering an "off-the-beaten path" result, courtesy of the power of the matrix data region.

About the Series ...

This article, Part Two of Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services ("Reporting Services"), with the objective of presenting an overview of its features, together with tips and techniques for real-world use. For more information on the series, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

As I have stated since the charter article of the series, published about the time Reporting Services was first publicly released, my conviction is that Reporting Services will commoditize business intelligence, particularly in its role as a presentation component within an integrated Microsoft BI solution. Having been impressed from my first exposure to this exciting application, when it was in early beta, my certainty in its destiny grows stronger by the day, as I convert formerly dominant enterprise business intelligence systems, such as Cognos, Business Objects, MicroStrategy, Crystal, and others, to the Reporting Services architecture. I receive constant requests to conduct strategy sessions about these conversions with large organizations in a diverse range of industries – the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five- to six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

Note: To follow along with the steps we undertake within the articles of this series, the following components, samples and tools are recommended, and should be installed / accessible, according to the respective documentation that accompanies MSSQL Server 2005:

Server Requirements

  • Microsoft SQL Server 2005 Reporting Services

  • Microsoft SQL Server 2005 Database Services

  • The AdventureWorks sample databases

  • Microsoft SQL Server 2005 Analysis Services

  • The AdventureWorks OLAP cube

Client Requirements

  • Microsoft Internet Explorer 6.0 with scripting enabled

  • Business Intelligence Development Studio (optional)

Sample Files

We noted in Part One of this article that we would be using one of the AdventureWorks sample reports in the practice sections, to save time and focus for the subject matter of the article. For more information on the AdventureWorks sample reports, and their installation, as well as other application and component considerations, please see Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1.

About the Mastering OLAP Reporting Articles ...

One of the first things that become clear to "early adopters" of Reporting Services is that the "knowledgebase" for OLAP reporting with this tool is, to say the least, sparse. As I stated in my article, Mastering OLAP Reporting: Cascading Prompts, the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the reporting solutions of well-established, but expensive, solutions, such as Cognos PowerPlay, can be met in most respects by Reporting Services – at a tiny fraction of the cost.

The vacuum of documentation in this arena, to date, represents a serious "undersell" of Reporting Services, from an OLAP reporting perspective. I hope to contribute to making this subject matter area more accessible to everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, rest assured that the combined relational and OLAP reporting potential in Reporting Services will be yet another reason that the application commoditizes business intelligence.

For more information about the Mastering OLAP Reporting articles, see the section entitled "About the Mastering OLAP Reporting Articles" in my article Ad Hoc TopCount and BottomCount Parameters.

Overview

As I have shown in many past articles, including Part One of this session, the Microsoft Integrated BI Solution, consisting of the MSSQL Server 2005 Database Engine, Analysis Services 2005 and Reporting Services 2005, provides unprecedented flexibility in helping implementers and developers to meet client and employer needs. My ongoing conversion of existing enterprise business intelligence applications to the Microsoft BI solution place me in a rather unique position to meet diverse needs with features to which my clients have become accustomed in working within once-dominant business intelligence applications. Reporting Services is particularly strong in the flexibility department: Presentation nuances are legion, and one discovers, with constant use of Reporting Services to meet a wide range of reporting needs, how truly flexible the application can be.

As I discussed in Part One, when one is in the business of performing conversions (for example, converting Cognos PowerPlay or Cognos Impromptu reports to Reporting Services), and even when implementing an evolving body of reports for the first time, the foremost challenges that we encounter are those that arise based upon the need to replicate features that exist in previously created reports, quite often in tandem with a requirement to enhance the reports to provide new capabilities and presentation options. Whether as a part of converting the reports of a predecessor enterprise reporting system, or to meet a need to duplicate early efforts within Reporting Services itself within enhanced reports that offer more flexibility, additional features, and so forth (as is the scenario within the Practice sessions of this article), we will consistently find that Reporting Services not only allows the knowledgeable designer / developer to exceed the capabilities of traditional reporting, but affords an environment within which development can become rapid and "Edison-esque" in its "assembly-line" nature.

In Part One, we began the 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 data region with a matrix data region that returned identical data. Creating the new matrix data region within the same .rdl file as the original matrix data region upon which it was based afforded us several advantages, both in rapid development and in ongoing "side-by-side" contrast as we "converted" the original data region to a new region, from which we could launch development of the enhancements we examine in this article. Having the two regions physically parallel made verification of comparability between them easy, as we finished the first half of our examination by ensuring that both regions gave us identical results.

In this article, we will further evolve the matrix, which we have already determined to be correct as a part of preparation, to meet the less intuitive components of the business requirements of the information consumers with which we are working. First, we will add the requested parameterization (with multivalue input capabilities) that they have requested for Sales Territory Groups, using a multivalue parameter. Next, we will make further structural changes to the report, to meet their somewhat innovative business requirements for presenting independent matrices based upon a geographical parameter to be selected by the consumers at runtime. As is our tendency throughout the body of our hands-on articles, we will discuss the results obtained within the development techniques that we exploit throughout the steps of our practice exercise. Finally, we will conclude with a preview of the report to ascertain the effectiveness of our solution.

In the foregoing sections, we will continue our examination of a scenario where the dynamic nature of the matrix helps us to meet the expressed needs of a hypothetical group of information consumers. In this, the second half of a two-part session, we will:

  • Add parameterization (with multivalue input capabilities) for Sales Territory Groups to the new report, using a multivalue parameter;
  • Make structural changes to the report, to meet the business requirements of a hypothetical group of information consumers for presenting independent matrices based upon a geographical parameter they select at runtime;
  • Continue to incrementally preview the report to ascertain the effectiveness of our solution;
  • Extend the ongoing discussion that we began interjecting in Part One, at appropriate junctures, surrounding the results obtained within the development techniques that we exploit throughout our second practice session.

Meet Business Needs with Matrix Dynamics – Continued ...

Objective and Business Scenario

Expressing my opinion in Part One that the matrix data region is one of the most valuable tools in the Reporting Services toolbox, I further opined that this is often nowhere more true than when one is employing the application to generate rich (and clever) presentations based upon OLAP cubes. The forehanded use of the matrix data region, as we have seen to be the case with many other Reporting Services objects within articles of my MSSQL Server Reporting Services series, can enable a report author or developer to accomplish many things that do not seem possible "out of the box," and often in ways that are impossible within other popular enterprise reporting applications.

In the following sections, we will continue the illustration we began in Part One of how we might artfully employ the matrix data region to achieve objectives that are beyond the limitations of the vanilla matrix or table data regions. Recall that, to provide a report upon which we could practice the steps of our hands-on exercise, we began with a copy of the Sales Reason Comparisons sample report, whose data source was the Adventure Works cube contained within the Analysis Services database, Adventure Works DW, which is available with the installation of the MSSQL Server 2005 samples. The sample Sales Reason Comparisons report is intended to present comparison summary data from the Adventure Works cube.

For the purposes of our two-part article, we created a setting within which we were working with a team of information consumers within the Office of the Vice President - Marketing of our client, the Adventure Works organization. To illustrate the business requirements of this client group, we said that the consumers had expressed the need for modifications to their existing Sales Reason Comparisons report, informing us that the report as it appeared at the time of our dialog, would serve as an excellent basis for newly extended requirements. The columns and rows of the report were consistent with the presentation objectives of the report they next envisioned. Among the limitations that made the existing report less than adequate was its nature as a static report. While its inflexible depiction of information for various territory groups of the organization might have been adequate, they told us, before the advent of the new portals that have gradually become the engines for information distribution within Adventure Works, the new need is for this information to be presented in a manner that allows analysts and other consumers to select one or more territories to view at runtime, rather than to see all territories together, as they appear anytime the existing report is executed.

In addition to parameterization, the consumers tell us that they want an even more innovative feature: they want "complete reports" (axes and all) to appear for each of the territories selected. This, they have told us, is because the report under consideration will appear in a portal window that we expect to only be large enough to present a single territory group at a glance, but for which a scroll bar (or, alternatively, a paging mechanism) will appear when, say, multiple territories appear in the window, so that users can scroll (or page) over to see all as needed. Scrolling over from one territory's data to the next, either with the existing report or even a "standard" matrix report (which shares the row axis among dynamic columns), however, would mean that the row axis would not appear in the presentation for the territories that we brought into view by scrolling right. For this reason, among others where the report will be presented via other mechanisms, the consumers wish for multiple territories to be presented as multiple stand-alone report objects / views.

In Part One, we worked with the team to construct a rough draft that represented the way that their then-current report would look within the scenario that they had requested we help them to accommodate. The draft we constructed, providing an example of the Sales Reason data for three territory groups, is replicated, once again, in the spreadsheet shown in Illustration 1.


Illustration 1: Rough Draft of the Proposed Presentation Layout – Multiple Territories Selected

Having grasped the stated need, and confirmed our understanding with the intended audience, we began the process of modifying the Sales Reason Comparisons report to satisfy the information consumers. We will resume where we left off in Part One, within the Practice section that follows.

Practice

We left our practice session in Part One with a matrix data region that accurately and completely replicated (we could easily compare them side-by-side) the results of the pre-existing matrix data region.

Preparation: Continue to Prepare the Environment for Report Enhancement

Before we parameterize the Sales Territory Groups, the next step in our plan to meet the business requirements of the information consumers, let's eliminate the matrix data region that we have replaced to free space on the design canvas. We will do so by taking the following steps.

1.  Restart the SQL Server Business Intelligence Development Studio.

We again briefly see a splash page that lists the components installed on the PC, and then Visual Studio .NET 2005 opens at the Start page.

2.  Close the Start page, if desired.

3.  Select File --> Open from the main menu.

4.  Click Project / Solution ... from the cascading menu, as shown in Illustration 2.

Click for larger image

Illustration 2: Selecting a Project ...

The Open Project dialog appears.

5.  Browse to location where we left the AdventureWorks Sample Reports solution at the conclusion of Part One.

NOTE: It is necessary to have completed Part One to participate in this practice session.

6.  Select the AdventureWorks Sample Reports.sln file within the appropriate folder, as we did in Part One.

7.  Click Open.

The AdventureWorks Sample Reports solution opens, and we see the various objects within appear in Solution Explorer, as we left them. In addition, the report we created in Part One, RS026_Sales_Reason_Comp_Matrix.rdl, opens, unless we closed it before closing the solution at the conclusion of our last session. (If we closed it last session, right click it in the Solution Explorer, and select Open from the context menu that appears, to open it.)

The report file appears open upon the Designer canvas as depicted in Illustration 3.


Illustration 3: The Report Appears within BI Development Studio ...

Let's resave the .rdl file with a name that matches our current article, so that we can retrace our steps in Part 1 with the original .rdl file, should the need arise.

8.  Select File --> Save RS026_Sales_Reason_Comp_Matrix.rdl As ... from the main menu, as shown in Illustration 4.


Illustration 4: Resaving the Report for the Current Session ...

9.  Within the Save File As dialog that next appears, leaving the folder location the same as it was for the previous report, replace the current report File name with the following:

RS027_Sales_Reason_Comp_Matrix.rdl

The Save File As dialog appears, with the name replacement, as depicted in Illustration 5.


Illustration 5: The Report Appears within BI Development Studio ...

10.  Click Save to resave the report under the new name, and to dismiss the Save File As dialog.

The report file appears, with the new name, in the Solution Explorer. Because we have used the original matrix data region to corroborate the correctness of the results as presented by the new matrix data region, we can discard the former to free up space on the canvas for the further enhancements we will perform in the next section.

11.  Click inside the upper left corner text box (where the image and Sales Reason label appear) of the top matrix data region (the original upon which we based our new data region), to make the gray column and row bars appear.

12.  Click the upper left corner of the gray bars, to select the matrix data region itself.

The matrix data region becomes highlighted with an outline, as shown in Illustration 6.


Illustration 6: Highlight the Original Data Region ...

13.  Touch the Delete key on the PC to delete the original data region.

The original data region disappears, leaving blank space atop the canvas.

14.  Lasso the new matrix data region, by clicking to the bottom left of the region, and then holding the left mouse button down, as you extend the outline that appears (a dotted line) completely around the data region, as depicted in Illustration 7.


Illustration 7: "Lasso" the New Data Region ...

15.  Release the mouse button, and ensure that the matrix data region, together with all the individual objects it contains, is highlighted, as shown in Illustration 8.


Illustration 8: New Data Region and all Member Components Selected ...

NOTE: It is important to ensure that all objects are selected, or our attempts to move the matrix data region in the next steps may leave objects behind.

16.  With the objects selected / highlighted, use the "arrow" keys to move the data region and all individual components to the upper left corner of the canvas, as depicted in Illustration 9.


Illustration 9: Relocate the Region Using the "Arrow" Keys ...

We are now ready to continue with the enhancements requested by the information consumers.

Procedure: Enhance the Report per the Business Requirements

Add a Multivalue Parameter for Sales Territories to the Report

We will add parameterization (with multivalue input capabilities) for Sales Territory Groups before making further structural changes to support the more involved business requirements described by the client representatives.

1.  Click the Data tab for the report.

We arrive at the Data view, where we see the ProductData Dataset open in Design Mode, as shown in Illustration 10.

Click for larger image

Illustration 10: The ProductData Dataset in Design Mode

NOTE: If the Data tab opens with another Dataset, or if it opens in Query Mode, make the necessary changes in the DataSet selector and / or the Design Mode toolbar button, respectively, as appropriate.

2.  Click the "Click to execute the query" link (in the middle of the Results pane) to populate the Results pane.

The Results pane appears as depicted in Illustration 11.

Click for larger image

Illustration 11: The Populated Results Pane

3.  Select Sales Territory in the Dimension selector, underneath the Product selection already in place, within the Filter pane atop the Results pane, as shown in Illustration 12.

Click for larger image

Illustration 12: Select Sales Territory within the Dropdown Dimension Selector ...

4.  Select Sales Territory Group in the Hierarchy selector, to the immediate right of the Dimension selector within the Filter pane.

5.  Select Equal in the Operator selector, to the immediate right of the Hierarchy selector.

6.  Check the All Sales Territories checkbox within the Filter Expression selector, to the immediate right of the Operator selector, as depicted in Illustration 13.


Illustration 13: Select All Sales Territories within the Filter Expression Selector ...

7.  Click OK on the Filter Expression selector to accept our selection and close the dropdown selector.

8.  Click the checkbox in the Parameters column of the Filter pane, to the immediate right of the Filter Expression selector.

Because we have selected to parameterize the query for Sales Territory Groups, Report Designer will automatically create the components we need to support the parameterization we have requested. Among the components created are a Dataset to support the Sales Territory Group parameterized filter, as well as the Report Parameter that goes along with the new Sales Territory Group parameter. The Sales Territory Group Dataset is actually used in conjunction with the Report Parameter to populate the runtime picklist that appears when we execute the report.

We can prepare to observe the very act of creation of these components within Report Designer, by taking the following steps.

9.  Select Report --> Report Parameters from the main menu, as shown in Illustration 14.


Illustration 14: Select Report --> Report Parameters ...

The Report Parameters dialog opens. We see a single parameter listed, the ProductCategory parameter, as presented in the partial dialog depicted in Illustration 15.


Illustration 15: Single Parameter Initially Appears ...

10.  Click OK to dismiss the Report Parameters dialog.

11.  Click the downward arrow on the Dataset selector for the report file.

We note that two Datasets appear, ProductData and ProductList, as shown in Illustration 16.


Illustration 16: Two Pre-existing Datasets Appear ...

Our next step, shifting to Layout view, will trigger the creation of a new Report Parameter as well as a new supporting Dataset, as we shall see in the following steps.

12.  Click the Layout tab to shift to Layout view.

13.  Select Report --> Report Parameters from the main menu, once again.

The Report Parameters dialog opens. This time, we see that a second Report Parameter has been created.

14.  Click the new Report Parameter within the Parameters list (on the left side of the dialog).

The new SalesTerritorySalesTerritoryGroup parameter, appears as presented in the partial dialog depicted in Illustration 17.


Illustration 17: The New Parameter in the Report Parameters Dialog (Partial View)

15.  Click OK to dismiss the Report Parameters dialog.

16.  Click the Data tab.

17.  Click the downward arrow on the Dataset selector for the report file, within the Data view, as we did earlier.

We note that three Datasets now appear. A new Dataset, SalesTerritorySalesTerritoryGroup, has joined the two pre-existing Datasets, as shown in Illustration 18.


Illustration 18: The New Dataset Appears in the Selector ...

18.  Select the new Dataset within the Dataset selector.

19.  Click the Execute Query button in the toolbar atop the Data view to populate the Results pane.

20.  Click the Refresh fields button to refresh the Dataset fields.

The new Dataset populates the Results pane, which appears as depicted in Illustration 19.


Illustration 19: The New Dataset Appears ...

Having verified that the support components have been automatically set up for us, let's preview the report to see the new Report Parameter in action.

21.  Click the Preview tab.

The report executes, with the defaults for the Report Parameters (All Sales Territories for the new Sales Territory Group parameter) in place. The report appears as shown in Illustration 20.


Illustration 20: The Report, Executed with Default Parameters

Let's take a look at the effects of selecting a couple of the Territory Groups within the Sales Territory Group parameter (we will leave the Product Category parameter at default – it really doesn't concern us at present).

22.  Select Europe and North America (and deselect All Sales Territories) within the Sales Territory Group parameter, as depicted in Illustration 21.


Illustration 21: Select Europe and North America Sales Territory Groups

23.  Click the View Report button.

The report executes, and then appears as shown in Illustration 22.


Illustration 22: The Report, Executed with New Parameters

We see that the report has contracted in width, displaying only the selected Sales Territory Groups, Europe and North America, as we requested. We have therefore verified the effectiveness of the multivalue Report Parameter within its "typical" use for a matrix data region: the columns of the matrix data region "adjust" to reflect the choices within the Report Parameter. This illustrates one of the primary advantages in using a matrix data region in conjunction with a multivalue Report Parameter.

Our client has requested something a little more innovative, however. In the next section, we will extend our work so far, to enable our report to generate a complete, standalone "report" (with separate row and column axes) to appear for each of the Sales Territory Groups selected within the new Report Parameter.

24.  Select File --> Save All from the main menu, to save our work to this point.

Make Structural Changes to the Report to Support Presentation of Independent Matrices, Based upon Ad Hoc Territories Selection

The information consumers for whom we are developing have stated that they wish to meet business requirements for presenting independent matrices based upon a geographical parameter to be selected by the consumers at runtime. This might be handled several ways, but we will examine one procedure that works readily "out of the box" within Reporting Services.

1.  Return to Layout view by clicking the Layout tab.

2.  Click the Matrix Report Item in the Toolbox.

3.  Click the canvas within the Layout view at a point underneath the existing matrix data region, to indicate the location for "dropping" the new matrix data region, at a point on the canvas approximating that depicted in Illustration 23.

Click for larger image

Illustration 23: Click the Canvas to "Drop" the Matrix ...

The matrix data region appears at the "drop point" on the canvas, as shown in Illustration 24.

Click for larger image

Illustration 24: The New Matrix Appears on the Canvas

4.  Click inside the upper left corner textbox of the new matrix data region, to make the gray column and row bars appear for the matrix, as we did with the other matrix data region earlier.

5.  Right-click the upper left corner of the gray bars.

6.  Select Properties from the context menu that appears, as depicted in Illustration 25.


Illustration 25: Accessing Properties for the New Matrix ...

The Matrix Properties dialog appears.

7.  Using the downward arrow to the right of the Dataset name box on the dialog, select the primary Dataset, ProductData.

This is the same Dataset referenced in the matrix atop the canvas.

The Matrix Properties dialog appears, with our selection, as shown in Illustration 26.


Illustration 26: The Matrix Properties Dialog with Dataset Selection

8.  Click OK to accept our changes and to dismiss the Matrix Properties dialog.

9.  Click within the textbox marked Data (the bottom right textbox) in the new matrix.

The Properties window for the textbox should appear (mine is docked underneath the Solution Explorer, on the right side of the design environment, If the Properties window does not appear, select View ---> Properties Window from the main menu to display it, as depicted in Illustration 27.


Illustration 27: Displaying the Properties Window, Should It Not Appear ...

10.  Scroll down, if necessary, within the Properties window, to reach the Size setting, toward its bottom.

11.  Replace the dimensions that currently occupy the Size setting with the following:

7in, 3in

12.  Press the Enter key to accept the new setting.

The Data textbox expands to the new dimensions within the matrix data region. The Size setting, within the Properties window for the Data textbox of the matrix, appears, with our input, as shown in Illustration 28.


Illustration 28: Our New Size Setting within the Properties Window for the Data Textbox

While we may have to make minor adjustments, my intent here is to make the Data textbox large enough to contain the original matrix. If, in our local environments, it seems that we need to make the textbox larger, then we can certainly do so at this point.

13.  In the primary matrix (in the upper left corner of the canvas), click the Adventure Works Cycles label textbox, within the upper left corner textbox of the matrix data region, to select it.

The Adventure Works Cycles label textbox becomes highlighted, indicating that it is selected.

14.  Holding down the Shift key on the keyboard, click each of the image file (the "cyclist" logo) and the Sales Reason label textbox.

All three objects should now be selected.

15.  Using the directional arrow key set on the keyboard, once again, move the three selected objects out of the matrix data region entirely, and to its right on the canvas.

The relocated objects appear (still highlighted) similar to those depicted in Illustration 29.


Illustration 29: Label Objects Moved Out of the Matrix ...

16.  Click the now empty upper left textbox of the top matrix data region, once again.

17.  Click the upper left corner of the gray bars, to select the matrix data region itself.

18.  Hold the mouse pointer over a portion of the now-highlighted outline of the matrix, until it becomes a four-pointed arrow symbol.

19.  Click once on the outline to "grab" the matrix with all components, and then, with the left mouse button of the PC (some may differ) depressed, drag the matrix into the newly enlarged Data textbox of the matrix data region beneath it, as shown in Illustration 30.


Illustration 30: Drag the Primary Matrix into the Data Textbox of the New Matrix Data Region

The primary matrix data region appears within the Data textbox of the new matrix data region as shown in Illustration 31.


Illustration 31: The Primary (Now "Nested") Matrix in the Data Textbox of the New "Parent" Matrix ...

Having now made the primary (now the "nested") matrix data region a data item within the context of the second matrix data region, we can leverage the power of the matrix mechanism to control matrix behavior, just as we can use it to manage data, via row and column groups, and so forth. We shall see a demonstration of this in the following steps.

20.  Right-click the upper left corner of the parent matrix data region, in the manner we did before with the now-nested matrix.

NOTE: It is important, once we venture upon nesting data regions within each other, as we have in this example, to always be aware of "where we are" within the object set we transit. We can always keep an eye on the Properties window, where we can see that we have selected the intended matrix. (Using descriptive naming conventions at creation, versus the default names, for our report items certainly enhances our ability to use the names we see in the Properties window, and elsewhere, with a greater degree of utility).

21.  Select Properties from the context menu that appears, as depicted in Illustration 32.

Click for larger image

Illustration 32: Selecting Properties ... for the New Matrix ...

22.  Click the Groups tab on the Matrix Properties dialog, when it appears.

23.  Click the single item appearing in the Columns list box, as shown in Illustration 33.


Illustration 33: Select the Column Entry ...

24.  Click the Edit button to the immediate right of the Columns list box.

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

25.  Select the following using the drop down selector, to the right of the top line in the Expression list, under the Group on label:

=Fields!Sales_Territory_Group.Value

The Grouping and Sorting Properties dialog - General tab appears as depicted in Illustration 34.


Illustration 34: Grouping and Sorting Properties Dialog - General tab

26.  Click OK to accept changes and return to the Groups tab.

27.  Click OK once more, to dismiss the Matrix Properties dialog.

28.  Click in the textbox watermarked "Rows," within the parent matrix data region to display the gray column and row headers for the matrix once more,

29.  Pass the cursor over the bottom left edge of the row headers.

30.  When the cursor becomes a double-headed arrow, click to "grab" the bottom of the row.

31.  Narrow the bottom row of the parent matrix by dragging upward, as shown in Illustration 35.


Illustration 35: Narrow the Bottom Row of the Parent Matrix ...

Note that narrowing the bottom row in the parent matrix in this manner compresses all rows of the nested matrix. Had we tried to narrow the individual rows within the nested matrix, we would have met with difficulty sizing one without sizing others. This is one of several awkward aspects of nesting a matrix within another matrix: we would do well to weigh such eccentricities with the gains in presentation capabilities, before deciding upon this avenue in our reporting projects. (Other issues, such as impaired export to Excel and other file formats, should also be taken into consideration, of course).

32.  Highlight the column group label expression =Fields!Sales_Territory_Group.Value) in the column heading textbox for the nested matrix.

33.  Right-click the highlighted expression.

34.  Select Cut from the context menu that appears, as depicted in Illustration 36.


Illustration 36: Moving the Column Group Label Expression to the Parent Matrix ...

35.  Click again in the parent matrix textbox marked "Rows," to give the parent matrix (along with its column and row headers) the focus, once more.

36.  Click within the group column heading textbox for the parent matrix, to enter the textbox.

37.  Right-click from within the textbox.

38.  Select Paste from the context menu that appears, as shown in Illustration 37.


Illustration 37: Transplanting the Column Group Label Expression to the Parent Matrix ...

39.  With the cursor still in the textbox housing the newly copied Column Group label expression, click the Bold formatting button in the toolbar atop the Report Designer.

40.  Click the Center formatting button, lying several buttons to the right of the Bold button, next, as depicted in Illustration 38.


Illustration 38: With Textbox Selected, Click Bold and then Center ...

41.  With the textbox housing the newly copied Column Group label expression still selected, pass the cursor over the top right edge of the column headers.

42.  When the cursor becomes a double-headed arrow, click to "grab" the right edge of the rightmost column header.

43.  Narrow the right column of the parent matrix by dragging left (enough to tighten the columns around their respective labels – I ultimately took mine to about the 4.75 inches point on the ruler atop the canvas), as shown from a starting point in Illustration 39.


Illustration 39: Narrow the Right Column of the Parent Matrix ...

44.  In a similar manner, narrow the column to the immediate left of the one just adjusted – the column containing the Rows textbox – to about the .5 inches point on the ruler.

45.  Click the upper left corner of the gray column and row headers of the parent matrix to select the matrix, once again.

46.  When the matrix becomes highlighted, as before, use the directional arrow keys to move the parent matrix data region to the upper left hand corner of the canvas, as depicted in Illustration 40.


Illustration 40: The Parent Matrix Data Region Flush with the Upper Left Corner of the Canvas

47.  Delete the image / label textboxes we earlier moved out of the matrix.

While we will not go into cosmetics with the report to any great extent in this session, we can certainly add labeling and other items, as need dictates in our own environment. The focus in this article is simply to demonstrate the functionality. We will add a little color, however, to set off the end results a bit, and to enhance visual recognition of what we have accomplished.

48.  Within the nested matrix, click the upper left corner textbox.

49.  Click the gray column heading above the upper left corner textbox, to select the column within which it resides (ensuring that the column is within the nested matrix, not the parent matrix), as shown in Illustration 41.

Click for larger image

Illustration 41: Select the Left Column in the Nested Matrix ...

50.  In the Properties window for the matrix column, which appears under Solution Explorer, assign a BackgroundColor of Olive, using the color palate provided via dropdown selector.

51.  In like manner, assign a font Color (whose setting is just above Font in the Properties window) of Yellow, as depicted in Illustration 42.

Click for larger image

Illustration 42: Assigning Matrix Column Properties

52.  Click the two gray row headings beside the upper left corner textbox of the nested matrix, to select the two top rows within which the corner textbox resides (ensuring that the rows are within the nested matrix, not the parent matrix), as shown in Illustration 43.

Click for larger image

Illustration 43: Select the Top Two Rows of the Nested Matrix ...

53.  In the Properties window for the matrix row, which appears under Solution Explorer, assign a BackgroundColor of Olive, as we did for the matrix column earlier.

54.  Assign a font Color of Yellow, again as we did for the matrix column earlier.

55.  Holding down the Shift key, select each of the three data fields within the nested matrix.

56.  In the Properties window, select a BackgroundColor of Pale GoldenRod.

57.  Select a Border Color of Olive.

58.  Select a Border Style of Solid.

59.  Select a font Color of Dark Olive Green.

The Properties window appears, with our setting modifications, as depicted in Illustration 44.


Illustration 44: Properties Window Settings for the Data Fields

60.  Click a point outside the nested matrix data region.

We note that the textbox within the parent matrix, which we have chosen to house our column label expression, still has a transparent background color. Let's handle that, as well as getting started with some external bordering.

61.  Select the textbox containing the column label expression within the parent matrix, by clicking within it.

62.  In the Properties window for the textbox, assign a BackgroundColor of Olive, using the color palate provided via dropdown selector.

63.  Assign a BorderColor of DarkOliveGreen

64.  Expand the BorderStyle setting, by clicking the "+" sign to its immediate left.

65.  Select Solid as the setting within the following border definitions:

  • Left
  • Right
  • Top

66.  Type the following into the BorderWidth setting:

5pt

67.  Assign a font Color of Yellow.

The textbox and associated Properties window appear, with our input, as shown in Illustration 45.


Illustration 45: Assigning Textbox Properties

68.  Click the left upper textbox in the nested matrix below the textbox containing the column label expression, to select the nested matrix, once again.

69.  Assign a BorderColor of DarkOliveGreen.

70.  Expand the BorderStyle setting, by clicking the "+" sign to its immediate left.

71.  Select Solid as the setting within the following border definitions:

  • Left
  • Right
  • Bottom

72.  Type the following into the BorderWidth setting:

5pt

The textbox and associated Properties window appear, with our input, as depicted in Illustration 46.


Illustration 46: Assigning Textbox Properties

73.  Rolling the mouse over the canvas edges until the double-headed arrow cursor ("ß->") appears, "grab" each of the right and bottom edges and adjust the canvas to more closely fit the combined data region, as shown in Illustration 47.


Illustration 47: "Tightening Up the Canvas"

Let's make one more adjustment to the nested matrix to finish up our procedure. We can remove the column group in the nested matrix, because the same group in the parent matrix will perform the same function – to group on Sales Territory Groups. While leaving the original groups in place makes sense for a standard matrix, and, indeed, appears to allow us to achieve the same results, any redundant grouping within the report will mean at least some deterioration in performance, among other potentially undesirable report traits. (In other situations, it might make sense to leave the original group alone – we simply do not need it here).

74.  Select the nested matrix once again, ensuring that the nested matrix has the focus.

75.  Right-click the upper left corner of the matrix.

76.  Select Properties from the context menu that appears, once again.

77.  Click the Groups tab on the Matrix Properties dialog, when it appears.

78.  Click the Group item appearing in the Columns list box, just above the [Static Group] column.

79.  Click the Delete button to remove the original Group item, as shown in Illustration 48.


Illustration 48: Deleting the Column Group Entry ...

80.  Click OK to accept changes, and to dismiss the Matrix Properties dialog.

We have completed the basic structural changes needed to enable our report to meet the expressed need for presenting independent matrices, based upon ad hoc Territory Group selection. We will conclude our article with the next section, where we will verify that our handiwork meets the business requirements of the information consumers.

Verification: Preview the Report to Ascertain Effectiveness

Having added parameterization (with multivalue input capabilities) for Territorial Groups, and making alterations to our report file to meet business requirements for presenting independent matrices based upon the same geographical parameter selection at runtime, we will next perform a quick preview of the report to ascertain the effectiveness of our solution.

1.  Click the Preview tab to execute the RS027_Sales_Reason_Comp_Matrix report.

The report executes, with default parameter selections, and appears as partially shown in Illustration 49.

Click for larger image

Illustration 49: Executed Report, with Default Parameter Selections

We see the report appear, executed by default with "All" parameter values selected for the parameter upon which we are focusing, Sales Territory Group. We can verify this, not only by the three independent matrix data regions that appear in the Preview pane, but also by taking a look at what selections have been made within the parameter picklist.

2.  Click the dropdown selector button to the right of the Sales Territory Group parameter.

The picklist is exposed, as depicted in Illustration 50.

Click for larger image

Illustration 50: The New Parameter Picklist

3.  Uncheck the checkbox for All Sales Territories in the picklist.

4.  Click the checkboxes to the immediate left of the Europe and Pacific Territory Groups to select them, as shown in Illustration 51.

Click for larger image

Illustration 51: Specify Two Territory Groups ...

5.  Click the View Report button to refresh the report.

The report executes and returns one matrix view each for the Europe and Pacific Territory Groups, as depicted in Illustration 52.


Illustration 52: Independent Matrices for the Europe and Pacific Groups

And so, we see that our Report Parameter does, indeed, function correctly with multiple input values. Moreover, the need for the dynamic presentation of a standalone matrix for each of the picklist items we chose for Sales Territory Groups is met when the report executes.

6.  Select File --> Save All from the main menu to save all work to the present.

7.  Select File --> Exit to exit MSSQL Server Business Intelligence Development Studio when ready.

We have corroborated the correct operation of the newly incorporated features. We present the results to the client information consumers, who express satisfaction that the changes do, indeed, meet specifications.

Conclusion ...

In this two-part article, we undertook the 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 an existing, but somewhat limited, matrix data region with a more robust matrix data region that returned identical data. In Part One, we began by creating the new matrix data region within the same .rdl file as the pre-existing data region upon which the new data region was to be based. This side-by-side contrast afforded us several advantages in a rapid development scenario, as we "converted" the original data region to a new data region, which would act as the foundation for innovative enhancements. Having both regions in physical parallel, made verification of comparability between the two easy, as we finished the first half of our examination by ensuring that both regions gave us identical results.

In this article, we further evolved the new matrix to meet the less intuitive facets of the business requirements of the information consumers with which we are working. First, we added the requested parameterization (with multivalue input capabilities) that they had requested for territorial groups, using a multivalue parameter. We then made further structural changes to the report, to meet the innovative business requirement for presenting independent matrices, based upon runtime selections within the new Sales Territory Group parameter. Throughout the two parts of our practice session, we discussed the results we sought to obtain within the development techniques that we exploited. Finally, we concluded 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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved