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

Tuesday Feb 21st 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 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 will be using one of the AdventureWorks sample reports in the practice section, to save time and focus for the subject matter of the article. The AdventureWorks sample reports are a set of prefabricated report definition files that use the AdventureWorks databases (both relational and Analysis Services) as data sources. The sample reports are highly useful to many new report authors and other practitioners, for whom they serve as a tool to assist in learning the capabilities of Reporting Services, as well as templates for designing new reports. For this reason, we typically make a copy of any report(s) we modify within our lessons.

The samples are not automatically installed. Before we can install the Reporting Services samples, we must have already copied the sample installation program to the PC with which we are working, in accordance with the instructions found in the SQL Server 2005 Books Online and elsewhere. We then run the sample installation program to extract and copy the reports (and other) samples to the computer. The sample installation program also installs the AdventureWorks databases (both Database Engine and Analysis Services varieties)..

The samples come packaged within a Report Server project file, which we will open and use in many lessons, rather than creating a new project file. Please make sure that the samples and the project file are installed before beginning the practice section of this article, so as to provide an environment in which to complete the exercises effectively.

Note: Current Service Pack updates are assumed for the operating system, along with the applications and components listed above and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.

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 arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, rest assured that the OLAP 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, 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. As I convert existing enterprise business intelligence to the Microsoft BI solution, I come across opportunities to meet diverse needs on a virtually daily basis. 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.

Foremost in the challenges that I find in the field are those that arise based upon the need to replicate features that exist in previously created reports, often in tandem with a requirement to enhance the reports to provide new capabilities and presentation options. These replication efforts often come as a part of converting the reports of a predecessor enterprise reporting system (for example, converting Cognos PowerPlay or Cognos Impromptu reports to Reporting Services). They also come about through a need to duplicate early efforts within Reporting Services itself into enhanced reports that offer more flexibility, additional features, and so forth, to information consumers, as we do within the Practice session of this article.

As most who have worked with Reporting Services know, authoring reports in the application consists largely of associating controls within the report body with fields that are created within one or more DataSets within the report file (.rdl). Sometimes we can associate controls with other controls to achieve results that might otherwise be difficult. One of my favorite controls in Reporting Services, for OLAP reporting in general, and for its synergistic qualities when working with other objects within Reporting Services, is the matrix data region.

One certainly finds that the table data region seems the clear favorite, among the few Reporting Services books on the market as of this writing. This is perhaps because the table data region closely resembles the functionality found in the those (largely) relational reporting applications that were dominant before Reporting Services appeared. Indeed, the vast majority of current writing continues to surround relational reporting (see my comments in the About the Mastering OLAP Reporting Articles ... section above), mostly because the majority of the current Reporting Services writers have previously written books on the relational enterprise reporting tools to which I refer, and have adapted much of their teaching approach with those applications to Reporting Services. Even among the sample reports that ship with MSSQL Server 2005 Reporting Services, one finds the vast majority to surround relational reporting of this sort.

Among many attributes that I like within the matrix data region, is the fact that its rows and columns can be dynamic, unlike those of a table data region, which are static. We can define matrices with either or both of static and dynamic rows and columns, and thereby support meeting business requirements in many environments where static columns and rows might limit consumers. When we couple the capability for dynamic columns with the behavior we can obtain when nesting other data regions within the matrix, we begin to see how we can produce a far more robust presentation for our employers and clients.

In this article, we will examine a scenario where the dynamic nature of the matrix helps us to meet the expressed needs of a hypothetical group of information consumers, both directly and (to some extent, a bit less intuitively) from a more indirect perspective. In this two-part session, we will:

  • Prepare for the steps of our practice session by accessing the Sample Report Server Project that is available with an MSSQL Server 2005 installation (Part 1);
  • Ascertain connectivity of the Shared Data Source, and open an existing sample OLAP report in Reporting Services that is based upon a table data region (Part 1);
  • "Convert" an existing sample OLAP report (based upon a sample SQL Server Analysis Services cube), using a matrix data region, with which to perform our practice exercise (Part 1);
  • Add parameterization (with multivalue input capabilities) for territorial regions to the new report, using a multivalue parameter (Part 2);
  • 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 (Part 2);
  • Incrementally preview the report to ascertain the effectiveness of our solution (Parts 1 and 2);
  • Discuss, at appropriate junctures, the results obtained within the development techniques that we exploit throughout our practice session (Parts 1 and 2).

