About the Series ...
article is a member of the series MSSQL Server
Reporting Services. The series is designed to introduce MSSQL Server Reporting
Services (Reporting Services), 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.
OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Mastering
OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, we acknowledged a common enterprise reporting
requirement: the capability to filter reports at run time for specific
information. We noted that this need is typically managed via parameterization
(known in other enterprise reporting applications 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.
Automatically Created Parameter Support Objects Pt. I, we 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. We next focused upon the effective use of parameter
defaults, in Parameter
Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, in making reports that contain
parameters even more user-friendly and efficient at runtime.
subsequent article of this series, Support
Parameterization from Analysis Services, we extended our examination
of parameterization support yet further, continuing our work within the scenario we established within Customize Automatically Created Parameter Support Objects
Pt. I, again
with an objective of meeting the need of the hypothetical client to support parameterization, including picklists, within the report, but
with a significant difference: We exposed the steps involved in offering a
simple means of supporting our solution from within the Analysis Services
layer of the integrated Microsoft BI solution. In examining the support of parameterization from Analysis
Services, we created a new clone of an existing sample OLAP report,
containing a Matrix data region, to which we made structural modifications, to
prepare for our practice exercise session. We next discussed, and then implemented,
an approach, from within Analysis Services, to meet the need of our
hypothetical client to support Report Parameters from the Analysis
Services layer of the integrated Microsoft BI solution. Finally, we accessed and employed the new Analysis
Services parameter support components from within Reporting Services,
reviewing how the various components of the parameter support solution
we proposed were tied together.
in our most recent article prior to this one, Support Parameterization from Analysis
Services Cascading Picklists, we continued to
work with the basic OLAP report we created in Support Parameterization from Analysis
Services. Our primary focus was to perform a
refinement from the perspective of the support we provided from the Analysis
Services layer of our integrated BI solution. We
established cascading picklists within the report we created in Support Parameterization from Analysis Services (just as we will undertake the
addition of support for intuitive parameter defaults at runtime in this
article), for greater user-friendliness and overall operational efficiencies. As a part of our examination of supporting cascading
picklists, we discussed the utility of establishing cascading parameters
within a report to make it more user-friendly and effective, and then we made modifications to
the datasets underlying our report parameters to incorporate cascading
this article, we will take up another refinement to our parameters that
we will, once again, support via objects that we create within Analysis
Services. We will thereby meet a business requirement to generate parameter
selection defaults at runtime within the same OLAP report for which we have
established cascading parameters supported within the Analysis
Services layer. In
pursuing this refinement, we will:
sample Report Server project, AdventureWorks Sample Reports, and
access the existing sample report we prepared in Support Parameterization from Analysis Services.
utility of establishing default parameters within a report to make it
more user-friendly and effective.
implement an approach, from within Analysis Services, to meet the need
of our hypothetical client to present parameter picklist defaults that
represent the last period of data entry in our cube.
Create a new dataset
within our report to incorporate parameter default support.
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.
report to verify the effectiveness of our solution in runtime action.
Defaults from Analysis Services
Objective and Business Scenario
As any regular reader of this series is aware, parameterization
can be implemented 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.
OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Mastering OLAP Reports: Parameters
for Analysis Services Reporting, Pt. II, we noted that 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), that we need to
quickly add basic parameterized filters to our reports. We discussed the
fact that the automatically created objects serve us well with minimal
modification in many cases; we emphasized, however, the frequent need to
tweak the components supporting parameterization, often a bit beyond mere
cosmetics, to obtain more sophisticated features. In two subsequent articles
of our series, Reporting Services:
Customize Automatically Created Parameter Support Objects and Parameter Support
Objects, Pt II: Support OLAP Parameter Defaults with Datasets, we outlined some of the
customization needs that might arise, and got some hands-on exposure to the
steps required to make the modifications involved.
As we mentioned within
these two articles, as well as within many others throughout my various Database Journal series, we might offer solutions to meeting reporting
requirements that are completely contained within the reporting layer of
the integrated Microsoft business intelligence solution, but that other options
exist at the Analysis Services and MSSQL Server RDBMS levels. An
important consideration during the design phase of any implementation effort, as
we have mentioned repeatedly, 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) within other layers of the solution. For example, I have often
supported parameter picklists with support objects I have created within
the Analysis Services database that serves as a data source for the
report(s) under consideration. As I have emphasized in many other of my articles, one of the
numerous advantages of placing the intelligence into layers outside Reporting
Services lies within the fact that we can thus avoid adding the
intelligence to support our needs within every individual report where it is
useful; it also means a central point of maintenance, the enforcement of
consistent application of the logic and / or business rules across multiple
reports, and other benefits.
with a new basic OLAP report in Support
Parameterization from Analysis Services, we began with an objective
of meeting the need of a hypothetical client to support parameterization,
including picklists, within the report. In that article we exposed the
steps involved in offering a simple means of supporting our solution from
within the Analysis Services layer of the integrated Microsoft BI
solution, and then accessed and employed the new Analysis
Services parameter support components from within Reporting Services.
NOTE: 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.
Having created, in Support Parameterization from Analysis
basic sample OLAP report containing a matrix data region, to which we
added simple filter / parameter combinations (based upon
structures we created within Analysis Services) and having previewed the
effectiveness of our solution for rudimentary runtime parameterization,
we next delved into the modifications we needed to make to meet additional
business requirements of our hypothetical client, the AdventureWorks
organization. In Support Parameterization from Analysis
Services Cascading Picklists we described a scenario where the same client team had made an additional request for our support in
accomplishing its objectives. Our client colleagues informed us that
information consumers had asked that they refine the report we created together
Parameterization from Analysis Services to make the existing parameterization even more user-friendly
by providing cascading behavior. We
described just what we meant by cascading (that is, the set of values of one parameter
depends upon the value chosen in another, typically previous, parameter),
confirmed our understanding
of the need with the client representatives, and constructed a working example
of a way to provide the cascading picklists that our colleagues had
requested for the new report parameters an example, we proposed, that could
be modified to drive different picklist behavior, should the consumers desire
further refinements after road testing the initial enhancements.
In this 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. We will again
reopen the basic sample OLAP report containing a matrix data region,
with which we have worked in the immediately previous articles of this series,
to which we will focus upon adding parameterization support to meet
additional business requirements of our hypothetical client. The requirements
will be largely identical to those which we met completely within the Reporting
Layer in Parameter
Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets.
To detail the
specifics, the client team has made a request for our further support in
accomplishing its reporting objectives. Our client colleagues inform us that
information consumers have asked that they refine an existing report 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 (specifically, they tell us, from the
perspective of Internet Sales Amount 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 specify their own individual needs.
While this is
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,
examples of which, again, include the procedures we took within Parameter Support Objects, Pt II:
Support OLAP Parameter Defaults with Datasets, where
we demonstrated an option that is completely contained within Reporting
Services. In this article we will expose a means for supporting dynamic
parameter defaults from within the Analysis Services layer of the
integrated BI solution.
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, supported from within
components that we add into the Analysis Services layer examples, we
add, that can be modified to specify different defaults, should the
consumers request them after road testing the initial labels, and so forth.
Preparation: Locate and Open the Report Project and Report Created in Pt I
part of preparation for our practice session in Support Parameterization from Analysis Services, 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
discussed, and then implemented, an approach to meet the need of our client to
support Report Parameters from the Analysis Services layer of the
integrated Microsoft BI solution. We then accessed and employed the new Analysis Services
parameter support components from within Reporting Services, examining
how the various components of the parameter support solution we proposed
are tied together to support our runtime parameters.
In the immediately following
article, Support Parameterization from Analysis Services Cascading
Picklists (the one just preceding this article), we resumed
where we had left off at the end of Support
Parameterization from Analysis Services, modifying our newly created Report
Parameters further to support the cascading behavior requested
by the client, as described in the section above.
In this practice session, we will
resume where we left off at the end of Support
Parameterization from Analysis Services Cascading Picklists, modifying our newly created Report
Parameters further to support the 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 Support Parameterization from Analysis Services.
Reopen SQL Server Business Intelligence
Close the Start
page, if desired.
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 Support Parameterization from Analysis Services.
report with which we conducted our practice session in Support Parameterization from Analysis Services,
which we named DBJ_OLAP_Report, by double-clicking the report
within the Solution Explorer.
opens in Layout
view, as depicted in Illustration 1.
Illustration 1: Our
Sample Report in Layout View
If the Analysis
Services database is already added to the project in your environment
because you previously left it there, then you can skip the next section.
Preparation: Add the Analysis Services Database to the Project
necessary, we will continue our preparation by adding the Analysis Services
database within our
newly reopened project in the Business Intelligence Development Studio.
As I note in many of my articles that involve multiple layers of the integrated
Microsoft BI solution, I typically like to set up a lab environment for each of
my client or research projects where I have both the respective UDM and
reports involved with the engagement within an integrated solution in Visual
Studio. This ensures ease in testing cube modifications through to the report
layer from a single, central location, as well as providing the advantage
of effective source control, among numerous other conveniences. For example,
in this particular case, I will have both a copy of the sample Adventure
Works DW and the AdventureWorks Sample Reports projects added into a
single solution within the Business Intelligence Development Studio,
where I can access all member objects from one point, the Solution Explorer.
If the Analysis
Services Database needs to be added to your project, continue with the
following steps in the Business Intelligence Development Studio:
-> Open from the main menu.
Services Database ... from the cascading menu, as shown in Illustration 2.
Selecting an Analysis Services Database into the Project ...
to Database dialog appears.
the radio button to the immediate left of Connect to existing database
(atop the dialog) is selected.
appropriate name within the Server input box.
appropriate name within the Database selector (the Analysis Services
database with which we have established connectivity of our report clone above
Adventure Works DW), just underneath the Server input box.
radio button to the immediate left of Add To Solution (in the bottom
section of the dialog), to select this option.
The Connect to Database dialog appears similar to that
depicted in Illustration 3.
Illustration 3: The
Connect to Database Dialog, with Our Input
Click OK to
accept our input, and to dismiss the dialog.
The Reading database from the server... message box appears briefly, as
shown in Illustration 4.
4: Reading the Database from the Server ...
Works DW Analysis Services project opens, and we see the various
associated objects appear within Solution Explorer, as depicted in Illustration
5 (with Dimensions folder collapsed to conserve space).
5: The Adventure Works DW Analysis Services Project Joins the Solution ...
now access our sample report and its underlying Analysis Services
database, and thus test cube enhancements through to the report layer, from a
single, central development environment.