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.
Introduction
As we learned in Mastering OLAP Reports: Parameters for Analysis Services
Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services
Reporting, Pt. II, a common enterprise reporting
requirement is the capability to filter reports at run time for specific
information. This is typically managed via parameterization, also known
as prompting, where the filter criteria is requested (and hence the
consumer is prompted) when the report is run. Depending upon the parameter
type (the most common are type-in and picklist), the filters are
typically enacted when the consumer types or selects a value, or a series of
values.
Type-in parameters accept directly typed user input for the
values upon which the report is based. Alternatively, the picklist
presents a selection of choices to a consumer based upon a static file, a
dataset from a larger data source, or through other means. The picklist is
often the tool of choice, because of its inherent elimination of typing errors,
as well as the enforcement of standard selections. A well-constructed picklist
makes selection easy for the consumer, who is not often pleased with a long
scrolling process, or other cumbersome method, as the initial step in
generating a commonly requested report.
Over the years, I have implemented parameters in many ways
to fit many client business needs, ranging from the most basic picklist prompts
to sophisticated prompts that push the envelope with regard to going beyond
out-of-the-box uses for these components. As I discussed in detail in Mastering OLAP Reports: Parameters for Analysis Services
Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services
Reporting, Pt. II, one
of the most impressive enhancements introduced with Reporting Services 2005
is its capability to automatically create the complete chain of components,
including filter, report parameter, and supporting dataset(s) we need to
quickly add basic parameterized filters to our reports. While the
automatically created objects serve us well with minimal modification in many
cases, we often have to tweak the components supporting parameterization a
bit to obtain more sophisticated features.
Another important consideration during the design phase of
any implementation effort, too, is that, while we can still manage much
customization within the reporting / presentation layer of our integrated
business intelligence solution, we also have multiple options for placing the
intelligence behind parameterization (as well as other functionality) in
other layers of the solution. For example, I have often supported picklist parameterization with support
objects I have created within the Analysis Services cube that is used as
a data source for the reports under consideration, and so forth. (For an
example of implementing support for a hierarchical picklist in this
manner, see my Database Journal article Create
a Cube-Based Hierarchical Picklist.) When we consider the
fact that we have many options for where to install the intelligence within
the multi-layered BI solution, we can begin to see that a grasp of all layers
is critical. For a general summary of my opinions surrounding the importance
of thinking "multi-dimensionally" within the design and
implementation of a business intelligence system, see Multi-Layered Business Intelligence Solutions ... Require
Multi-Layered Architects.
In this article, we will discuss the general concepts, and
then set up a scenario within which we work with a basic OLAP report, to expose
the steps involved in meeting a basic need of a hypothetical client in
modifying the default behavior of automatically created parameter picklists to
display an All option at runtime. We will review the mechanics that
underlie the automatic creation of parameter support objects by Reporting
Services, as well as touching upon potential issues that might arise when
we modify elements of those objects once they are created. In examining the requested
addition and customization of parameters 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 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 the customization of Parameter Picklists within our report;
-
Create, within
the graphical Design Mode of the MDX Query Builder, multiple
filters for which parameterization is enabled via the Filter pane
setting;
-
Examine the
automatically created Report Parameters and their settings;
-
Review the
automatically created Datasets underlying the new Report Parameters;
-
Discuss how
the various components are tied together, and potential challenges we face in
modifying these objects without consideration of the resulting dependencies;
-
Make
modifications to remove the All selection that appears by default in
our newly created parameters picklists at runtime;
-
Preview the
report to observe the effectiveness of our solution, as well as the cascading
nature of the parameters created, in runtime action.
Customizing Auto-Created Parameter Support Objects in Reporting Services 2005
Objective and Business Scenario
As we have emphasized in earlier
articles, the reporting / presentation layer of the integrated Microsoft
business intelligence solution is often the only point of interaction with
corporate information stores for organizational information consumers. Parameterization
not only makes this interface more user-friendly, but it affords the tandem
benefit of enabling knowledgeable report authors to leverage single reports for
multiple uses, delivering more for less from a development and maintenance
perspective.
We introduced parameterization in
general, discussing the challenges that faced many of us within the Reporting Services
2005 environment, in Mastering
OLAP Reports: Parameters for Analysis Services Reporting, Pt. I. We continued our general discussion of parameterization
in Mastering
OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, where we explored
the underlying mechanics that support parameters, focusing particularly upon several
events that take place in conjunction with parameter creation in the design
environment. We further extended our Part
I examination of the interaction
among the various components that comprise and support run-time parameterization,
and focused, in Part II, upon the actions that Reporting Services
takes behind the scenes in our creation of cascading parameters.
In this two-part article,
we continue the
extended exmination of Parameters in Reporting Services 2005,
concentrating upon a couple of specific refinements we can make to our
parameters to fine tune them to local business environments. We will begin,
once again, by adding simple filter / parameter combinations to a basic sample OLAP
report, containing a Matrix data region, via the graphical interface of
the MDX Query Builder. We will review, as part of our preparation of the
sample report for our practice session, the objects that Reporting Services
automatically creates, in conjunction with our additions, to support runtime parameterization.
We will then delve into the modifications we need to make to meet the
requirements of a hypothetical client, the AdventureWorks organization.
To detail the
specifics, we have been contacted by a team with which we consult regularly,
the Adventure Works Customer Care department, whose work centers around
analyzing and reporting upon customer satisfaction indicators, among various
other information, in support of the overall organization in accomplishing its
objectives. Our client colleagues inform us that information consumers have
asked that they refine an existing OLAP report, Sales Reason Comparisons, to make the existing
parameterization a bit more user friendly. First, they wish to be able to
select Year, Quarter and Month for which the report is run
(the initial report, a sample authored by an intermediate-level employee who
has since returned to his home country, presents data for all time, allowing
users the capability of selecting Product Class(es) only at runtime). Moreover,
the team wants a brief overview of the mechanics involved in the automatic
creation of the support objects that occurs as we create the new parameterized
filters, primarily so that they can understand the relationships between
these components to allow for ongoing maintenance.
Because the client
representatives have worked with date-related parameterization in other reports
and systems, they already know that, in addition to merely being able to
specify dates through standard picklists, they want to request a couple of
enhancements to the simple prompts they have seen thus far in reports with
similar runtime parameters. First, they have noted in several OLAP reports
that, even though the authors did not design the reports to provide multi-value
selection in the picklists (something they so not want in the report under
consideration, either), an All Periods selection appears in each of the
date selectors (say, for Year, Quarter, Month, etc.),
which confuses some consumers (reporting requirements will be restricted to
single selections within each level of the Date dimension to meet the
current, simple needs of the group). Moreover, the group tells us that they
want the prompts to reflect intelligent, intuitive defaults, and not just arbitrary,
hard-coded values or the <Select a value> label that might
otherwise appear, if no defaults at all are in place.
We
confirm our understanding of the foregoing needs, and propose to first add the
requested date-related parameters, and then to eliminate the All Periods
selection from the respective picklists entirely. Having accomplished this, we
will construct working examples, in the second half of this article, of ways to
provide the intuitive defaults that our colleagues have requested for the new
report parameters examples, we add, that can be modified to specify different
defaults, should the consumers request them after road testing the initial
labels.
Preparation
Our
first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement the
solutions that we propose to meet our clients requirements. The focus of our efforts in this,
the first half of this article, will be the elimination of the All
level we have described earlier. In Part II of this article we will
examine the addition of intuitive parameter defaults for the same parameters.
Because of time limitations, 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 basic parameterization is the
same in real world scenarios, with perhaps a more complex set of underlying
considerations. (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. We will examine a solution for the
elimination of the default All selections within the parameter
picklists, then, within the first half of this article, and position our
report for the addition of intelligent parameter defaults in the second half.
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 Development 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 report 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. We can, therefore, use the original as 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 basic parameterized filters as required by our client
colleagues. 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 from which we can commence
our from scratch 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 ...
We will
begin by adding additional parameters to our sample report. These parameters
will initially be of a standard, basic picklist variety. We will then work
with these parameters as we explore our options for eliminating a
characteristic default behavior of their respective picklists (in this half of
our article), and for adding intuitive parameter defaults to our report (in the
second half of our article).
Add
Additional Parameters through the Query Builder Interface and Review the
Automatic Creation of Support Objects
As we
stated in Mastering OLAP Reports: Parameters for Analysis Services
Reporting, Pt. I, and elsewhere within this,
and other of my, series, the Design Mode button in the toolbar of the Data
tab allows us to easily shift between Design view and Query view,
when working with our OLAP queries. The idea, obviously, is to provide those
who are not at ease with direct MDX a means of creating queries within a
graphical interface.
We
have created the basic report with the core datasets, and are ready to add three
rudimentary, date-related prompts that will meet our objectives. We will create these prompts
within Design view, by taking the following steps:
We
enter the Data view, which defaults to the presentation of the first of
two pre-existing datasets, ProductData. To state what is probably
obvious, we can tell that we are in Design view, primarily because we see the column headings for our
query components in the Data grid, and we do not see MDX syntax. We
also see that the Design Mode button is depressed, as depicted in Illustration
2.
Illustration 2: MDX
Query Builder in Design Mode (Compressed View)
We have returned
to add three date-related filters (each of which we shall parameterize): one
each for Calendar Year, Calendar Quarter and Calendar Month.
Lets first run the existing query to ascertain that all is in order with
regard to the retrieval of data.
2.
Click the Execute
Query button atop the Data tab, as shown in Illustration 3.
Illustration 3: Click
Execute Query to Retrieve Data with the Existing Query ...
Data populates the Data
pane, and appears similar to that depicted in Illustration 4.
Illustration 4: Initial
Query Operates Successfully in a Quick Test
Having
ascertained that the core query is in working order, we will now add the
date-related filters, as well as the vanilla parameters that we intend for
them to support.
3.
In the Metadata
pane, expand the Date dimension, as required.
4.
Within the
expanded Date dimension, expand the Calendar folder.
5.
Right-click
the Calendar Year attribute hierarchy that appears within the expanded Calendar
folder.
6.
Select Add
to Filter within the context menu that appears, as shown in Illustration
5.
Illustration 5: Select
Add to Filter in the Context Menu
The Date
dimension populates the Dimension column, within a newly created row
of the Filter pane. The Calendar Year attribute hierarchy
appears within
the Hierarchy column, as well.
7.
Leave the Operator
setting (to the immediate right of the Hierarchy column) at its default
of Equal.
8.
Place a
checkmark in the checkbox that appears in the Parameters setting (to the
immediate right of the Filter Expression setting), the rightmost column
in the new row of the Filter pane.
Our
completed filter-with-parameter entry appears in the new row of the Filter
pane, as depicted in Illustration 6.
Illustration 6: The
Filter Pane Reflects Our Addition of the Calendar
Year Attribute Hierarchy
We have
successfully added a new parameter, in conjunction with a filter,
within the dataset underlying our report. The addition of the parameter
within the graphical interface, as we shall see, has already begun a sequence
of events that will trigger the automatic creation of a corresponding Report
Parameter. A supporting dataset will also be created, as soon as we
move to the Layout tab. But first, we need to add entries within the Filter
pane for the additional Date filters we intend to add.
NOTE: As we have noted in other articles, it is important to
remember that, although the Report Parameter and its underlying Dataset
are created automatically when we enable parameterization within the Filter
pane of the MDX Query Builder (Design Mode), the removal of the Parameter
checkmark within the associated row of the Filter pane, or even the
deletion of the entire corresponding filter row, will not produce an opposite
effect. The Report Parameter and the dataset will remain until
they are manually removed. Moreover, the disablement of parameterization
within the Filter pane, followed by re-enablement and / or recreation of
the Filter pane entry will, unless we intervene before we re-enable /
recreate the Parameter, result in the creation of a second Report
Parameter.
Lets pause from our preparation steps and take this opportunity to view some of the behind the scenes construction that occurs as we progress our report.
9. Click the Layout tab to transit to Layout view.
10. Select Report -> Report Parameters from the main menu.
We recall that a single Report Parameter for Product Category existed in the original report, before we began our preparation for the practice exercise. Yet we note, within the Report Parameters dialog that has appeared, the presence of a new Report Parameter, called DateCalendarYear. This Report Parameter was created automatically when we designated our new row within the Filter pane as a Parameter (via the enabling checkbox).
Another important observation we might make is that the From query setting within the Available values section of the dialog is active (the radio button is selected), and that the setting references a Dataset (and Value and Label fields therein) with the same name as the new Report Parameter - a Dataset, we will discover, that has also been automatically created. The purpose of this Dataset is to support the selection picklist for the new Report Parameter.
Finally, we note a couple of other settings that have been made for us: in the Prompt section (within the upper third of the dialog) of the Report Parameters dialog, the Multi-value setting is checked. While we might certainly leave the setting in place, we will disable it for the purposes of our practice session. In addition, in the Default values section (in the lower third of the dialog), we find that the radio button for Null has been preselected. For the time being, we will leave the latter preselected setting (Null) in place; the disablement of the Allow null value setting in the Prompt section effectively means that, even if the parameter selector defaults to blank at runtime, the information consumer will be forced to make a selection before he / she can proceed further with executing the report. This arrangement works well in many environments, particularly where default settings tend to be overlooked, at times, and the report run with unintended constraints. Obviously, settings should be adjusted to accommodate local requirements and realities, and, as we shall see in our practice session, we can accommodate intuitive defaults in conjunction with some modifications in this area.
11. Replace the default string that appears within the Prompt box, Date.Calendar Year, with the following, more user-friendly label:
Calendar Year
12. Uncheck the checkbox labeled Multi-value.
13. In the Available Values section (the middle section of the Report Parameters dialog), in the bottom of the three settings, Label field, modify the setting from ParameterCaptionIndented to ParameterCaption.
The Report Parameters dialog appears, with our adjustments, as shown in Illustration 7.
Illustration 7: Our Settings for the Automatically Created Report Parameter
14. Click OK to accept settings, and to dismiss the Report Parameters dialog.
15. Click the Preview tab next, to execute the report.
The report runs, and then displays a drop-down parameter prompt labeled Calendar Year, to the right of the pre-existing Product Category prompt. The selector box for Calendar Year, by default, contains the placeholder <Select a Value>, and, when it is expanded, via the downward pointing arrow on its right side, appears as depicted in Illustration 8.
Illustration 8: The New Runtime Parameter Appears ...
16. Click the downward arrow to the right of the Calendar Year parameter selector to expose the picklist of Year choices.
17. Select CY 2003.
18. Click the View Report button in the upper right corner of the Preview tab.
The report runs again, and returns for the Date level groupings, for Calendar Year 2003, as expected.
We will now return to the Data tab, to examine the Dataset which has been automatically created, to populate the picklist in support of the new Report Parameter.
19. Click the Data tab.
20. Click the downward selector arrow on the right side of the Dataset selector.
21. Select the new DateCalendarYear dataset that appears, as shown in Illustration 9.
Illustration 9: Select the New Dataset ...
The Dataset loads, and presents the MDX syntax in Query view. This is the query that has been automatically created to generate the picklist to support the new DateCalendarYear Report Parameter (we saw the DateCalendarYear Dataset referenced within the Dataset selector, where columns from the new Dataset were assigned to the Value field and Label field selectors, within the Available values section of the dialog). As we have discussed, the Dataset was created automatically, after which the Report Parameter was itself created and connected to its underlying Dataset via the three settings we described (which were also established automatically by Reporting Services).
As we noted in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, the automatically generated MDX query creates additional fields, in addition to retrieving data from the cube; the purpose of the group of fields is to support the parameter picklist, from which information consumers make selections at runtime. These fields are:
- ParameterCaption
- ParameterValue
- ParameterLevel
All three fields are products of calculated members created via MDX syntax (using the WITH MEMBER keyword combination). As we discover within other articles of the series, there are multiple ways to approach picklist support (I very often construct members such as the above at the Analysis Services level, to afford central maintenance and reusability, among other, perhaps less obvious, advantages). This is simply the mechanism through which Reporting Services accomplishes support of the automatically generated Report Parameter. As we saw earlier, the Value and Label settings within the Report Parameter dialog reference the ParameterValue and ParameterCaption fields, respectively, within this Dataset.
NOTE: While we will encounter and discuss MDX functions, expressions, queries, and related subject matter throughout the MSSQL Reporting Services series, please refer to the articles of my MDX Essentials series, whose member articles are published monthly here at Database Journal, for detailed information about MDX.
Having examined various details about the objects that are created to support a parameterized filter, we will return to the Data tab to create the remaining two Date parameters that we will require for our practice session. While, as we saw above, we might simply move the hierarchical levels that we need to the Filter pane using the context menu for that matter, we might drag them as well - it is sometimes easier to work directly with the pane selectors. (We can, of course, still refer to the expanded Date.Calendar hierarchy within the Metadata pane as a visual reference, in this case.)
We will add two more parameterized filters directly in the Filter pane next.
22. Using the dropdown Dataset selector, as before, select ProductData, to return to the primary dataset and Filter pane.
23. Click the leftmost box (which contains the placeholder <Select dimension>, as before), within the Dimension column, in the bottom row of the Filter pane (this time, beneath the box containing Date, which we added in creating the first entry in the Filter pane earlier).
The dropdown selector is again enabled.
24. Select Date from the list that appears in the selector, once again.
25. Select Date.Calendar Quarter of Year within the Hierarchy box to the immediate right, using the selector that is built in.
26. Leave the Operator setting (to the immediate right of the Hierarchy setting) at its default of Equal, as we did within the first entry.
We will take a small detour, at this point, to confirm our understanding as to the timing of Report Parameters creation, based upon our activities within the Filter pane.
27. Leaving settings in the third row of the Filter pane, select Report ->Report Parameters ... from the main menu, once again.
The Report Parameters dialog opens, revealing only the two Report Parameters (the original ProductCategory parameter, along with the DateCalendarYear parameter we created in the immediately preceding steps) that we saw earlier,
28. Click OK to close the Report Parameters dialog.
29. Returning to the third row of the Filter pane, where we left off with our settings to establish a second new parameterized filter, place a checkmark in the checkbox that appears in the Parameters setting, once again.
30. Leaving the Filter pane once again, select Report ->Report Parameters ... from the main menu.
The Report Parameter dialog opens, this time revealing three Report Parameters. We see that the ProductCategory and DateCalendarYear parameters that existed before have been joined by the DateCalendarQuarterofYear parameter. It thus becomes evident that the act of checking the Parameters checkbox in the respective Filter pane row triggers the creation of the associated Report Parameter.
31. Click the DateCalendarQuarterofYear item within the Parameters list to select the new parameter within the Report Parameters dialog.
32. Replace the default string that appears within the Prompt box, Date.Calendar Quarter of Year, with the following, more user-friendly label:
Calendar Quarter
33. Uncheck the checkbox for the Multi-value setting, as we did with the first Report Parameter.
34. In the Available Values section (the middle section of the Report Parameters dialog), in the bottom of the three settings, Label field, modify the setting from ParameterCaptionIndented to ParameterCaption, once again.
The Report Parameters dialog - with the DateCalendarQuarterofYear parameter selected - appears, with our adjustments, as depicted in Illustration 10.
Illustration 10: Settings for the New Calendar Quarter Report Parameter
35. Click OK to accept settings, and to dismiss the Report Parameters dialog.
Our completed entry appears in the third row of the Filter pane.
36. Click the downward selector arrow on the right side of the Dataset selector, once again.
An examination of the Dataset selection picklist reveals only the pre-existing ProductData and ProductList, together with the newly added DateCalendarYear, datasets. In this manner, we can verify that, although the creation of the Report Parameter, complete with internal references to a supporting dataset, is triggered by our placing a checkmark in the Parameters setting of the respective Filter pane row, the dataset itself is not fully instantiated until we leave the Data tab (as we did in our previous example, when we clicked the Layout tab to transit to the Layout view).
We will now create the final parameterized filter within the Filter pane.
37. In the bottom row of the Filter pane, click the leftmost box within the Dimension column, once again.
38. Select Date from the list that appears in the selector, as we did before.
39. Select Date.Month of Year within the Hierarchy box to the immediate right, using the selector that is built in.
40. Leave the Operator setting (to the immediate right of the Hierarchy setting) at its default of Equal, as we did within the first entry.
41. Place a checkmark in the checkbox that appears in the Parameters column, as we have done with the previous two row entries we have made in the Filter pane.
The Filter pane, with all four entries, appears as shown in Illustration 11.
Illustration 11: The Four Parameterized Filters in the Filter Pane
42. Select Report ->Report Parameters ... from the main menu, as before.
The Report Parameters dialog opens, this time revealing four Report Parameters.
43. Click the DateMonthofYear listing (the bottom of the four) within the Parameters list to select the new parameter within the Report Parameters dialog.
44. Replace the default string that appears within the Prompt box, Date.Month of Year, with the following, more user-friendly label:
Month
45. Uncheck the checkbox for the Multi-value setting, as we did with the previous two parameters.
46. In the Available Values section (the middle section of the Report Parameters dialog), in the bottom of the three settings, Label field, modify the setting from ParameterCaptionIndented to ParameterCaption, once more.
The Report Parameters dialog - with the DateMonthofYear parameter selected - appears, with our adjustments, as depicted in Illustration 12.
Illustration 12: Settings for the New Month Report Parameter
47. Click OK to accept settings, and to dismiss the Report Parameters dialog.
48. Click the downward selector arrow on the right side of the Dataset selector, once again.
An examination of the Dataset selection picklist reveals only the ProductData, ProductList and DateCalendarYear Datasets, as we left them earlier.
49. Click the Layout tab, once again, to transit away from the Data tab.
50. Click the Data tab, once more, to return immediately.
51. Click the downward selector arrow on the right side of the Dataset selector, one last time.
Based upon our earlier discussion, we see the behavior we have expected. All five Datasets, including the originally existing two Datasets and the three we have added via the enactment of parameterized filters within the Filter pane (and subsequent departure from the Data tab), now appear, as depicted in Illustration 13.
Illustration 13: All Datasets Appear as Expected ...
We have successfully added three rudimentary parameters, based upon filters we added within the Filter pane of the primary Dataset underlying our report. The addition of the parameters within the graphical interface, as we have verified, triggered the automatic creation of corresponding Report Parameters. Supporting datasets were also created, as soon as we moved away from the Data tab. The timing of the automatic creation of objects can thus be understood; the sequencing becomes very important in the event that we need to modify or remove any of the objects after they are initially created and aligned.
NOTE: As we have noted previously, although a Report Parameter and its underlying Dataset are created automatically when we enable parameterization within the Filter pane of the MDX Query Builder (Design Mode), the removal of the Parameter checkmark within the associated row of the Filter pane, or even the deletion of the entire corresponding filter row, will not produce an opposite effect. The Report Parameter and the dataset will remain until they are manually removed. Moreover, the disablement of parameterization within the Filter pane, followed by re-enablement and / or recreation of the Filter pane entry will, unless we intervene before we re-enable / recreate the parameter, result in the creation of an additional Report Parameter.
Order is somewhat important in preventing confusion: for example, when deleting, the Report Parameter needs to be deleted first, with the underlying dataset (to which it refers) being deleted next, before creating a new Report Parameter even of the same name within the Filter pane. A Report Parameter can, of course, be repointed, but, unless we manually intervene, will likely be left pointing to an incorrect, remaining dataset if the dataset to which it was initially married was, for some reason, deleted from under it.
It is important to remember that changing a name can also have consequences from the perspective of alignment, and that if one side of the equation is modified, the other side must also be modified. Changing the Prompt label has no adverse effect, as we have seen multiple times within the steps of our practice exercise.
Lets perform a quick test to see that all is operating as expected in our sample report, before venturing into the practice session that follows, where we eliminate the All Periods selection within the date-related picklists. In the meantime, we will be setting up the environment for the second half of our article, where we will establish intuitive run-time defaults within our newly added date parameters.
52. Click the Preview tab.
DBJ_OLAP_Report.rdl initializes, and the first prompt (based upon the pre-existing ProductCategory parameter), becomes enabled.
53. Leave the Product Category prompt selection at its default of Bikes, Components.
54. Click the downward pointing arrow on the right side of the Calendar Year selector.
55. Select CY 2003 within the Calendar Year parameter picklist, as shown in Illustration 14.
Illustration 14: Select CY 2003 within the Calendar Year Parameter Picklist
We notice the All Periods selection that appears atop each of the daterelated picklists we have created. This is the All option that we will eliminate within the practice session, as one of our objectives in meeting the expressed needs of our client colleagues.
Once we make a choice within the Calendar Year dropdown selector, the next parameter selector, Calendar Quarter, becomes enabled.
56. Select CY Q3 from the Calendar Quarter picklist.
57. Select the month of August in the Calendar Month parameter picklist.
58. Click the View Report button.
The report executes quickly and returns the data for the selections we have made within our parameter picklists, in a manner similar to that depicted in Illustration 15.
Illustration 15: The Sample Report Operates as Expected with New Date Parameters
Having obtained assurance that the sample report operates as expected, we are ready to proceed with its use in our practice session, which follows below.
Practice
Having
added the requested date-related parameters in the foregoing Preparation
section, we are ready to begin making modifications to the clients existing
report to meet the two requirements they have communicated. We will first
eliminate the All Periods selection that appears within the picklists
for the new parameters at runtime (the focus procedure in this, the first
half of our article). Then, in Part II of this article, we will begin
the construction of example intuitive defaults for the new report parameters,
to provide illustrations of ways our client colleagues might approach similar reporting
needs.
Procedure:
Removing the All Selection from Auto-Created Parameter Picklists in Reporting
Services 2005
We
noticed in our earlier trial run of the report (where our intent was to
verify the operation of our newly added Date parameters) that an All
Periods option appeared atop each picklist. While I have had myriad
clients state that they did not want this option to be available, the fact is
that Reporting Services is delivering exactly what is requested via the
datasets that were automatically created to support the newly added parameters.
The dataset created to support each parameter simply does not filter, by
default, the dimensional levels retrieved from the dataset and presented in the
runtime picklist, which, as we shall see, means that the All level is
retrieved among the members of the desired level.
The good
news is that provision has been made, within the dataset, to entrain the level
number as a data column. We can easily perform our own filtering via this
column, as we will do in the following steps.
We arrive
at the Data view, as we did earlier.
2.
Using the Dataset
selector on the left side of the Data tab toolbar, select the DateCalendarYear
dataset, as shown in Illustration
16.
Illustration 16: Select the
DateCalendarYear Dataset ...
The MDX
Query Builder opens, exposing the MDX that was created (to populate the Report
Parameter supporting dataset for the new parameter) as an activity in the
chain of events triggered by our addition of the parameterized filter
for DateCalendarYear in the Preparation section above. Lets
retrieve the dataset to examine its composition.
3.
Click the Execute
Query button atop the Data tab.
4.
Data populates the Data pane,
and appears similar to that depicted in Illustration 17.
Illustration 17: The
Retrieved Parameter Support Dataset
The
column upon which we will focus is the ParameterLevel column. This
column contains the values of a calculated member, [Measures].[ParameterLevel],
which represents the data retrieved from the AdventureWorks cube by the
following expression: [Date].[Calendar Year].CURRENTMEMBER.LEVEL.ORDINAL.
This expression simply generates a number for the dimensional level at which
the ParameterCaption (the label seen in the picklist by the
information consumer at runtime) and the ParameterValue (the
corresponding MDX value passed by Reporting Services as filter criteria
in generating the data selected in the report at runtime) belong.
NOTE: For more detail surrounding the MDX
.Ordinal function, see MDX
Numeric Functions: The .Ordinal Function. For information on the .Level
function, see various articles throughout my MDX
Essentials series (of which the .Ordinal
function article is also a member) at Database Journal.
To
eliminate the All Periods selection from our Year parameter
picklist, we have only to implant a dataset filter at this stage.
5.
Ensuring that
the DateCalendarYear dataset appears within the Dataset selector,
click the ellipses (...) button that appears to the selectors immediate
right, as shown in Illustration 18.
Illustration 18: Opening
the Dataset Dialog
The
multi-tabbed Dataset dialog for the DateCalendarYear dataset
opens.
6.
Click the Filters
tab.
7.
In the Filters
table on the tab, click the top row in the Expression column to activate
the downward-pointing selector.
8.
Select =Fields!ParameterLevel.Value
within the selector, as depicted
in Illustration 19.
Illustration 19: Selecting
the Parameter Level Value Expression ...
9.
Ensure that
the = sign occupies the Operator column to the immediate right
(it is populated by default when we select an entry for the Expression
column).
10.
Click the Value
column to the right of the Operator column for the same row, and type
the following into the box:
=1
The Filters
tab of the Dataset dialog appears, with our input, as shown in Illustration 20.
Illustration 20: The
Filters Tab of the Dataset Dialog with Our Input
11.
Click OK,
to accept our modifications, and to close the Dataset dialog.
The
dialog closes, and we return to the MDX Query Builder. At this point,
we should note that, should we execute the query once again, we will see no
difference in the dataset that is returned: the filter is not applied at this
level, although it is enforced when we run the report, as we shall see a few
steps below.
12.
Select the DateCalendarQuarterofYear
dataset within the dropdown Dataset selector.
13.
Click the Execute
Query button atop the Data tab to retrieve the dataset.
The Query
Parameters dialog appears. It is here that we provide a default value for
the Year parameter, to enable the query to execute. In effect, passage
of the Year parameter value, at execution time of the Quarter
dataset, is the mechanism behind the cascading nature of the parameter
picklists.
14.
In the table labeled
Specify details of the parameters defined in the query, click the second
row in the Default column (the row containing DateCalendarYear) to
activate the downward-pointing selector.
15.
Expand All
Periods in the selector picklist, and place a check in the box to the left
of CY 2003 within the selector, as depicted in Illustration 21.
Illustration 21: Select
a Default for the DateCalendarYear Parameter ...
16.
Click OK to
accept the new default.
17.
Click OK
on the Query Parameters dialog to save our input and to dismiss the
dialog.
Data
populates the Data pane, and appears similar to that shown in Illustration 22.
Illustration 22: The
DateCalendarQuarterofYear Dataset Appears ...
Here
again we see that the All level value is zero (0).
We will, therefore, filter the data supporting the corresponding parameter
picklist to Level 1, as we did for the DateCalendarYear dataset
earlier.
18.
Click the
ellipses (...) button that appears to the immediate right of the Dataset
selector, as we did earlier.
The Dataset
dialog for the DateCalendarYear dataset opens.
19.
Click the Filters
tab.
20.
In the Filters
table on the tab, click the top row in the Expression column to activate
the downward-pointing selector, once again.
21.
Select =Fields!ParameterLevel.Value
within the selector, as we did for the DateCalendarYear dataset earlier.
22.
Ensure that
the = sign occupies the Operator column to the immediate right,
once again.
23.
Click the Value
column to the right of the Operator column for the same row, and type
the following into the box:
=1
24.
Click OK,
to accept our modifications, and to close the Dataset dialog.
The
dialog closes, and we return to the MDX Query Builder, once again. We
are now ready to perform level filtering on the last of our date-related
datasets, DateMonthofYear.
25.
Select the DateMonthofYear
dataset within he dropdown Dataset selector.
26.
Click the Execute
Query button atop the Data tab to retrieve the dataset.
The Query
Parameters dialog appears, as it did for the DateCalendarQuarterofYear dataset earlier. We will provide a default
value for both the Month and Quarter parameters, at this point,
to enable the query to execute.
27.
In the table labeled
Specify details of the parameters defined in the query, click the second
row in the Default column (the row containing DateCalendarYear) to
activate the downward-pointing selector.
28.
Expand All
Periods in the selector picklist, and place a check in the box to the left
of CY 2003 within the selector, as we did earlier.
29.
Click OK to
accept the new default.
30.
Click the third
row in the Default column (the row containing DateCalendarQuarterofYear)
to activate the downward-pointing selector.
31.
Expand All
Periods in the selector picklist, and place a check in the box to the left
of CY Q3 within the selector, as we did earlier.
32.
Click OK to
accept the new default.
The Query
Parameters dialog appears, with our input, as that depicted in Illustration 23.
Illustration 23: The Query
Parameters Dialog for the DateMonthofYear Dataset with Our Input
33.
Click OK
on the Query Parameters dialog to save our input and to dismiss the
dialog.
Data
populates the Data pane, and appears similar to that shown in Illustration 24.
Illustration 24: The
DateMonthofYear Dataset Appears ...
Once
again we see that the All level value is zero (0).
We will filter the data supporting the corresponding parameter picklist
to Level 1, as we did for the DateCalendarYear and DateCalendarQuarterofYear
datasets earlier.
34.
Click the
ellipses (...) button that appears to the immediate right of the Dataset
selector, once again.
35.
Click the Filters
tab on the Dataset dialog for the DateMonthofYear dataset, which
opens next.
36.
In the Filters
table on the tab, click the top row in the Expression column to activate
the downward-pointing selector.
37.
Select =Fields!ParameterLevel.Value
within the selector, as we did for the DateCalendarYear and DateCalendarQuarterofYear
datasets earlier.
38.
Ensure that
the = sign occupies the Operator column to the immediate right,
as we did earlier.
39.
Click the Value
column to the right of the Operator column for the same row, and type
the following into the box, once again:
=1
Having
applied the filters required to the new parameters supporting datasets, we are
now ready to verify the effectiveness of our solution, and to ascertain the
removal of the All Periods option within the date-related parameters
picklists at runtime.
Verification:
Preview the Report and Inspect the Effectiveness of Our Solution
Lets
preview the report to inspect the results of our handiwork. (One of the
reasons that I chose hierarchical time for the dimensional example with
which we undertook our practice efforts is the unambiguous relationship between
parent and child level members. The idea was to both demonstrate the means of
eliminating the All selection within the parameter picklists,
while providing the dual benefit of demonstrating the cascading nature of the
picklists as a secondary advantage of taking the avenue we have taken.)
1.
Click the Preview
tab.
DBJ_OLAP_Report.rdl initializes, and the first prompt
(based upon the pre-existing ProductCategory parameter), becomes
enabled.
2.
Leave the Product
Category prompt selection at its default of Bikes, Components.
3.
Click the
downward pointing arrow on the right side of the Calendar Year selector.
4.
Select CY
2004 within the Calendar Year parameter picklist.
We
notice the All Periods selection that previously appeared atop the Year
parameters picklist has now disappeared. We also notice that, once we
make a choice within the Calendar Year dropdown selector, the next
parameter selector, Calendar Quarter, becomes enabled. The
cascading nature of the newly added, date-related parameter set becomes obvious
in this scenario, as the AdventureWorks cube contains data only through August,
and the third quarter (CY Q3) of 2004, a
fact that is reflected in the picklist of three Quarter selection
options, as we shall see next.
5.
Select CY Q2
from the Calendar Quarter picklist, as depicted in Illustration 25.
Illustration 25: Selection
Options Demonstrate Cascading Nature of the Date Parameters
6.
Select the
month of June in the Month parameter picklist, as shown in Illustration
26.
Illustration 26: Cascading
Nature of the Date Parameters Again in Evidence
We
notice again that the All Periods selections that previously appeared
atop the Quarter and Month parameters picklists have disappeared.
The cascading nature of the date-related parameters is also demonstrated again,
as we see that our selection of CY Q2 enables the picklist for
the Month parameter, which appropriately reflects only the three member Months
belonging to CY Q2 as selections, April, May and June.
7.
Click the View
Report button.
The
report executes quickly and returns the data for the selections we have made
within our parameter picklists, in a manner similar to that depicted in Illustration
27.
Illustration 27: The
Sample Report Operates as Expected with New Date Parameters
Our
verification process has demonstrated the effective removal of the All
selection (which appears by default when we chose to generate our parameter
picklists via the automatic creation mechanism that Reporting Services
offers) from our newly added picklists. Moreover, we have witnessed the
cascading nature of the new parameters that is established as a secondary
benefit of adding parameters in the manner we have chosen.
We have
verified that the parameter level filtering we have put into place
accomplishes the intended ends, and allows us to meet the expressed requirement
of the information consumers to remove the All selection. We have
also noted that the Reporting Services 2005 environment, with its
graphical design environment, supports easy and flexible design of cascading
parameters, in addition to allowing us to customize picklists via filtering
within the automatically created datasets.
We will
extend our examination of parameterization yet further in the second half of
this article, where we will generate a solution to meet the need of our
colleagues to provide intuitive parameter defaults to information consumers at
report runtime.
NOTE: Please consider saving the .rdl file we have created to this point
for use in the second half of this
article, so as to avoid the need to repeat the preparation process we have
undertaken above.
8.
Experiment
further with the report, if desired.
9.
When ready,
click the Layout tab.
10.
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 Part II of this
article.
11.
Select File
-> Exit to leave the design environment,
when ready.
Conclusion ...
In
this article, we continued the extended examination of Parameters in Reporting
Services 2005 that we began in Mastering OLAP Reports: Parameters for Analysis Services
Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services
Reporting, Pt. II. After discussing parameterization
in general, we defined a hypothetical scenario within the context of a
client business requirement: the need to remove the All selection that
appears by default within parameter picklists created automatically by Reporting
Services. We then moved into our hands-on practice session.
After
creating a clone of a sample OLAP report, containing a Matrix data
region, we ascertained
connectivity of its shared Analysis Services data source. We then made
structural
modifications to the report, to prepare for our practice exercise session with
three date-related parameters, whose additions to the report were
requested by our hypothetical client. We created, within the graphical
Design Mode of the MDX Query Builder, three filters for which parameterization
was enabled via the Filter pane setting.
In
conjunction with the creation of the parameterized filters, we inspected the automatically created
Report Parameters and their settings, as well as the subsequently
created datasets underlying the new Report Parameters. We then
focused upon the modification of the respective datasets to filter the All
parameter level from the data rows retrieved and presented within the parameter
picklists at runtime. Throughout the steps we undertook, we discussed how the various
components were tied together, and the potential challenges we face in
modifying these objects without consideration of the resulting dependencies.
Finally, we previewed
the report to observe the effectiveness of our solution in eliminating the All
selection in the picklists at runtime, as well as the added benefit of the
general cascading nature of the parameters at runtime.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.