Meet Business Needs with Matrix Dynamics

Objective and Business Scenario

The matrix data region, in my opinion, is one of the most valuable tools in the Reporting Services toolbox – certainly, when one is employing the application to generate rich 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 certainly in ways that are impossible within other popular enterprise reporting applications.

In the following sections, we will illustrate uses for the matrix data region to achieve objectives that are beyond the limitations of the table data region. To provide a report upon which we can practice the steps of our hands-on exercise, we will begin with the Sales Reason Comparisons sample report, based upon 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 Sales Reason Comparisons report is intended to present comparison summary data from the Adventure Works cube. For the purposes of our article, we will say that we are 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, let's say that the consumers have expressed the need for modifications to the existing Sales Reason Comparisons report. They tell us that the report as it appears today, constructed by an individual who is no longer with the organization, would serve as an excellent basis for newly extended requirements, in that the columns and rows of the report are consistent with the objectives of the report they envision. The existing report, however, is a static report that depicts information for various territory groups of the organization in an inflexible manner. While the current presentation might have been adequate, they tell us, before the advent of the new portals that have gradually taken hold within the organization as a vehicle for distributing information, the need now is for this information to be presented in a manner that allows 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 having the territories parameterized, the consumers tell us that they want a complete "report" (axes and all) to appear for each of the territories selected. They tell us that this 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 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.

We see immediately, upon examination of the Sales Reason Comparisons report, that it consists of a matrix data region, used in rather "vanilla" way. We surmise that a matrix data region will, indeed, handle variable / multiple territory specifications at runtime, with minimal alterations to the report file as a whole. The flexibility of the matrix would especially present itself when, as the consumer specified, say, two territories, the number of columns that the matrix generated would increase to meet the requirement. Moreover, the need to present the data as standalone matrices, too, can be handled via the capabilities of the matrix data region, albeit in a manner that might not be readily intuitive. Having done similar things for other clients, we agree to leverage a procedure we have followed before to prove the concept with the Adventure Works team's data.

The Sales Reason Comparisons report, as it was originally created, appears as depicted in Illustration 1.


Illustration 1: Original Sales Reason Comparisons Report

We work with the team to construct a rough draft that shows what the same report would look like within the scenario that they have requested we help them to accommodate. The draft, presenting the Sales Reason data for three territories, appears as depicted in the spreadsheet shown in Illustration 2.


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

As part of our typical business requirements gathering process, we listen attentively to the details, formulating, in the background, an idea of the steps we need to take in modifying a copy of the report to produce the desired results. Once we grasp the stated need, and confirm our understanding with the intended audience, we begin the process of modifying the Sales Reason Comparisons report to satisfy the information consumers, within the Practice section that follows.

Practice

Our first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement the requested enhancements we have discussed with the client information consumer group. We will perform this, and the other steps of our practice session, from inside the BI Development Studio.

NOTE: For more exposure to the MSSQL Server Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see articles in this and my other Database Journal series, Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to get to the focus of our session more efficiently.

Prepare the Reporting Services Development Environment for Our Practice Example

For purposes of our practice session, we will create a copy of the Sales Reason Comparisons report, one of several samples that are available with (and installable separately from) Microsoft SQL Server 2005. Creating a "clone" of the report means we can make changes to our report while retaining the original sample in a pristine state – perhaps for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, in learning more about Reporting Services in general.

