The ability to choose grouping
criteria at run time can mean the selection and delivery of enterprise data in a more focused and consumer-friendly manner. It also means doing more with fewer reports. BI Architect Bill Pearson leads a hands-on demonstration of a way to parameterize grouping with runtime sorting options.
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),
presenting an overview of its features, with tips and techniques for real-world
use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools
needed to complete the hands-on portion of this article see BlackBelt
Administration: Linked Reports in Report Manager, another article within this
series.
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 Analysis Services reporting with this
tool is, to say the least, sparse. As I stated in my article, Mastering
OLAP Reporting: Cascading Prompts (where I treated the subject of cascading
parameters for Reporting Services 2000), 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 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, even taking into consideration the
release of several books surrounding Reporting Services in recent years,
continues to represent a serious undersell of Reporting Services, from
an OLAP reporting perspective. I hope to contribute to making this
arena more accessible for everyone, and to share my implementation and
conversion experiences as the series evolves. In the meantime, we can rest
assured that the OLAP potential in Reporting Services will
contribute significantly to the inevitable commoditization of business
intelligence, via the integrated Microsoft BI solution.
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
In
recent articles within this series, we have focused upon various aspects of parameterization
within the Reporting Services environment. In some cases we have
supported parameterization from structures completely contained within Reporting
Services, and in others we have created parameter (predominantly
picklist) support from within other layers of the integrated Microsoft business
intelligence solution. As many of us are aware, enterprise reporting
applications typically allow for parameterization (via what are
sometimes known as prompts or parameter prompts) to enable information
consumers to quickly find the information they need from a report. These
parameters, whose values are physically passed to an axis specification or a
slicer in the dataset query, often act to put filters into place on the fly;
the filters are thus enacted when the consumer types or selects a value, or a
series of values, at run time.
Because they allow
information consumers to assume a role in guiding the delivery of information
and add a self-serve component to the reporting experience - parameterization
in general is a popular topic in the forums and newsgroups of most enterprise
reporting applications. My first exposure to the concepts of parameterization
was in working with very early versions of Cognos Impromptu. My
continued application of those concepts over the succeeding years within Cognos,
Crystal, Business Objects, MicroStrategy,
and several more specialized applications, has given me a great appreciation
for the opportunities that exist in the business environment for effective
parameterization. Whether the reports are to be printed, displayed on screen,
or any of the other options for production / deployment, its easy to see the
value that parameterization can add in making the selection and delivery of
enterprise data more focused and consumer-friendly.
While
I have extended parameterization concepts into many arenas, none have captured
my attention as much as their deployment within the integrated Analysis
Services / Reporting Services pairing. These applications work
together to provide business intelligence in a way that is powerful and highly
flexible. Indeed, I often advise clients who are attempting to locate a
consultant to implement the integrated Microsoft BI solution (composed of MSSQL
Server, MSSQL Server Analysis Services, and Reporting Services)
to seek a multidimensional architect - a practitioner who has a good working
knowledge of each of the components, and who can determine where, among
three or more possible logical layers, to place which components so as
to optimize the system as a whole.
NOTE: For details surrounding hands-on
approaches (as you will see, they are Legion) to the mechanics behind
supporting parameterization, (including the generation of picklists) in Reporting
Services, see these articles in MSSQL Server Reporting Services series
here at Database Journal:
Throughout various articles of this series, we have
generated simple lists to provide virtually all we need to support
parameterization within Reporting Services and other enterprise
reporting applications. In this article, we will perform an examination of the
straightforward mechanics behind the parameterization of grouping,
and sorting of the members within the group we select. We will get hands-on
exposure to parameterizing grouping within a preexisting sample OLAP
report. Beginning with the general concepts, we will continue into a practice
session where we set up a scenario, within which we work with a basic OLAP
report, to expose the steps involved. In examining the rudiments of grouping
parameterization within an OLAP report containing a matrix data region, we
will:
-
Open the
sample Report Server project, AdventureWorks Sample Reports, and ascertain
connectivity of its shared Analysis Services data source;
-
Create a clone
of an existing sample Analysis Services report, containing a matrix data
region, with which to perform our practice exercise;
-
Make structural
modifications to the clone report, to prepare for our practice exercise session
with parameters;
-
Perform a
brief overview of the concepts behind ad hoc grouping, which we will use
to support the stated reporting needs of a hypothetical client;
-
Modify the
existing dataset within, and add a new dataset to, the sample report clone, to
support group and group member sorting parameterization;
-
Make
modifications to the report layout to support the stated client
reporting needs;
-
Add Report
Parameters to support ad hoc group
and group member sorting selection;
-
Modify matrix
settings to fully enable the newly supported group and group member sorting
parameterization;
-
Modify the
face of the report to dynamically reflect grouping parameter selections
made at run time.
-
Discuss the
results obtained with the development techniques that we exploit throughout our practice session.
Parameterizing Grouping in an Analysis Services Report
Throughout many past
articles of the MSSQL Server Reporting Services series, we have
leveraged parameters within the context of MDX queries. Reporting
Services 2000, initially intended as a component of the MSSQL Server
2005 BI Release, was released to market early, as many of us are aware,
with resulting limitations in some of its capabilities (as most realistic
practitioners would expect). One of the challenges that faced many of us was
the absence of an MDX editor: while those of us who were comfortable
with MDX syntax were not impaired significantly (although we had to deal with
circumstances that accompanied parameterization in Reporting Services 2000,
such as the need to string our queries for passage from Reporting Services
to the Analysis Server, and the resulting inability to test generate
our datasets, once we had parameters in place within the MDX
queries), those who were already challenged with MDX as a language almost
certainly found no amusement in dealing with the added mechanics. Reporting
Services 2005 introduced the MDX Query Builder, a tool that
appeals, due to its on-off flexibility, to most practitioners who are
comfortable writing direct MDX queries, as well as the MDX- challenged among
those authors and developers who need to work with OLAP data sources. This
enhancement, unsurprisingly, changed the way that many of us had become
accustomed to writing the underlying MDX queries for our Analysis Services
reports.
Objective and Business Scenario
In this article, we will
perform a relatively straightforward examination of parameterization within a copy of an
existing sample Reporting Services 2005 report that we will create for
this purpose. Our focus will be to parameterize a row group that
we add to a report that already contains one row group, although the
procedure we undertake will work for column groups, as well. As a part
of meeting a hypothetical client need that we will detail below, we will concentrate
upon the steps we will need to take, and the objects we will need to modify /
add to the functional OLAP report to support parameterization of the
newly added group, as well as parameterizing the sorting of members
within that group. We will also cover, in passing, some of the general details
of setting up parameters and so forth in Reporting Services (we
cover the latter in more detail in various articles I have written for that
specific purpose).
To
provide a report upon which we can practice our exercises, we will begin with
the Sales Reason Comparisons sample report that, among other samples,
accompanies the installation of Reporting Services 2005, and which is
based upon the ubiquitous Adventure Works cube, contained within the
Analysis Services database named Adventure Works DW (which is
available to anyone installing Analysis Services 2005).
The Business Need
For
purposes of our practice procedure, we will assume that information consumers
within the Marketing office of the Adventure Works organization have
expressed the need for modifications to the existing Sales Reason
Comparisons report. The existing capabilities in the
report meet some of their analysis needs, but recent requirements for a more
flexible presentation of the data has resulted in our being called to assist
with creating a custom report that meets an immediate, specialized need in a
user-friendly manner.
In discussing their
requirements in detail, representatives of the Marketing department inform us
that the current report contains a filter that limits the data presented
to a couple of operating years. In addition to the focus requirements of
this lesson, they request that we open the available periods for reporting -
to the full range of operating data that is physically available within the
cube. The consumers request some additional changes to the report itself,
primarily that we remove the existing report parameter, as well as its
underlying support mechanisms, which allows us to filter products by Product
Category.
They also specify that
they would like to see more simplicity in the new report with regard to other
considerations: Instead of three measures in the column axis, we will
only display two, Internet Orders and Internet Sales Amount.
Moreover, as the report will apply to the Adventure Works organization
as a whole, and will not break out measures by Territory Group, we will
eliminate this existing grouping within the matrix data region. Finally, we
will (column) group the measures by Month, instead.
More to the primary
focus of this article, our client colleagues state that ongoing analysis
operations would be dramatically enhanced if they could simply specify, at
report run time, a subgrouping of the information displayed. Once the
report is modified to comply with the general requirements above, they say,
they would like it to generate a subgroup below the Sales Reason group.
Moreover, they tell us, they would like to be able to select, at run time, among
multiple group choices: to meet present needs, they want to be able to choose
between Product, Product Category, and Product Subcategory; Product
would be the most frequent selection, made by the largest number of information
consumers, in any given month, but the option to select, instead, Category
or Subcategory, would be useful, as a minority of consumers require
totals among these Product levels on a regular basis to perform various
analytical pursuits. Finally, the capability to perform choices at run time to
sort upon the members of any of the subgroupings (regardless of the
actual choice of subgroup itself) would, according to the client
representatives, be of utility to a couple of analysts, as well.
These changes will
result in a multi-purpose report, the subgrouping (and its respective sort
order) presented by which can be dictated on the fly by the easy, ad hoc selection
of the Product level upon which to group, as well as to sort,
each time the report is executed. This will, of course, allow the consumers to
rapidly meet differing conditions and analysis needs. As is often the case
with the addition of parameterization, the organization will ultimately
be able to accomplish more with fewer reports.
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. Then, having
grasped the stated need, and having confirmed our understanding with the
intended audience, we begin the process of modifying the Sales Reason
Comparisons report to satisfy the information consumers.
Practice
Our
first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement the
newly required parameters. The
focus of our efforts will be the addition of the requested parameters into an OLAP
report containing a matrix data region (the mechanics behind adding
the capability, not the design of the report itself). To save time, we
will be working with a simple, pre-existing sample report in reality, the
business environment will typically require more sophistication. The process of
setting up the basic parameters is the same in real world scenarios, with
perhaps a more complicated set of underlying considerations. (As I have often
said, I virtually never encounter a client reporting requirement that does not
involve at least basic parameterization.)
We
will perform our practice session from inside the MSSQL Server Business
Intelligence Development Studio. For more exposure to the Business
Intelligence Development Studio itself, and the myriad design, development
and other evolutions we can perform within this powerful interface, see other
articles in this series, as well as within my 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 keep to
the focus of the article more efficiently.
Preparation: Create a Clone Report within the Reporting Services Development Environment
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) the MSSQL Server
2005 integrated business intelligence suite. Making preparatory
modifications, and then making the enhancements to the report to add the
functionality that forms the subject of our lesson, can be done easily within
the Business Intelligence Studio environment. Working with a copy
of the report will allow us the luxury of freely exploring our options, and will
leave us with 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
For purposes of our
practice session, we will open the AdventureWorks Sample Reports
project, which contains the sample reports that ship with the Reporting
Services component of the MSSQL Server 2005 suite. We will complete
our practice session within the sample project so as to save the time required
to set up a development environment from scratch within the Business
Intelligence Development Studio.
To open the AdventureWorks
Sample Reports project, please see the following procedure in the References
section of my articles index:
Ascertain Connectivity of the Shared Data Source
Lets
ensure we have a working 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. (The default for
the Adventure Works DW project samples connection is localhost,
which will not work correctly in such a side-by-side installation, as MSSQL
Server 2000 will have assumed the identity of the local PC by default.)
If you do not know how
to ascertain or modify connectivity of the Analysis Services data
source, please perform the steps of the following procedure in the References
section of my articles index:
Create a Copy of the Sales Reason Comparisons Report
We will begin with a copy
of the Reporting Services 2005 Sales Reason Comparisons OLAP report,
which we will use for our practice exercise. Creating a clone of the project means we can make
changes to select contents (perhaps as a part of later exploration with our
independent solution), while retaining the original sample in a pristine state
for other purposes, such as using it to accompany relevant sections of the Books
Online, and other documentation. Such uses may form a part of learning more
about Reporting Services (particularly an OLAP report using an Analysis
Services data source), and other components of the Microsoft
integrated business intelligence solution in general.
If you do not know how
to create a copy of an existing report, please perform the steps of the
following procedure in the References section of my articles index:
We now
have a clone OLAP report file within our Reporting Services 2005 Project,
with which we can proceed in the next section to make modifications for our
subsequent practice session.
Preparation: Modify the OLAP Report for Use within Our Practice Session
We will
next make a few modifications to prepare the report for our practice session. Our
objective will be to begin the session with a simple OLAP report that contains
no parameters. Lets
open the report in Layout view (for those of us not already there) and
make the necessary settings to place it into a state upon which we can commence
our practice steps.
1.
Right-click DBJ_OLAP_Report.rdl
(or your own choice of a similar report) in the Solution Explorer.
2.
Select Open
from the context menu that appears, as shown in Illustration 1, as
necessary.
Illustration 1: Opening the New Report ...
DBJ_OLAP_Report.rdl
opens in Layout
view.
We will
start with the Data tab.
We
enter the Data tab, where we will remove a handful of components that we
do not need for our practice session. We will accomplish this from the
perspective of the MDX Query Builder, the main components of which (in Design
mode) are labeled in Illustration 2 below.
Illustration 2: The MDX Query Builder Design Mode
4.
With the Dataset
selector, select the ProductList dataset, as depicted in Illustration
3.
Illustration 3: Select the ProductList Dataset ...
5.
Once the Dataset
loads, click the Delete button, as shown in Illustration 4.
Illustration 4: Deleting the Unneeded Dataset ...
6.
Click Yes
on the Microsoft Report Designer warning message dialog that appears
next, as depicted in Illustration 5.
Illustration 5: Confirm Intention to Delete ...
The
primary (and sole remaining) dataset, ProductData opens.
7.
Within the Query
pane of the MDX Query Builder, right-click the column heading for
Internet Total Product Cost.
8.
Click Delete
Internet Total Product Cost from the context menu that appears, as shown
in Illustration 6.
Illustration 6: Deleting the Unwanted Measure from the Dataset
9.
Within the Calculated
Members pane (bottom left corner of the Data tab), right-click the
sole Calculated Member, Profit.
10.
Click Delete
on the context menu that appears, as depicted in Illustration 7.
Illustration 7: Deleting the Calculated Member
11.
Click Yes,
when asked Are you sure ....
12.
Right-click
the single entry in the Filter pane (directly atop the Query pane).
13.
Click Delete
to eliminate the existing Product Category Parameter from the Filter
pane, as shown in Illustration 8.
Illustration 8: Deleting the Product Parameter from the Filter Pane
We
will make an addition to the Dataset next.
14.
Within the Metadata
pane, expand the Date dimension, by clicking the + sign to
its immediate left.
15.
Expand the Fiscal
folder that appears within the Date dimension.
16.
Expand the Fiscal
hierarchy (labeled Date.Fiscal) within the Fiscal folder.
17.
Drag the Month
level into the Data pane, dropping it to the left of the Sales Reason
column, as depicted in Illustration 9.
Illustration 9: Adding Months to the Dataset
The
new Month column appears, as desired. Having made the necessary changes on the Data
tab, we are ready to move to the Layout tab, where we can conclude our
preparatory modifications to the report file.
18.
Click the Layout
tab, as shown in Illustration 10.
Illustration 10: Click the Layout Tab
19.
On the
Layout tab, within the matrix data region, select the value
appearing underneath the Internet Total column heading.
20.
Right-click
the value in the text box (the value appears as =Sum(Fields!Internet_Total_Product_Cost.Value)
).
21.
Select Delete
from the context menu that appears, as depicted in Illustration 11.
Illustration 11: Delete the Value for Internet Total Product Cost
22.
Right-click
the upper left-hand corner of the Matrix Data Region (the gray square).
The
gray column and row bars disappear, as a light, patterned outline forms around
the matrix data region, and the context menu appears.
23.
Select Properties
from the context menu, as
shown in Illustration 12.
Illustration 12: Select Properties from the Context Menu ...
The Matrix
Properties dialog appears, defaulted to the General tab.
24.
Click the
Groups tab.
25.
In the Columns
section of the Groups tab (in the lower half of the tab), select the
middle group, named matrix1_Sales_Territory_Group.
26.
Click the
Edit button, as depicted in Illustration 13.
Illustration 13: Editing the Matrix1_Sales_Territory_Group Column Group ...
The Grouping
and Sorting Properties dialog appears, defaulted to its General tab.
27.
Atop the General
tab, change the existing Name to the following:
matrix1_Month
28.
Click the row
containing the Expression value (currently the expression is Fields!Sales_Territory_Group.Value),
within the Group on list, to enable the selector.
29.
Select =Fields!Month.Value
within the selector, as shown in Illustration 14.
Illustration 14: Replacing the Existing Group Expression ...
30.
Click OK,
to accept our modifications, and to close the Grouping and Sorting Properties dialog.
31.
Click OK
on the Groups tab, to close the Matrix Properties dialog.
32.
Once again on
the Layout tab, within the Matrix Data Region, right-click the
value appearing underneath the Internet Orders column heading (the
leftmost of the two remaining value cells).
33.
Select Properties
on the context menu that appears, as depicted in Illustration 15.
Illustration 15: Modifying Properties for the Count Value ...
34.
On the Textbox
Properties dialog that next appears, click the Format tab.
35.
Replace the
existing Format code setting (in the upper left corner of the Format
tab) with the following string:
#,###
Here
we are simply changing the existing format to one more appropriate for a count
value. The Format code appears on the Format tab of the Textbox
Properties dialog as shown
in Illustration 16.
Illustration 16: Replacing the Existing Format Code ...
36.
Click OK
to accept our modifications, and to dismiss the Textbox Properties dialog.
We next
need to change the column heading associated with the Group we modified
earlier, so that it reflects Months, versus the previous grouping
criteria of Sales Territory Group.
37.
Right-click
the text box containing the column label (immediately above the two measure
values column labels) in the Layout tab (the text box currently
contains the expression =Fields!Sales_Territory_Group.Value).
38.
Select Expression...
from the context menu that appears, as depicted in Illustration 17.
Illustration 17: Modifying the Primary Column Label
The Expression
Editor opens.
39.
Replace the
expression in the upper portion of the Editor with the following:
=Fields!Month.Value
Recall
that the intent is to label the column to reflect the nature of the Group we
have put in place: Months instead of Sales Territory Group.
The Expression
Editor appears, with our modification, as shown in Illustration 18.
Illustration 18: The Expression Editor with Our Substitution in Place
40.
Click OK
to accept our modification, and to dismiss the Expression Editor.
41.
Click the
column heading for which we have just changed the expression, if necessary, simply
to select the textbox.
42.
Change the Font
Size, in the Format toolbar atop the design environment, from 12pt
to 10pt, to make the presentation more compact, as depicted in Illustration
19.
Illustration 19: Change the Font to 10pt ...
All that
remains to complete our preparatory steps is to remove the pre-existing Report
Parameter. Recall that one of our earlier steps was to remove the Product
entry from the Filter pane (the sample report from which our report was
cloned came equipped with a parameterized Product filter). This entry
had been established with the Parameter checkbox selected, a common way
to create a parameterized filter, as we have seen in various other
articles of the MSSQL Server Reporting Services series. Because
parameterization was selected for the Product filter, it follows that a Report
Parameter might well have been created at the time the entry was
made. We will now remove the remaining Report Parameter.
43.
From the main
menu, select Report ->
Report Parameters...,
as shown in Illustration 20.
Illustration 20: Select Report -> Report Parameters...
The Report
Parameters dialog opens, and we see a single Report Parameter
listed.
44.
Ensure that
the ProductCategory Parameter is selected.
45.
Click the Remove
button, as depicted in Illustration 21.
Illustration 21: Removing the Residual Report Parameter ...
46.
Click OK
to accept our modifications, and to dismiss the (now empty) Report
Parameters dialog.
We will
execute the report to ascertain that our modifications are complete, and that
we have a working report for the practice session that follows.
47.
Click the Preview
tab, as shown in Illustration
22.
Illustration 22: Click the Preview Tab to Execute the Report
The Report
is being generated message briefly appears, and then the report displays.
The modified report appears as partially depicted in Illustration 23.
Illustration 23: The Modified Report (Partial View)
We note
that the fiscal months July 2001 through July 2004 appear (representing
a total of 37 months, the earliest of which are relatively sparse with
regard to data).
Our
report is now ready for the practice exercise, which we will begin in the next
section.
Procedure: Add Group and Group Member Sort Parameterization in Reporting Services 2005
Modify the Existing Dataset to Make Grouping Options Available to the Report
As we
noted earlier, our intent within this article is to examine the detailed
mechanics involved in adding and parameterizing a group, together
with the ad hoc capability to sort the members of the
selected group, within our report. First, we will need to add the
supporting data elements to our report. We will do this by modifying the
composition of the pre-existing ProductData dataset via adjustments to
the underlying dataset query. We will
accomplish these steps from the Data tab, to which we will return next.
1.
Click the
Data tab, once again
We
return to the Data tab, where this time we will need to add three data
elements to the pre-existing ProductData
dataset. As most of us know, when we create a dataset from an Analysis
Services data source, Report Designer initially offers us the MDX
query designer in Design mode. We can work within Design mode
to interactively build an MDX query using graphical elements. Design
mode affords us the capability to select a cube and to drag numerous
objects to the Data pane, including:
-
Dimensions
-
Dimension
Attributes
-
Levels
-
Hierarchies
-
Measures
-
Key
Performance Indicators (KPIs).
We can
also add calculated members, set default values for variables,
and automatically see result sets returned for the queries we build
(particularly useful as we make changes within the Query pane) to the Data
pane. Moreover, we can set filters (like the one we removed in our
earlier steps to prepare our OLAP report clone for use within our practice
session), to limit the data retrieved from the data source by the query,
and define parameters.
To
provide support for grouping upon each, we will add the Category, Subcategory
and Product levels of the Product dimension (Product
Categories hierarchy) to the Dataset next.
2.
Within the Metadata
pane, expand the Product dimension, by clicking the + sign
to its immediate left.
3.
Expand the Product
Categories hierarchy that appears within the Product dimension
(beneath the folders and attribute hierarchies that appeared when
we expanded the Product dimension).
Within
the newly expanded Product Categories hierarchy, we see the Category, Subcategory and Product
levels, as shown in Illustration 24.
Illustration 24: The Targeted Levels Appear ...
4.
Click the Product
Categories hierarchy to select it.
5.
Drag the Product
Categories hierarchy icon into the Data pane, dropping it to the right
of the Sales Territory Groups column, as depicted in Illustration 25.
Illustration 25: Adding the Levels the of the Product Categories Hierarchy to the Dataset
The
new Category, Subcategory and Product columns simultaneously appear, populating
as desired. Having
made the necessary changes on the Data tab, we are ready to move to the Layout
tab, where we can conclude our preparatory modifications to the report
file.
6.
Click the Layout
tab.
7.
Drag the Category
data field from the Datasets pane, across the single existing row cell
(containing =Fields!Sales_Reason.Value) within the matrix data
region, dropping it to the right of the cell (the I beam appears
at the drop point), as shown
in Illustration 26.
Illustration 26: Dragging the Category Data Field to the Matrix Data Region
Category (=Fields!Category.Value) appears in
the row axis of the report, to the right of Sales Reason, as depicted in
Illustration 27.
Illustration 27: Category Appears in the Rows Axis ...
NOTE: We will use this single cell for grouping any of the Category,
Subcategory and Product data elements, as we shall see, through the use of
an expression with which we replace the =Fields!Category.Value that currently occupies the
cell. For now, we will leave the current occupant here as a placeholder.
Add a New Dataset to Support the Proposed Report Parameters
Now we
will add a new dataset to support the proposed parameter picklists (the
parameter selections that the information consumers see at report run time).
1.
Click the
Data tab, once again.
2.
With the Dataset
selector, select <New Dataset ...>, as shown in Illustration
28.
Illustration 28: Select <New Dataset ...>
The Dataset
dialog opens, defaulted to the Query tab.
3.
Type the
following into the Name box :
RowGroup
4.
In the Data
source box just below, select AdventureWorks (shared).
5.
In the Query
string box, type (or cut and paste) the following query:
SELECT
'Product Category' AS ParameterCaption,
'Category' AS ParameterValue
UNION
SELECT 'Product Subcategory' AS ParameterCaption,
'Subcategory' AS ParameterValue
UNION
SELECT 'Product' AS ParameterCaption,
'Product' AS ParameterValue
The Dataset dialog appears,
with our input, as depicted in Illustration 29.
Illustration 29: The Dataset Dialog with Our Input ...
6.
Click OK to accept our input and to dismiss
the Dataset dialog.
We are taken to the Query
pane, where our newly crafted query appears as shown in Illustration 30.
Illustration 30: Our Query in the (Partially Depicted) Query Pane
7.
Click the Run
(!) button to test the query.
Data is returned, and appears in
the Data Pane as depicted
in Illustration 31.
Illustration 31: Data is Returned and Appears in the Data Pane
We
will see how the ParameterCaption and ParameterValue fields are
employed when we set up the two report parameters in the steps that
follow.
Set Up the Grouping and Group Member Sorting Report Parameters
We are ready at this
point to add a couple of new report parameters. We will reference these
parameters later in our procedure.
1.
Select Report
-> Report Parameters ..., from the main menu, as shown in Illustration 32.
Illustration 32: Select Report --> Report Parameters ...
The now
empty Report Parameters dialog opens.
2.
Click Add
to begin adding our first report parameter.
3.
Type the
following into the Name box in the Properties section (at the top
of the right side of the dialog):
RowGroupBy
4.
Type the
following into the Prompt box in the Properties section (the
second box down from the Name box ):
Group Rows By:
5.
Clear any
check marks in the lower part of the Properties section.
6.
In the Available
values section (just below the Properties section on the right
side of the dialog), click the From query radio button to select it.
The Dataset,
Value field and Label field selectors appear.
7.
Click the
downward pointing arrow on the Dataset selector (currently displaying
the ProductData selection) within the Data tab.
8.
Select the new
RowGroup dataset.
9.
In like
manner, select ParameterValue within the Value field.
10.
Select ParameterCaption
within the Label field.
11.
Leave all
other settings at default.
The Report
Parameters dialog for the new RowGroupBy parameter appears, with our
input, as depicted in
Illustration 33.
Illustration 33: Settings for the New RowGroupBy Parameter
We will
next create a report parameter to for use in sorting the members of the
group we select at runtime.
12.
Click Add,
once again, in the bottom left corner of the still open Report Parameters dialog.
13.
Type the following
into the Name box in the Properties section (at the top of the
right side of the dialog):
RowSortBy
14.
Type the
following into the Prompt box in the Properties section (the
second box down from the Name box ):
Sort Rows By:
15.
Clear any
check marks in the lower part of the Properties section, as we did with
the first report parameter above.
16.
In the Available
values section (just below the Properties section on the right
side of the dialog), click the From query radio button to select it.
The Dataset,
Value field and Label field selectors appear, as we saw earlier.
17.
Ensure that
the new RowGroup dataset is selected within the Dataset selector.
18.
In like
manner, select ParameterValue within the Value field.
19.
Select ParameterCaption
within the Label field.
20.
Leave all
other settings at default.
The Report
Parameters dialog for the new RowSortBy parameter appears, with our
input, as shown in
Illustration 34.
Illustration 34: Settings for the New RowSortBy Parameter
21.
Click the OK
button on the Report Parameters dialog to accept the new parameters and to
dismiss the dialog.
Lets
preview the report at this stage, simply to ascertain that the new parameters
appear to work properly (we will have to do some further work on the Layout
tab before they actually affect the report display we just want to determine
that the picklists appear appropriate, etc.).
22.
Click the Preview
tab, once again.
The two
new report parameters appear atop the report - actually execution
is held up until we make a selection here. We can click the selector arrows and
see that the three grouping / sorting criteria appear in each parameter
picklist, as depicted (for the Group Rows By parameter) in
Illustration 35.
Illustration 35: The Parameter Picklists (Only the Group Rows By Parameter is Shown Here) Appear to Work Properly
We are
now ready to hook things up within the Layout tab of the report so
that the new parameters actually work, and so that the report reflects our
selections in a user-friendly manner.
Set
Up the Grouping and Group Member Sorting Report Parameters
We
will move now to the Layout tab, where we have several adjustments to
make to leverage our new report parameters.
1.
Click the Layout
tab.
2.
Click within
the Matrix data region, to cause the gray header bars to appear on top
and to the left.
3. Right-click the upper left corner,
where the gray bars meet, as we did in the preparation steps above.
4.
Select Properties
from the context menu that appears, as we did earlier.
The Matrix
Properties dialog appears, once again, defaulted to the General tab.
5.
Click the
Groups tab.
6.
In the Rows
section of the Groups tab (in the upper half of the tab), select the
second group, named matrix1_Category.
7.
Click the
Edit button, once again.
The Grouping
and Sorting Properties dialog appears, as before, defaulted to its General
tab.
8.
Atop the General
tab, change the existing Name to the following:
matrix1_RowGroupBy
9.
Click the row
containing the Expression value (currently the expression is =Fields!Category.Value),
within the Group on list, to enable the selector.
10.
Select <Expression...>
within the selector (the top entry), as shown in Illustration 36.
Illustration 36: Replacing the Existing Group Expression ...
The Expression
Editor opens.
11.
Replace the
expression in the upper portion of the Editor with the following:
=Fields(Parameters!RowGroupBy.Value).Value
Recall
that the intent is to enforce grouping upon our selection via the runtime
report parameter (RowGroupBy). Reporting Services once again
allows us to answer the need via an expression.
The Expression
Editor appears, with our modification, as depicted in Illustration 37.
Illustration 37: The Expression Editor with Our Substitution in Place
12.
Click OK
to accept our modification, and to dismiss the Expression Editor.
13.
Click the
Sorting tab.
14.
In the Expression
box of the section labeled Sort on, select <Expression...> within
the selector (the top entry), as shown in Illustration 38.
Illustration 38: Replacing the Existing Sorting Expression ...
The Expression
Editor opens.
15.
Type, or cut
and paste, the following expression into the upper portion of the Editor
with the following:
=Fields(Parameters!RowSortBy.Value).Value
The Expression
Editor appears, with our addition, as depicted in Illustration 39.
Illustration 39: The Expression Editor with Our Newly Added Syntax
16.
Click OK
to accept our modification, and to dismiss the Expression Editor.
We
return to the Sorting tab, where we see, in the Direction setting
adjacent to the Expression setting, a default of Ascending, as
shown in Illustration
40.
Illustration 40: The Sorting Tab with Our Input ...
17.
Leaving the Direction setting at its default of Ascending,
click OK to accept our input and to dismiss the Grouping and Sorting Properties dialog.
18.
Click OK
on the Matrix Properties dialog next, to accept all changes and dismiss
the dialog.
We are
returned to the Layout tab, where we will perform a couple of remaining
modifications to make the new report more consumer-friendly at runtime. First,
lets modify the subgroups label (we left it at =Fields!Category.Value when we created the
column in earlier steps).
19.
Right-click
the cell containing =Fields!Category.Value.
20.
Select Expression
... from the context menu that appears, as depicted in Illustration 41.
Illustration 41: Replacing the Existing Label Expression ...
The Expression
Editor opens.
21.
Replace the
expression within the upper pane (currently =Fields!Category.Value)
with the following:
=Fields(Parameters!RowGroupBy.Value).Value
The Expression
Editor appears, with our substitution, as shown in Illustration 42.
Illustration 42: The Expression Editor with Our Substituted Syntax
22.
Click OK
to accept our modification, and to dismiss the Expression Editor.
Next,
we will add a simple column heading, which we will insert atop the new subgroup
label, to make our layout a little more self-explanatory.
23.
Click the Sales
Reason textbox to select it.
24.
Right-click
the outline that appears, surrounding the box.
25.
Select Copy
from the context menu that appears, as depicted in Illustration 43.
Illustration 43: Copying the Sales Reason Textbox ...
26.
Click into the
open space to the right of the Sales Reason label, positioning the
cursor approximately as shown with the X in Illustration 44.
Illustration 44: Placing the Cursor for the Paste Operation ...
27.
Right-click
the canvas where the cursor rests, and select Paste from the context
menu that appears, as depicted in Illustration 45.
Illustration 45: Pasting the Sales Reason Textbox ...
A
second copy of the Sales Reason textbox appears near the drop point.
28.
Align the
bottom left corner of the new Sales Reason textbox to rest at the upper
left corner of the second row group cell (containing our new input of =Fields(Parameters!RowGroupBy.Value).Value),
as shown in Illustration 46.
Illustration 46: Aligning the Copied Sales Reason Textbox as a Label for the Second Row Group
29.
Right-click
the new Sales Reason textbox copy.
30.
Select Expression
from the context menu that appears, as depicted in Illustration 47.
Illustration 47: Select Expression ... from the Context Menu ...
The Expression
Editor opens, once again.
31.
Replace the
expression within the upper pane (currently Sales
Reason) with the following:
=Parameters!RowGroupBy.Label
The Expression
Editor appears, with our substituted syntax, as shown in Illustration 48.
Illustration 48: The Expression Editor with Our Substituted Syntax
32.
Click OK
to accept our modification, and to dismiss the Expression Editor.
Lets
preview the report at this juncture to confirm that we have taken the steps
needed to meet the requirements specified by our client colleagues.
33.
Click the Preview
tab, once again.
The two
new report parameters appear atop the report, as we noted earlier. This
time we will actually make parameter selections and then execute the report.
34.
Select Product
within the Group Rows By parameter picklist.
35.
Select Product
within the Sort Rows By parameter picklist.
Our
selection criteria appear in the parameter selectors as depicted in
Illustration 49.
Illustration 49: Our Test Selections Appear ...
36.
Click the View
Report button to execute the report.
The Report
is being generated message briefly appears, and then we see the report
display, similar to that partially shown in Illustration 50.
Illustration 50: The New Report (Partial View) Generates for the Chosen Parameter Selections
As we can
see, our Product group members appear, in ascending order, as
requested. Moreover, our new column label dynamically reflects the selected
group, as well. The client representatives express satisfaction with our
efforts, and state that, with a few cosmetic changes, the report will be ready
for deployment to the targeted information consumer group.
37.
Experiment
further with the report, if desired.
38.
When finished
with the report, click the Layout tab.
39.
Select File
-> Save DBJ_OLAP_Report.rdl As ... to save our work, up to this
point, to a location where it can be easily accessed for later reference.
40.
Select File
-> Exit to leave the design environment,
when ready.
Conclusion ...
In
this article, we explored a rudimentary approach to the parameterization
of grouping within an Analysis Services report containing a matrix
data region. Beginning with a discussion surrounding the general concepts
of parameterization, we continued into a practice session where we set
up a scenario within which we work with a basic OLAP report, to expose the
steps involved. In
examining the concepts underlying runtime grouping parameterization
(along with ad hoc sorting of the selected groups members) we first described a stated
reporting need of our hypothetical client a need that we would set out to
support in our practice session.
We
next prepared a copy of a sample report sourced from Analysis Services, making
structural modifications to the clone report, to meet peripheral client
requests, as well as to prepare for our practice exercise session with parameters
surrounding a matrix data region. We modified the existing dataset,
and added a new dataset, to support group and group member sorting
parameterization, and then made modifications to the report layout
to support the stated client reporting needs.
We next
added report parameters to support group and group member sorting
parameterization, and modified matrix settings to fully enable the newly
supported parameterization. We then modified the face of the report to
dynamically reflect group and group member sorting parameter selections
made at run time. Finally, we discussed the results obtained with the
development techniques that we exploited throughout our practice session.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.