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 ...
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.
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 2005 Reporting Services
Server 2005 Database Services
AdventureWorks sample databases
Server 2005 Analysis Services
Internet Explorer 6.0 with scripting enabled
Intelligence Development Studio (optional)
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
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.
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
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.
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
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
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.
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
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);
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.
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
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.
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 /
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: Original Sales Reason Comparisons Report
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.
2: Rough Draft of the Proposed Presentation Layout Multiple Territories
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
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.
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.
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.
the Sample Report Server Project and Ascertain Connectivity of the Shared Data
begin, we will launch the SQL Server Business Intelligence Development Studio.
and click, the SQL
Server Business Intelligence Development Studio, as appropriate.
equivalent on my PC appears as depicted in Illustration 3.
Launching SQL Server Business Intelligence Development Studio
briefly see a splash page that lists the components installed on the PC, and
then Visual Studio .NET 2005 opens at the Start page.
Close the Start
page, if desired.
--> Open from the main menu.
/ Solution ... from the cascading menu, as shown in Illustration 4.
Selecting a Project ...
Project dialog appears.
Browse to the AdventureWorks
The reports are installed, by default (and, therefore,
subject to be installed in a different location on our individual machines), in
the following location
Files\Microsoft SQL Server\90\Samples\Reporting Services\Report
Samples\AdventureWorks Sample Reports
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 ...
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 ...
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
within the Shared Data Sources folder seen in Solution Explorer.
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
Properties dialog opens, and appears with default settings shown in Illustration
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
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
15. Click OK to accept changes,
as appropriate, and to dismiss the Connection Properties dialog.
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
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
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.
a Copy of the Sales Reason Comparisons Report
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.
the Reports folder underneath the Shared Data Sources folder, in
the Solution Explorer.
--> Existing Item ... from the cascading context menus
that appear, as shown in Illustration 12.
Existing Item AdventureWorks Sample Reports dialog appears.
the actual location
of the sample reports (we provided the default path earlier), if the dialog has
not defaulted thereto
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.
Navigating to the Sample Reports Folder ...
the Sales Reason Comparisons report inside the dialog.
from the context menu that appears, as depicted in Illustration 14.
Performing a Quick Copy of the Sales Reason Comparisons Report
somewhere in the white space inside the Add Existing Item AdventureWorks Sample Reports dialog.
from the context menu that appears, as shown in Illustration 15.
Illustration 15: Select
Paste within the New Folder ...
of the Sales Reason Comparisons report appears within the dialog.
the new file.
from the context menu that appears.
following name in place of the highlighted existing name:
NOTE: Be sure to include the .rdl extension in the file name.
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
white space to the right of the file name, to accept the new name we have
new file by clicking it.
on the dialog box to add the new report to report project AdventureWorks
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: The New
Report Appears in Solution Explorer Report Folder
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.
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.
the Layout and Operation of the Sample OLAP Report
in the Solution Explorer.
from the context menu that appears, as shown in Illustration 19.
Illustration 19: Opening
the New Report ...
opens in Layout
view, and appears as depicted in Illustration 20.
preview the report, so as to get a feel for its general operation prior to
performing our enhancements.
Click the Preview
tab to execute Sales Reason Comparisons.
begins (the report initially executes with the default parameter setting).
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.
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. 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.
Drag a matrix
data region object from within the Toolbox to the canvas area of the
3. Drop the matrix data region onto the canvas below the existing table data region, as depicted in Illustration 23.
data region appears on the canvas approximately as shown in Illustration
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. 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 ...
Click the top
row of the matrix to select it.
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. Drag and drop a textbox, from the Toolbox, outside, and to the right of, the new matrix data region.
following title into the newly added textbox:
Adventure Works Cycles
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
Click the Foreground
Color button (to the immediate right of the Italic button).
Slate Blue from the Choose Color dialog that appears, as shown in Illustration
Illustration 27: Select
Dark Slate Blue in the Choose Color Dialog ...
to accept the color setting, and to close the Choose Color dialog.
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 ...
sides of the textbox, as appropriate, to compactly fit the new title it
15. Click the image (the bicyclist) in the top of the upper left corner box in the existing data region, to select it.
+ C to copy the image.
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. 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.
+ C to copy the textbox (with the text it contains).
20. Click within outside the new matrix data region, at a point on the canvas, this time below the new title textbox.
+ 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 the new image
copy to select it.
appear around the image.
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
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.
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. 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
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.
Drag the Internet Sales
Amount to the right edge of the cell in the data area
where we dropped the Internet Order Quantity field.
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
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
Illustration 34: The Matrix Data Region
with Added Data Fields
Within the column heading for
the first column, replace the current text ("Internet Order Quantity") with the following:
the CTRL key, click each of the three column headings textboxes
to select them.
Within the Properties
pane, select Center in the TextAlign property, to apply
centered Text Alignment to each of the column headings.
for the Vertical Align setting, below the Text Alignment setting
we adjusted above.
headings still selected, expand the Font property and modify the existing
settings for the following to the values indicated:
FontSize: 9 pt.
settings we have made for the column headings appear as shown in Illustration
Illustration 35: Settings
for the Column Headings Group ...
Select the Row
section, which contains "=Fields!Sales_Reason.Value," the data field we inserted earlier.
Set the FontSize setting
to 9pt in the Properties window, as we did earlier.
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.
Set the FontSize setting
to 9 in the Properties pane, as we did earlier.
Set the Format
to "#,###" to enable the data fields to display rounded
settings we have made for the value data fields appear as depicted in Illustration
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.
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.
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.
The Grouping and Sorting Properties dialog opens.
46. In the top row of the Expression box, within the Group on section, select "=Fields!Sales_Territory_Group.Value," as depicted in Illustration 40.
Illustration 40: Performing Column Grouping on Sales Territory Group ...
47. Click OK to accept our input and close the Grouping and Sorting Properties dialog.
We return to the Groups tab.
48. With the new column group selected, click the Up button to the right of the Columns group list, to raise the new group above the pre-existing static group.
The Groups tab appears, with the new group, as shown in Illustration 41.
Illustration 41: The New Group in Place ...
49. Click OK to accept our input and to close the Matrix Properties dialog.
We return to the Layout tab, where we see the new group appear within the columns of the matrix.
50. Select the top two, of the three, rows of the matrix, by holding down the SHIFT key and clicking the gray borders to their left.
51. Modify the Height property within the Properties pane to .5in, as depicted in Illustration 42.
Illustration 42: Modify the Height of the Top Two Rows to .5 Inches ...
Our new matrix is now ready to test. While we remain a few steps from meeting the complete business requirement of the information consumers, it is important to test our results in this case against the conveniently placed, pre-existing data region that is assumed to return the same data before proceeding with the more involved steps that we undertake in Part 2.
52. Adjust the objects in the upper left corner box of the matrix, as necessary, to align them fully inside the box.
53. Select File --> Save All to save our work to this point.
54. Click the Preview tab to execute the report.
Our report executes with the default parameter selection, and returns the two data regions closely aligned for easy comparison. We see that the values we have delivered within the new matrix agree with those that appear within the original data region, as shown in Illustration 43.
Illustration 43: The Two Regions Appear, with Comparable Results ...
We now have a matrix data region that accurately and completely replicates the results of the pre-existing data region. In our next article, we will parameterize the territories, and then engage the more unusual challenge to build in the automatic generation of separate data regions for each territory selected at run time.
55. Save RS026_Sales_Reason_Comp_Matrix.rdl in a safe location for access in Part 2.
56. Exit Reporting Services when ready.
In this article, we conducted the first half of an examination of a scenario where the dynamic nature of the Reporting Services matrix data region makes it the "object of choice" for enabling us to meet the expressed needs of a hypothetical group of information consumers. Part of the requirement was to replace a somewhat limited pre-existing matrix data region with a new matrix data region that returned identical data. We accomplished this step, verifying comparability between results displayed in each of the data regions, in concluding our preparation to take on the more non-intuitive steps that we will examine in Part 2.
As a rapid way of preparing the matrix data region to meet the needs of the information consumers, while providing an immediate means of verifying the accuracy of the new data region, we built the new matrix side-by-side with the existing data region. We began by adding a matrix data region to the report file, which we accessed within the Sample Report Server Project that is available with an MSSQL Server 2005 installation. We ascertained connectivity of the Shared Data Source, and opened the existing sample OLAP report that we sought to replicate, overviewing its capabilities and limitations. We then duplicated the data returned by the existing sample OLAP report within our new matrix data region, where we added grouping to present the data in a way that afforded "apples-to-apples" comparability to the data in the original matrix data region.
In our next article, we will continue our efforts with the matrix data region, and will deliver a solution that completely meets the expressed requirements of the client consumer group. We will add parameterization (with multivalue input capabilities) for territorial regions, using a multivalue parameter, and then make further structural changes to the report, to meet the business requirements for presenting independent matrices based upon a geographical parameter to be selected by the consumers at runtime. We will discuss the results obtained within the development techniques that we exploit throughout the steps of our practice exercise, as we have done to this point, and will conclude with a preview of the report to ascertain the effectiveness of our solution.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.