Within our practice procedures, we will begin with the Sales Reason Comparisons report as it exists today. Based upon the existing matrix data region, the report does the job for which it was designed, at least within the limited scope of the original vision. In this, the first half of this two-part article, we will use the original data region to assist us in creating a quick matrix data region that presents identical data elements and numerical results. In Part 2, we will continue our enhancement efforts with the matrix data region, continuing to use the pre-existing data region to verify the accuracy and completeness of our new matrix data region. (We will ultimately dispense with the original data region.) We will discover that, with a few enhancements, the new matrix data region will be quite adequate to present what the original data region did from the outset. Moreover, the new matrix will be superior in several other capabilities, some of which particularly suit the recently expressed business needs.

Making preparatory modifications, and then making the enhancements to the report to add the functionality to support the subject of our lesson, can be done easily within the Business Intelligence Development Studio environment. Working with a copy of the report will allow us the luxury of freely exploring our options, and leave us a working example of the specific approach we took, to which we can refer in our individual business environments.

Open the Sample Report Server Project and Ascertain Connectivity of the Shared Data Source

To begin, we will launch the SQL Server Business Intelligence Development Studio.

1.  Click Start.

2.  Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.

The equivalent on my PC appears as depicted in Illustration 3.


Illustration 3: Launching SQL Server Business Intelligence Development Studio

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

3.  Close the Start page, if desired.

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

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


Illustration 4: Selecting a Project ...

The Open Project dialog appears.

6.  Browse to the AdventureWorks sample reports.

The reports are installed, by default (and, therefore, subject to be installed in a different location on our individual machines), in the following location

C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports

7.  Select the AdventureWorks Sample Reports.sln file within the sample reports folder, as depicted (circled) in Illustration 5.


Illustration 5: The Open Project Dialog, with Our Selection Circled ...

8.  Click Open.

The AdventureWorks Sample Reports solution opens, and we see the various objects within appear in Solution Explorer, as shown in Illustration 6.


Illustration 6: The Solution Opens within BI Development Studio ...

Let's first ensure we have a working shared data source. Many of us will be running "side-by-side" installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone.

9.  Double-click AdventureWorksAS.rds, within the Shared Data Sources folder seen in Solution Explorer.

The Shared Data Source dialog opens, and appears with default settings as depicted in Illustration 7.


Illustration 7: The Shared Data Source Dialog with Default Settings ...

10.  Click the Edit button on the Shared Data Source dialog.

The Connection Properties dialog opens, and appears with default settings shown in Illustration 8.


Illustration 8: The Connection Properties Dialog with Default Settings ...

We note that the default Server name is "local." While this might prove an adequate setting for a PC with only MSSQL Server 2005 installed (default instance), in the case of many of our installations, the requirement here is for the server / instance combination that correctly identifies the correct MSSQL Server 2005 instance. (Clicking the Test Connection button at this point will provide confirmation whether we need to make this change).

11.  If appropriate, type the correct server / instance name into the Server name box of the Connection Properties dialog. (Mine is MOTHER1\M1MSSQL2K5, as depicted in Illustration 9.)


Illustration 9: The Connection Properties Dialog with Corrected Settings ...

12.  Ensure that authentication settings are correct for the local environment.

13.  Click the Test Connection button.

A message box appears, indicating that the Test connection succeeded, assuming that our changes (or lack of same, as appropriate) are appropriate. The message box appears as shown in Illustration 10.


Illustration 10: Testing Positive for Connectivity ...

14.  Click OK to dismiss the message box.

15.  Click OK to accept changes, as appropriate, and to dismiss the Connection Properties dialog.

The Shared Data Source dialog appears, with our modified settings, similar to that depicted in Illustration 11.


Illustration 11: The Shared Data Source Dialog with Modified Settings ...

16.  Click OK to close the Shared Data Source dialog, and to return to the development environment.

We are now ready to open the Sales Reason Comparisons sample report, and to use it as a model as we proceed with the practice exercise.

Open and Review the Sales Reason Comparisons Report

