Support Parameterization from Analysis Services – Parameter Defaults

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

In Mastering
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.

In Customize
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.

In a
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.

Finally,
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
picklist
support.

In
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
Service
s layer. In
pursuing this refinement, we will:

  • Reopen the
    sample Report Server project, AdventureWorks Sample Reports, and
    access the existing sample report we prepared in Support Parameterization from Analysis Services.
  • Discuss the
    utility of establishing default parameters within a report to make it
    more user-friendly and effective.
  • Discuss and
    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.
  • 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 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.
In Mastering
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.

Working
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
Services
, a
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
in Support
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

Preparation: Locate and Open the Report Project and Report Created in Pt I

As a
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
pane setting.

We
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.

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 Support Parameterization from Analysis Services.

4. 
Reopen the
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.

DBJ_OLAP_Report
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

As is
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:

1. 
Select File
> Open from the main menu.

2. 
Click Analysis
Services Database …
from the cascading menu, as shown in Illustration 2.



Illustration 2:
Selecting an Analysis Services Database into the Project …

The Connect
to Database
dialog appears.

3. 
Ensure that
the radio button to the immediate left of Connect to existing database
(atop the dialog) is selected.

4. 
Type the
appropriate name within the Server input box.

5. 
Select the
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.

6. 
Click the
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

7. 
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.



Illustration
4: Reading the Database from the Server …

The Adventure
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).



Illustration
5: The Adventure Works DW Analysis Services Project Joins the Solution …

We can
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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles