Reporting Services: Customize Automatically Created Parameter Support Objects

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

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