As we have noted, we will begin by opening the Reporting Services 2005 Sales Reason Comparisons report, whose existing matrix data region we will use as a guide to the quick creation of a new matrix data region that presents identical information. We will then be in a position to leverage further features of the matrix data region that support the specific enhancements requested by the information consumers, while retaining a copy of the original data region, with which we can perform quick number comparisons to gain comfort in the accuracy and completeness of our replacement matrix .

As we noted in the Objective and Business Scenario section above, the information consumers with which we are working have outlined a few enhancements that they wish to make to the report clone, to outfit it to meet a specific business need that is different than the need addressed by the original report. Again, we will use the original data region as a guide in creating a new matrix region that will serve to meet the expressed needs. We will accomplish this within a replica of the original .rdl file, so as to leverage the existing DataSets as well as the existing data region, primarily to allow us to save time and steps in getting to the focus of this article.

We are now ready to "clone" the sample OLAP report and proceed with the practice exercise.

Create a Copy of the Sales Reason Comparisons Report

As we have noted, we will begin with a copy of the Reporting Services 2005 Sales Reason Comparisons report file, which we will use for our practice exercise in meeting the business requirements of the Adventure Works information consumers.

1.  Right-click the Reports folder underneath the Shared Data Sources folder, in the Solution Explorer.

2.  Select Add --> Existing Item ... from the cascading context menus that appear, as shown in Illustration 12.

Click for larger image

Illustration 12: Adding the Report to the Project ...

The Add Existing Item – AdventureWorks Sample Reports dialog appears.

3.  Navigate to the actual location of the sample reports (we provided the default path earlier), if the dialog has not defaulted thereto already.

An example of the Add Existing Item – AdventureWorks Sample Reports dialog, having been pointed to the sample Reports folder (which contains the Sales Reason Comparisons report file we seek), appears as partially shown in Illustration 13.


Illustration 13: Navigating to the Sample Reports Folder ...

4.  Right-click the Sales Reason Comparisons report inside the dialog.

5.  Select Copy from the context menu that appears, as depicted in Illustration 14.


Illustration 14: Performing a Quick Copy of the Sales Reason Comparisons Report

6.  Right-click somewhere in the white space inside the Add Existing Item – AdventureWorks Sample Reports dialog.

7.  Select Paste from the context menu that appears, as shown in Illustration 15.


Illustration 15: Select Paste within the New Folder ...

A copy of the Sales Reason Comparisons report appears within the dialog.

8.  Right-click the new file.

9.  Select Rename from the context menu that appears.

10.  Type the following name in place of the highlighted existing name:

RS026_Sales_Reason_Comp_Matrix.rdl

NOTE: Be sure to include the .rdl extension in the file name.

The renamed copy of the Sales Reason Comparisons sample report appears as depicted in Illustration 16.


Illustration 16: The New Report File, RS026_Sales_Reason_Comp_Matrix.rdl

11.  Click the white space to the right of the file name, to accept the new name we have assigned.

12.  Re-select the new file by clicking it.

13.  Click Add on the dialog box to add the new report to report project AdventureWorks Sample Reports.

RS026_Sales_Reason_Comp_Matrix.rdl appears in the Reports folder, within the AdventureWorks Sample Reports project tree in the Solution Explorer, as shown in Illustration 17.


Illustration 17: The New Report Appears in Solution Explorer – Report Folder

14.  From the main menu in the design environment, select File ---> Save All, as depicted in Illustration 18.


Illustration 18: Select File --> Save All to Save Our Work So Far ...

We now have a clone report file within our Reporting Services 2005 Project, with which we can proceed in the next section to make alterations per the specification we have received. Our efforts will consist, in Part 1, of the addition of a matrix data region to ultimately replace the existing data region that largely comprises the sample report.

Preparation: Enhance the Report per the Business Requirements

As we noted in the Objective and Business Scenario section above, the client information consumers have outlined a few enhancements that they wished to make to the existing report, to outfit it to meet a specific business need that is different than the need addressed by the original report. Let's review the presentation that the current report file supports, and then add a matrix data region to the file that will replicate the existing data region.

Review the Layout and Operation of the Sample OLAP Report

1.  Right-click RS026_Sales_Reason_Comp_Matrix.rdl in the Solution Explorer.

2.  Select Open from the context menu that appears, as shown in Illustration 19.


Illustration 19: Opening the New Report ...

RS026_Sales_Reason_Comp_Matrix.rdl opens in Layout view, and appears as depicted in Illustration 20.

Click for larger image

Illustration 20: The Existing Report Opens in Layout View ...

Let's preview the report, so as to get a feel for its general operation prior to performing our enhancements.

3.  Click the Preview tab to execute Sales Reason Comparisons.

Execution begins (the report initially executes with the default parameter setting).

The report executes, and appears as shown in Illustration 21.


Illustration 21: The Report Appears with Default Parameter Selection

Once we have seen the intended presentation of the report, we can return to the Layout view, where we will replicate the current data region with a new matrix data region, with which we can deliver the information presented in the original view, while adding the enhancements that the information consumers have requested.

4.  Click the Layout tab, to return to the design environment.

We will create the new version of the existing matrix data region in the section that follows.

Replicate the Existing Sales Reason Comparisons Report Data within a New Matrix Data Region

We are now positioned to replicate the existing matrix data region that comprises the report with a new matrix data region, which will present identical information while supporting the new navigation and presentation business requirements of our users.

1.  1. Placing the mouse cursor over the bottom and right edges of the canvas, one after another, until the cursor becomes an "I" beam, drag edges of the canvas (not the data region) to approximately the measurement marks of 5 and 10 on the horizontal and vertical axes, respectively, as depicted in Illustration 22.

Click for larger image

Illustration 22: Extending the Report Canvas on the Layout Tab

2.  Drag a matrix data region object from within the Toolbox to the canvas area of the Layout tab.

3.  3. Drop the matrix data region onto the canvas below the existing table data region, as depicted in Illustration 23.

Click for larger image

Illustration 23: Adding a Matrix Data Region to the Report

The matrix data region appears on the canvas approximately as shown in Illustration 24.

Click for larger image

Illustration 24: The Matrix Data Region Appears ...

Our first task in replicating the information presented by the existing data region is to make the appropriate data source / data field associations within the new matrix. (Because we are using the same .rdl file as the former report, we can assume that the DataSets in place are adequate for our needs).

4.  4. Align the matrix data region just below the existing data region, so that it approximates the position of the original data region for easy reference as we proceed, as shown in Illustration 25.


Illustration 25: Align the Matrix for Easy Field Replication ...

5.  Click the top row of the matrix to select it.

6.  Increase the height of the top row in the Properties window to 1.5, as depicted in Illustration 26.


Illustration 26: Increase Height to 1.5 Inches ...

7.  7. Drag and drop a textbox, from the Toolbox, outside, and to the right of, the new matrix data region.

8.  Type the following title into the newly added textbox:

Adventure Works Cycles

9.  Highlight the text we have just typed, and then make the following settings from the tool bar atop the design environment:

Font Name: Arial

Font size: 12 pt.

Bold: Button Depressed

Italic: Button Depressed

10.  Click the Foreground Color button (to the immediate right of the Italic button).

11.  Select Dark Slate Blue from the Choose Color dialog that appears, as shown in Illustration 27.


Illustration 27: Select Dark Slate Blue in the Choose Color Dialog ...

12.  Click OK to accept the color setting, and to close the Choose Color dialog.

13.  Click the Center button in the alignment buttons group, to the right of the settings we have already made, as depicted in Illustration 28.


Illustration 28: Click the Center Button to Center the Title in the Textbox ...

14.  Reposition the sides of the textbox, as appropriate, to compactly fit the new title it encloses.

15.  15. Click the image (the bicyclist) in the top of the upper left corner box in the existing data region, to select it.

16.  Press CTRL + C to copy the image.

