Join BI Architect Bill Pearson as he
continues his examination of parameterization within Analysis Services
reports. In this article, we provide a solution for supporting parameter
defaults that represent the last periods of data entry within our cube.
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.
In the first half of this article,
Customize
Automatically Created Parameter Support Objects Pt. I, we
reviewed type-in and picklist parameters in general, and then
concentrated our focus, once again, upon picklist parameters, which we noted
to be a frequent choice among information consumers for user-friendly operation
within reports. Picklist parameters add value in several ways,
including the inherent elimination of typing errors, as well as the enforcement
of standard selections for report execution. We noted that the design of the picklist
is important in making runtime selections easy, and that picklists that
present long scrolling processes or other cumbersome methods can negatively
affect consumer perceptions, particularly when they are components within
commonly requested reports.
When working with clients to
design and create virtually any enterprise report, I ask questions about the
reports objectives and intended appearance; moreover, I ask questions about
source data, filtering, and other considerations involved with the information
retrieved by the underlying datasets. When I reach the point at which we
discuss parameterization (the vast majority of reports contain parameters),
I virtually always follow up with questions about desired parameter defaults.
Parameter defaults are important to consider for a couple
of primary reasons. First, and certainly the most basic, is that the absence
of parameter defaults within a report results in one of two things at
runtime: either all sits idle until the information consumer makes parameter
selections and executes the reports, or a blank report is generated (assuming
the Report Parameters dialogs involved allow blank values within
the parameter input / selector boxes). While neither of these
conditions presents an issue to consumers understanding underlying settings,
each can still mean inconvenience in general and perhaps confusion to less
experienced users.
The second design consideration of
importance that surrounds Parameter defaults goes beyond their simple
absence or presence, and focuses more upon their intuitiveness. By
intuitive, I mean dynamically intelligent. While it is quite desirable to
select among numerous criteria at runtime, we can add finesse to the selections
we offer by having the more likely desired selections, considering the nature
of the report and its typical usage by information consumers, appear as defaults
at runtime. The obvious objective is to make the steps involved in report
execution more efficient, for most of the consumers, most of the time.
In this article, we will continue working with the basic OLAP
report we created in the first half of this article, Customize
Automatically Created Parameter Support Objects Pt. 1. We will establish a scenario within which we expose the steps
involved in meeting a basic need of a hypothetical client in adding runtime default
selections that will appear within the parameter picklists of the
report. In examining
the requested addition of parameter defaults within an OLAP report
containing a Matrix data region, we will:
-
Reopen the
sample Report Server project, AdventureWorks Sample Reports, and
access the existing sample report we prepared in Pt. I.
-
Discuss
options for supporting intelligent parameter picklist defaults among the
three primary layers of the integrated Microsoft business intelligence
solution.
-
Discuss an
approach to meeting the need of our hypothetical client to present parameter
picklist defaults that represent the last period of data entry in
our cube.
-
Create a dataset
to provide parameter default support in the Reporting Services
layer of the clients BI solution.
-
Overview how
the various components of the default support solution we propose are
tied together, as a part of a hands-on practice session where we create and
align the necessary components to support our parameter defaults.
-
Preview the
report to observe the effectiveness of our solution in runtime action.
Support OLAP Parameter Defaults Using Datasets
Objective and Business Scenario
As I have stated within numerous articles of this series, I have implemented parameters in many
ways to fit 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. Our practice session within this
article contains an example of the sort of customization to which I refer.
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 parameter picklists 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.
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.
Reporting
Services offers a
robust Report Parameter interface that is exposed through the Report
Designer, through its Web service interface, through the Report Manager,
and via the reports we schedule. Many of the reports we author for our
employers or clients depend upon time / date parameters that are based upon the
current date in some regard. As an illustration, a sales report might
typically be executed at multiple period cutoffs (weekly, monthly, quarterly,
annually, and so forth) to present data from the previous period. Because Reporting
Services supports dynamic default parameters, as we shall see, the tasks
involved in providing information consumers with user-friendly, intuitive
parameters, by which timely, relevant results can be retrieved become far
easier for knowledgeable authors.
In this, the second
half of a two-part article, we will continue our extended examination of Parameters in Reporting
Services, concentrating upon another specific refinement we can make to our
parameters to fine tune them to local business environments. Having created
a basic sample OLAP report containing a matrix data region, to which we
added simple filter / parameter combinations, in Pt. I, and having reviewed, 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 next delve into
the modifications we need to make to meet additional business requirements of a
hypothetical client, the AdventureWorks organization.
To detail the
specifics, the same client team with which we consulted in Pt. I, the Adventure Works
Customer Care department, has made an additional request for our support in
accomplishing its objectives. Our client colleagues inform us that information
consumers have asked that they refine the report we created together in Pt. I to
make the existing parameterization even more user-friendly by providing
intuitive parameter picklist defaults. They tell us that, because the
vast majority of information consumers accessing this report seek to return
results based upon the most recent period (including Year, Quarter,
and Month) for which our cube contains data, parameter defaults
reflecting these most recent periods would mean more rapid report execution for
most users, while still allowing consumers with different requirements the
capability of specifying their own specific needs.
While this is a
relatively common desire on the part of information consumers, there are, as I
have implied, multiple ways to approach the support of intelligent parameter
defaults within the integrated Microsoft business intelligence solution. In
this article we will demonstrate a means for a accomplishing our ends that is
completely contained within Reporting Services. But it is important to
remember that we might also add objects to support dynamic parameter
defaults within the Analysis Services layer (via calculated
members / named sets, etc.) or even from the relational layer (where
we might support the needs via tables constructed for that purpose, User
Defined Functions (UDFs), or via other mechanisms). One of the
numerous advantages of placing the intelligence into layers outside Reporting
Services lies within the fact that we would thus avoid adding the
intelligence to support our needs within every report where it is useful; it
also means a central point of maintenance, the enforcement of consistent
application of the logic across multiple reports, and other benefits. (We
provide demonstrations of these approaches within other articles of this series.)
Because our client
colleagues tell us that they want a solution which is contained completely
within Reporting Services, we decide to take a relatively
straightforward path, the creation of a dataset within our sample
report, from which we will populate the targeted Report Parameter default
settings. The client team requests a brief overview of the MDX involved in
the creation of such a dataset, as well as help in understanding the
relationships between the components involved in the overall solution, to allow
for ongoing maintenance, as well is for the extrapolation of the concepts to
meet other reporting needs.
NOTE: There are, of course, multiple
other options for producing the necessary default values, even from
within the Reporting Services layer. For instance, we might have
generated the default values in each of the respective, already existing
parameter support datasets. Moreover, we might have used calculated
fields, or even embedded code, within Reporting Services to
accomplish similar ends.
We
confirm our understanding of the foregoing needs, and propose to construct
working examples of a way 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
Preparation: Locate and Open the Report Project and Report Created in Pt I
As a
part of preparation for our practice session in Pt. I, we created a
clone of a sample OLAP report, containing a matrix data region, for
which 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 parameter picklists, as well as the added benefit of
the general cascading nature of the parameters at runtime.
In this half of the practice
session, we will resume where we left off at the end of Pt. I, modifying our newly created Report
Parameters further to support the dynamic parameter picklist defaults
requested by the client, as described in the section above. We will perform
the preparation and practice steps which follow within the SQL Server
Business Intelligence Development Studio, as we did within Pt. I.
1.
Reopen SQL Server Business Intelligence
Development Studio,
as appropriate.
2.
Close the Start
page, if desired.
3.
Reopen the AdventureWorks
Sample Reports project, which contains the sample reports that ship with
the Reporting Services component of the MSSQL Server 2005 suite,
as well as the clone report we created in Pt. I.
4.
Reopen the
report with which we conducted our practice session in Pt. I, DBJ_OLAP_Report, by double-clicking the report
within the Solution Explorer.
DBJ_OLAP_Report
opens in Layout
view, as shown in Illustration 1.
Illustration 1: Our
Sample Report in Layout View
We are
now ready to begin working with the existing report to add support for dynamic
parameter picklist defaults.
Practice
Having
already created a clone report containing date-related parameters, we
can go directly to create a dataset that we can use, in much the same
manner as we used those automatically created to support our Report
Parameters (see Pt. I for
an in-depth examination of the relevant objects), this time to support the default
fields within each of the associated Report Parameter settings.
Procedure: Create a Dataset to Support Dynamic Parameter Picklist Defaults at Runtime
Recall
that we noted, within our earlier discussion, that there are multiple ways to
support dynamic
parameter defaults
within Reporting Services, as well as within other layers of the
integrated Microsoft business intelligence solution. Our client colleagues
have requested that we deliver a means of supporting defaults for the
time parameters that reflect the
most recent
period (including Year, Quarter, and Month) for which
our cube contains data.
One of
the many versatile features of Reporting Services lies within its support of expressions throughout
the various objects with which it accomplishes its mission. We can easily
exploit this utility by creating a dataset, based upon an MDX query that
we construct, to retrieve the Year, Quarter, and Month labels
and values identifying the most recent of each of these periods for which
data exists within our cube. We will obtain some practical experience with
this within the following steps.
We
arrive at the Data view.
2.
Using the Dataset
selector on the left side of the Data tab toolbar, select the <New
Dataset> selection at the bottom of the selection, as depicted in Illustration 2.
Illustration 2: Select <New
Dataset> ...
The Dataset
dialog opens with the cursor defaulted at the Name input box.
3.
Type the following
into the Name input box:
DefaultDateSupport
The
Dataset dialog, with our input, appears as shown in Illustration
3.
Illustration 3: Dataset
Dialog with Our Input ...
4.
Click OK to
accept our input, and to enter the MDX Query Designer in Design
Mode.
5.
Switch to Query
Mode by clicking the Design Mode toggle button on the toolbar, as
depicted in Illustration 4.
Illustration 4: Switch
to Query Mode ...
6.
Replacing the
code snippet that appears by default, type (or cut and paste) the following
into the Query pane:
WITH
SET
[Last Period Sales]
AS
'TAIL(
FILTER(
[Date].[Calendar].[Month].MEMBERS,
NOT ISEMPTY ([Measures].[Internet Sales Amount])
),
1
)'
MEMBER
[Measures].[YearParamDefaultCaption]
AS
'[Date].[Calendar Year].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER
[Measures].[YearParamDefaultValue]
AS
'[Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME'
MEMBER
[Measures].[QtrParamDefaultCaption]
AS
'[Date].[Calendar Quarter of Year].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER
[Measures].[QtrParamDefaultValue]
AS
'[Date].[Calendar Quarter of Year].CURRENTMEMBER.UNIQUENAME'
style="color: green; background: transparent;"MEMBER
[Measures].[MonthParamDefaultCaption]
AS
'[Date].[Month of Year].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER
[Measures].[MonthParamDefaultValue]
AS
'[Date].[Month of Year].CURRENTMEMBER.UNIQUENAME'
SELECT
{[Measures].[YearParamDefaultCaption], [Measures].[YearParamDefaultValue],
[Measures].[QtrParamDefaultCaption], [Measures].[QtrParamDefaultValue],
[Measures].[MonthParamDefaultCaption], [Measures].[MonthParamDefaultValue]}
ON AXIS(0),
[Last Period Sales] ON AXIS(1)
FROM
[Adventure Works]
The
Query pane, with our input, appears as shown in Illustration 5.
Illustration 5: Our
Query within the Query Pane
Our
query accomplishes several things. First, it creates a named set (Last
Period Sales), via the WITH SET keywords, within which is the
logic to determine the most recent period (including Year, Quarter,
and Month) for which our cube contains data.
WITH
SET
[Last Period Sales]
AS
'TAIL(
FILTER(
[Date].[Calendar].[Month].MEMBERS,
NOT ISEMPTY ([Measures].[Internet Sales Amount])
),
1
)'
The
expression that defines Last Period Sales leverages the MDX Tail()
function in conjunction with the Filter() function, along with NOT
ISEMPTY(), to determine the latest (in time) Calendar Month in the
cube containing data. (Because we are retrieving data at the month level, the
corresponding row set returned by Reporting Services will also include,
as we shall see, higher levels within the Date dimension (Calendar
hierarchy) - a characteristic of Reporting Services that produces a
dataset ideal for many purposes.
Many will recognize the WITH MEMBER keywords as representing
the creation of various calculated members, which we also specify within
our SELECT clause for the purposes of supporting our parameter
defaults. These work in a manner that is very similar to the calculated
members that appeared within the automatically generated dataset queries
that we examined in Pt. I. They create the following six data columns within the returned
dataset:
-
YearParamDefaultCaption
-
YearParamDefaultValue
-
QtrParamDefaultCaption
-
QtrParamDefaultValue
-
MonthParamDefaultCaption
-
MonthParamDefaultValue
The default caption columns will not be used
within our parameter definitions, but they represent a possibly useful
label that I would typically add into my design of datasets of
this sort - mostly for
use in labeling within the report, where it might be helpful to list our parameters
so that secondary information consumers are made aware of the filters
we have placed upon the data when looking at, say, a printed or exported
version of the report. The caption version of the default value,
(the value itself represents the actual qualified names that we
pass as a filter to Analysis Services at runtime) would perhaps serve as
a more understandable form of the values when used in this manner. We will see
how the default values are used within the Report Parameters in
our next steps.
Let's test our query
for general operability at this stage, where it's convenient to do so, before
going forward.
7.
Click the Execute
Query button within the toolbar of the Data tab, as depicted in Illustration
6.
Illustration 6: Execute
the Query ...
Data
populates the Data pane, and appears similar to that partially shown in Illustration 7.
Illustration 7: The
Retrieved Parameter Defaults Support Dataset (Partial View)
Procedure:
Align the Value Columns within the Parameter Default Support Dataset to the
Respective Default Values Settings within the Report Parameters Definitions
Having
created a dataset to feed the parameter defaults, we will next go
to the Report Parameters dialog to make the necessary alignments to
entrain the individual default value data fields to the perspective default
value selectors.
1.
Click the Layout
tab to transit to Layout view.
2.
Select Report
-> Report Parameters from the main menu.
3.
Select DateCalendarYear
within the Parameters list of the Report Parameters dialog that
appears next.
4.
Within the Default
values section in the lower third of the dialog, ensure that the From
query radio button is selected.
5.
Within the top
of the two settings that appear within the Default values section, Dataset,
select the new DefaultDateSupport dataset.
6.
Within the Value
field selector just underneath the Dataset selector, select YearParamDefaultValue.
The Report
Parameters dialog appears, with our adjustments highlighted, as depicted in
Illustration 8.
Illustration 8: Our
Settings for the DateCalendarYear Report Parameter
We will next
follow the same steps for each of the Quarter and Month Report
Parameters.
7.
Select DateCalendarQuarterofYear
within the Parameters list of the Report Parameters dialog.
8.
Within the Default
values section in the lower third of the dialog, ensure that the From
query radio button is selected.
9.
Within the top
of the two settings that appear within the Default values section, Dataset,
select the new DefaultDateSupport dataset.
10.
Within the Value
field selector just underneath the Dataset selector, select QtrParamDefaultValue.
The Report
Parameters dialog appears, with our modifications, as shown in Illustration
9.
Illustration 9: Our
Settings for the DateCalendarQuarterofYear Report Parameter
All that
remain are the same steps for Month Report Parameter.
11.
Select DateMonthofYear
within the Parameters list of the Report Parameters dialog.
12.
Within the Default
values section in the lower third of the dialog, ensure that the From
query radio button is selected.
13.
Within the top
of the two settings that appear within the Default value section, Dataset,
select the new DefaultDateSupport dataset.
14.
Within the Value
field selector just underneath the Dataset selector, select MonthParamDefaultValue.
The Report
Parameters dialog appears, with our adjustments, as depicted in Illustration
10.
Illustration 10: Our
Settings for the DateMonthofYear Report Parameter
We are
now ready to leave the Report Parameters dialog.
15.
Click OK to
accept our settings, and to dismiss the Report Parameters dialog.
Verification: Preview the Report and Inspect the Effectiveness of Our Solution
Lets
preview the report to inspect the results of our handiwork.
1.
Click the Preview
tab.
DBJ_OLAP_Report.rdl initializes, and the prompts (including
the pre-existing ProductCategory parameter), become enabled.
2.
Select All
Products among the multiple options in the Product Category dropdown
selector, as shown in Illustration 11.
Illustration 11: Select
All Products at the First Prompt ...
We see
that the Calendar Year, Calendar Quarter and Month are
populated with CY 2004, CY Q3, and July, respectively, as
depicted in Illustration 12.
Illustration 12: Defaults
Appear within the Time-related Parameter Picklists
These
are, in fact, the last member of each Date dimension (Calendar
hierarchy) level containing data in the sample cube.
3.
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 shown in Illustration
13.
Illustration 13: The
Sample Report Operates as Expected with New Date Parameter Defaults
Our
verification process has demonstrated the effectiveness of our solution. (As a
further test, we can select August (the last Month option) within
the Month selector and re-run the report. No results are returned.) Our client colleagues express
satisfaction with, and understanding of, the steps we have taken and the
results we have obtained, and state that they will be able to extend the
concepts to other reports. We have also noted that the Reporting Services
2005 environment, along with the integrated Microsoft business intelligence
solution as a whole, supports flexible design of parameter picklist default
support at multiple places within each of the database, Analysis
Services, and Reporting Services layers.
We
will extend our examination of parameterization yet further in subsequent
articles.
4.
Experiment
further with the report, if desired.
5.
When ready,
click the Layout tab.
6.
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, if useful.
7.
Select File
-> Exit to leave the design environment,
when ready.
Conclusion ...
In
this article, we continued the extended examination of Parameters in Reporting
Services that we began in Mastering
OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, Mastering
OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, and which we continued in the first half of this article, Customize
Automatically Created Parameter Support Objects Pt. I. Continuing to work with the basic OLAP report we created in Pt. I, we established a scenario within which we exposed the steps
involved in meeting a basic need of a hypothetical client in adding runtime defaults
that appear within the parameter picklists of the report.
In
examining the requested addition of parameter defaults within an OLAP
report containing a matrix data region, we reopened the sample Report Server project, AdventureWorks
Sample Reports, and accessed the existing sample report we prepared in Pt. I. We then touched upon options for supporting intelligent parameter picklist
defaults among the three primary layers of the integrated Microsoft
business intelligence solution, before focusing upon an approach to meeting the need of
our hypothetical client to present parameter picklist defaults representing
the last periods of data entry within our cube.
We created
a dataset to provide parameter default support in the Reporting
Services layer of the clients BI solution, and then overviewed how the various components of the parameter
default support solution we proposed were tied together, as a part of a hands-on
practice session where we created and aligned the necessary components to
support our parameter defaults. Finally, we previewed the sample report to
observe the effectiveness of our solution in runtime action.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.