17.  17. Press CTRL + V to paste a copy of the image outside the new matrix data region, and to its right, once again, above the newly created title textbox.

18.  18. Click the title textbox (containing the words "Sales Reason") in the bottom of the upper left corner box (underneath the image file, and centered upon leftmost column) in the existing data region, to select it.

19.  Press CTRL + C to copy the textbox (with the text it contains).

20.  20. Click within outside the new matrix data region, at a point on the canvas, this time below the new title textbox.

21.  Press CTRL + V to paste the column title textbox onto the canvas.

The objects we have replicated appear to the right of the new matrix data region as shown in Illustration 29.

Click for larger image

Illustration 29: The Newly Copied Objects Outside the Matrix ...

22.  Click the new image copy to select it.

Borders appear around the image.

23.  23. Using the arrow keys on the PC, move the image file to the upper left corner of the unlabelled, upper left (new) matrix box.

NOTE: Dragging the image with the mouse will not achieve the desired result.

24.  Perform the same operation, using the arrow keys, with the two title textboxes we have placed outside the matrix, aligning them under the image, as depicted in Illustration 30.

Click for larger image

Illustration 30: Tentative Alignment of the Replicated Objects in the Matrix ...

25.  25. Fit the box into which we have placed them to compactly "fit" the newly added objects, so that the upper left corner box of the new matrix appears as shown in Illustration 31.


Illustration 31: Fitting the Upper Left Corner Box of the Matrix to the New Objects ...

Now, let's move the data fields that we see associated with the original data region into the new matrix data region.

26.  26. From the ProductData DataSet, within the DataSets pane click and drag the Sales Reason field into the Rows area of the new matrix data region, as depicted in Illustration 32.


Illustration 32: Drag the Data Field to the Rows Section of the New Matrix Data Region

27.  In like manner, click and drag the Internet Order Quantity field (also from the ProductData DataSet) into the Data area (to the immediate right of the Rows area) of the matrix data region.

28.  Drag the Internet Sales Amount to the right edge of the cell in the data area where we dropped the Internet Order Quantity field.

29.  When the small, white column / beam appears hovering over the right edge of the cell, as shown in Illustration 33, drop the Internet Sales Amount data field.


Illustration 33: Drop Point Indicator Appears ....

The matrix data region expands, creating a column to house the new data field, and placing the name of the fields added to this point into the column headings automatically.

30.  Again, in like manner, drag and drop the Internet Total Product Cost field to the right of the Internet Sales Amount column that appeared in our last step.

The matrix data region expands yet another column, creating a column to house the new Internet Total Product Cost data field, and adding a column label. Our matrix data region resembles that depicted in Illustration 34, at this stage.


Illustration 34: The Matrix Data Region with Added Data Fields

31.  Within the column heading for the first column, replace the current text ("Internet Order Quantity") with the following:

Internet Orders

32.  Holding down the CTRL key, click each of the three column headings textboxes to select them.

33.  Within the Properties pane, select Center in the TextAlign property, to apply centered Text Alignment to each of the column headings.

34.  Select Bottom for the Vertical Align setting, below the Text Alignment setting we adjusted above.

35.  With the headings still selected, expand the Font property and modify the existing settings for the following to the values indicated:

FontSize: 9 pt.

FontWeight: Bold

The settings we have made for the column headings appear as shown in Illustration 35.


Illustration 35: Settings for the Column Headings Group ...

36.  Select the Row section, which contains "=Fields!Sales_Reason.Value," the data field we inserted earlier.

37.  Set the FontSize setting to 9pt in the Properties window, as we did earlier.

38.  Holding down the CTRL key, click each of the three data fields to the right, and within the same row of the matrix, as "=Fields!Sales_Reason.Value," to select them.

39.  Set the FontSize setting to 9 in the Properties pane, as we did earlier.

40.  Set the Format to "#,###" to enable the data fields to display rounded numbers.

The settings we have made for the value data fields appear as depicted in Illustration 36.


Illustration 36: Settings for the Data Fields ...

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.

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