Architect Bill Pearson explores an
approach for supporting MDX picklists in Reporting Services: A table in a
relational database to provide centralized, one-stop maintenance of filtering
and row axis definition in the reporting layer.
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"), with the objective of presenting an overview of its features,
together with many tips and techniques for real-world use. For more
information on the series, as well as the hardware / software requirements
to prepare for the exercises we will undertake, please see my initial Database Journal article, A New
Paradigm for Enterprise Reporting.
have stated since the charter article of the series, published about the time
Reporting Services was first publicly released, my conviction is that Reporting
Services will commoditize business intelligence, particularly in its role as a presentation
component within an integrated Microsoft BI solution. Having been impressed
from my first exposure to this exciting application, when it was in early beta,
my certainty in its destiny grows stronger by the day, as I convert formerly
dominant enterprise business intelligence systems, such as Cognos, Business
Objects, Crystal, and others, to the Reporting Services architecture. I receive
constant requests to conduct strategy sessions about these conversions with
large organizations in a diverse range of industries the interest grows daily
as awareness of the solution becomes pervasive. Indeed, the five- to six-plus
figures that many can shave from their annual IT budgets represent a compelling
sweetener to examining this incredible toolset.
assumptions underlying the series are that you have correctly installed
Reporting Services, including current service packs, along with the
applications upon which Reporting Services relies, and that you have access and
the other rights / privileges required to complete the steps we undertake in my
articles. For details on the specifics of the adjustments necessary to quickly
allow full freedom to complete the exercises in this and subsequent articles,
as well as important assumptions regarding rights and privileges in general, please
see earlier articles in the series, as well as the Reporting Services Books
About the Mastering OLAP Reporting Articles...
have noted in many articles and presentations, one of the first things that
becomes clear to "early adopters" of Reporting Services is that the "knowledgebase"
for OLAP reporting with this tool is, to say the least, sparse. (I
recently heard an internal "reporting guru" say, during a BI strategy
session with a major soft drink manufacturer in Atlanta, that "we didn't evaluate Reporting Services because it
doesn't do cubes ...") As most of us are aware, minimal, if any, attention
is given to using Analysis Services cubes as data sources for reports in the
handful of books that have been published on Reporting Services to date. All are
written from the perspective of relational reporting, as if with existing
popular tools for that purpose. One Reporting Services book discusses OLAP
reporting with Reporting Services, and then performs illustrative exercises
with Office Web Components (OWC), instead. Another depicts an MDX snippet at
the end of the book, as if as an afterthought. All of the early books focus
entirely on relational reporting, and most make heavy use, typically enough, of
the Books Online and other scraps of documentation that we already have
anyway. (I could go on, but my overall opinion of the technical book industry
is already well known.)
stated in my article, Mastering
OLAP Reporting: Cascading Prompts, the purpose of the Mastering OLAP Reporting
subset of my Reporting Services series is to focus on techniques for using Reporting Services
for OLAP reporting. In
many cases, which I try to outline in my articles at appropriate junctures, the
functionality of the reporting solutions of well-established, but expensive,
solutions, such as Cognos PowerPlay, can be met in most respects by
Reporting Services at a tiny fraction of the cost. The vacuum of
documentation in this arena, to date, represents a serious "undersell"
of Reporting Services from an OLAP reporting perspective. I hope to contribute
to making this arena more accessible to everyone, and to share my
implementation and conversion experiences as the series evolves. In the
meantime, rest assured that the OLAP potential in Reporting Services will be
yet another reason that the application "commoditizes" Business
more information about the Mastering OLAP Reporting articles, see
the section entitled "About the Mastering OLAP Reporting
Articles" in my
article Ad Hoc
TopCount and BottomCount Parameters.
this and other of my series, we have examined parameterization and parameter
picklist support. While my focus has often been support of picklists using
datasets generated through MDX queries against the cube under consideration, I
have often found myself in client engagement scenarios where differing
reporting requirements, as well as various "exceptions," drive a need
to extend picklist support beyond the capabilities of the basic MDX queries
that we have examined. (For step-by-step exercises surrounding the creation
and use of cube-based picklists, see Create
a Cube-Based Hierarchical Picklist in my Database Journal MDX in Analysis Services series).
there are many avenues to meeting the business requirements we encounter within
the integrated Microsoft business intelligence solution, I have found that the
combination of MSSQL Server, Analysis Services and Reporting
Services offers us a great deal of flexibility, with regard to "where
we can install intelligence" in the reporting system. One of those
options, in the case of parameter picklists, is likely to be obvious to
most of us: using a table in the relational database to house the actual MDX
that we pass into our primary OLAP Dataset queries. This option,
combined with the fact that we can combine both OLAP and relational data sources
within a Reporting Services report, provides yet another example of the
powerful features that accrue to an organization that implements the integrated
solution for enterprise reporting.
explore a simply constructed, relationally based solution for OLAP picklist
support in this article. While the intent is to convey the possibilities,
rather than to offer a "one size fits all" solution, we will discuss logical
extensions that might leverage our approach even more, by allowing us to
customize the approach to the scenarios we encounter in the business world.
In this article,
business needs within the OLAP reporting environment of a hypothetical client;
use of relational picklist support as a possible solution for the expressed business
needs, highlighting advantages in this approach;
sample layout for a table to support parameter picklists in our OLAP reports;
the manner for "connecting" the picklist support to the report via
the Report Parameters dialog.
Relationally-Based Picklists for OLAP Reporting
Introduction and Business Scenario
explored in my articles String
Functions: The .Name Function and String
Functions: The .UniqueName Function, both members of the Database
series, .Name and .UniqueName provide us with an ideal pair of
identifiers to populate a Report Parameter dialog in Reporting Services
for an OLAP report. It becomes evident, as we saw in String Functions: The .UniqueName
Function, that, when used in an MDX query together, the two
functions allow us to produce a list of Names and Unique
Names that works wonderfully to populate report picklists. The
dual column list provides the standard, easily understandable names from which
the consumer selects, while supplying the MDX equivalent for the associated
choice for passage by Reporting Services to the
underlying Dataset query or elsewhere within the report
structure. The result, as we have noted, is cube-based picklist support,
thanks to the MDX .Name and .UniqueName functions.
We will begin our examination of this approach to meeting
business needs within
our usual context of a business scenario. We will assume that we have received
a request from a group of information consumers in the Finance Department
of the FoodMart organization. The request is for support in making an
existing report, currently used for one purpose, fit varying needs that arise
for information contained within the Sales sample cube. Initial
discussions make it clear that we can convert the existing report to a multi-purpose
report that can be shared beyond the current immediate circle of users.
consumers begin to discuss the specifics, focusing first on the Product
Family report parameter, which currently allows selection of one of the
three families at run time, as depicted in Illustration 1.
Illustration 1: The
Current Product Family Parameter Picklist
consumers tell us that that existing Product Family parameter selection
is too limited in a couple of ways: First, they would like to be able to
select "all" Product Families in some cases, instead of being
able only to choose between the families, each time they run the report.
Second, they wish to extend parameterization to lower levels within the Product
dimension hierarchy (Product Department, Product Category, and
beyond). Within all new levels, they want to replicate the "all"
selection capability that they have requested for the Product Family selection
additional nuance relating to the Product Family parameter, the
consumers make one further request: They tell us that based upon a recurring
need to see "Food" versus "Non-Food" items, they would like
to add one more selection to the parameter picklist. They would like to call
this selection "Food & Drink," and for this option to
provide a combination of the Food and Drink families in the
report when selected. (We might have suggested the word "Consumables,"
but the team seems set upon "Food & Drink," and, as we know,
appearances are everything in the reporting environment...). The consumers add
that, within the picklist, they want the "all" selection to appear as
the default in the selection box at run time.
carefully to the requirements. After confirming our understanding of the
specified enhancements, we set about to provide the requested functionality in
the sections that follow.
Considerations and Comments
setup of the environment to prepare for a hands-on walkthrough of the steps
would be time consuming, we will discuss the steps, and I will provide
illustrations that I hope will make an approach I have used clearer. There are,
of course, many ways to accomplish the same ends, and we can leverage numerous
tools for this purpose, as well. The important thing is to gain an awareness of
the concepts, the chief of which is the provision of a means for supplying MDX
qualified names to Reporting Services so that it can complete and
execute an MDX query to create a Dataset based upon a selected Analysis
We will be working in MSSQL
Server to create the picklist support table. Moreover, we will use Reporting
Services to create the picklist Dataset, as well as the primary Dataset
to which we supply the parameters that are selected by the
information consumer at runtime. We would likely have Analysis Services
open in the background, to refer to the cube structure as we construct the MDX
that populates the picklist support table. In addition, we might use
the MDX Sample Application (or another query tool) to formulate and test
the MDX syntax that we ultimately put into our picklist support table.
Whatever the means, one approach to meeting the expressed or similar consumer
needs follows in the next section.
first objective is to create a table to contain the syntax that will
ultimately populate the Value and Label fields of the Report
Parameters dialog inside the Report Designer of the intended report
within Reporting Services.
a Relational Table to Contain the Picklist Items
we would create a table to contain the selections that we wish to appear in the
parameter picklist at runtime, together with the associated "qualified"
MDX name (the Unique Name) that is needed in creating a Dataset
based upon an Analysis Services cube.
design for this table, to which I refer as MSAS_PickListMaster (I try to
name tables in the relational database in a way that makes dependencies easy to
understand, but this could obviously be named in accordance with any
convention), appears in Illustration 2.
Illustration 2: A Simple
Table Design for Parameter Picklist Support
columns I have defined in MSAS_PickListMaster represent but a few of
many possibilities. Their purposes are detailed in Table 1.
Table 1: Columns of the Parameter Picklist Support Table
A high-level grouping ("Picklist
Code") to assist in the selection of the correct picklist items for a
given picklist Dataset in Reporting Services.
(Examples: Product, Store, etc.)
subgrouping within PL Code, whereby we might provide even more precise
filtering in our picklist Dataset query.
Product Family, Store Country, etc.)
that we may create additional subgroup columns, as well, for similar purposes
at varying levels, etc.
picklist selection name we wish to display to the information consumer
at report execution time.
MDX name, known as the Unique Name within MDX, and corresponding to
the Display Name that the consumer selects.
several possible sorts, I included this one to allow for "other than
alpha" sorting of the parameter selections within the Dataset
queries that generate the parameter picklists.
The populated table
(using an MSSQL Server table as an example) appears as partially shown in Illustration
Illustration 3: Partial
View of the Populated Parameter Picklist Support Table
NOTE: In creating a table that we actually use to support
picklists within a modification of the sample FoodMart Sales report, it
would likely make more sense to create the parameter picklist support
table within MS Access, where the relational tables supporting the FoodMart
Sales cube (which contains the data presented in the report) reside. While
perhaps not a hard and fast requirement, placing the new table in the primary
source database would certainly mean more convenient reporting and upkeep.
Virtually all my real-world cube data sources rest upon
the MSSQL Server RDBMS, (even when the ultimate OLTP(s) involved lie within
other RDBMS'). A great deal of effort must often be expended to build fully
functional training examples in MS Access. This is one of the reasons that I
have avoided doing so in this article, as the preparation alone would dwarf the
procedure involved in the focus of the article, the use of a relational table
to support parameter picklists in OLAP reports.
created the table, based upon expressions that we might assemble and test using
the MDX Sample Application (or other OLAP query tool), we can now move
to Reporting Services and create the Dataset upon which we will
base our parameter picklists. We will examine the general steps in the
section that follows.
Create a Picklist Dataset to House the Picklist Data
Our next steps
will be to open the Visual Studio project that houses the report (or to create
a new project, obviously, if we are building the report from scratch), and then
to open (or create) the report involved. In our example of enhancing the FoodMart
Sales report, we would begin by creating a Dataset to support each
of the report parameters that we wished to create.
illustrate, let's look at an example for Product Family (expecting that
we will have discarded the Product Family parameter that came with the
sample report). We would first create a data source specifying the
database within which we have housed our new picklist support table. Then
we would create the SQL query to produce the Dataset to which we
would refer in our subsequent creation of the corresponding Report Parameter
for Product Family.
itself would look something like the following:
SELECT DispName, UniqName
WHERE (PL_Code='PROD' AND NameType='Family')
ORDER BY Sort1
behind this simple query is probably obvious to most of us. We are creating a straightforward
list (much as I described in detail, within an MDX context in String Functions: The .UniqueName
Function, in the MDX Essentials series)
whereby we generate two columns. The first houses the Display
Name for the picklist selection, while the second houses the Unique
Name, the MDX syntax that must be passed to the MDX query that
generates the ultimate Dataset (from the
cube) upon which the FoodMart Sales report is
based. Because we are interested in Products in general,
and Product Families specifically, we would insert these
specifications in the WHERE clause, as
shown above. Moreover, our provision of the sort key in the ORDER BY clause
would produce a Dataset that sorts
in the order we wish for it to assume in the ultimate picklist. (This could
also be done in the report itself, among other places; optimization of the
report in general requires consideration of where to place many of the
processes to get the most bang for our processing buck). This would allow us to
meet the need for the "default" selection of "All
Families" to appear at the top of the list, and therefore as default, in the parameter
picklist selector, with no additional user interaction at run time.
Once we had clicked the Run button in
the Report Designer, our efforts would be rewarded with
a Dataset that resembles that partially
depicted in Illustration 4.
Illustration 4: The
Picklist Support Dataset Appears ...
hone in on the logic we have used to meet the requirements of the consumers in Table
2, which presents the Dataset for the Product Family
Table 2: Close-up View of the Data in the Dual Columns of the Dataset
Products], [Product].[Product Family])
Products].[Food], [Product].[All Products].[Drink]
the "All" capability is supported by the appropriate MDX (we would,
again, have tested all our Unique Name syntax independently before
creating a Dataset based upon it to support our picklists). Moreover,
note how we are able to create a "grouping" of the Food and
Drink families, within its own picklist line item. This might have been
done numerous ways (I often create Named Sets within the cube to house
specifications of this type, and then simply place the Named Set titles
in the Unique Name column of the table, as applicable, among other
approaches). Suffice it to say that much flexibility exists in our design
choices, allowing us to optimize performance of the integrated solution in ways
that can be highly customized to our individual business environments.
the greatest advantages in using the relational table to support the OLAP
report picklists is that we can add new picklist items easily in one central
location that might lie outside the hierarchical structure of our cube
design, such as we have done with the Food & Drink item. We can
also make name changes for picklist Display Names (and for that matter
report Display Names) here, as well a handy capability if the need
arises to change the presentation in the report of an item whose name we wish
to leave in its original format in the OLAP data source itself. This can
become particularly useful if the report creator needs to "restate"
member names for, say, members of a given dimensional level, but has no editor
capabilities within the Analysis Services cube. Many other advantages
become apparent in "real world" situations where the use of the
relational picklist table, or a hybrid thereof, can be seen as a viable option.
not belabor the point with additional Datasets, as the same logic would
apply, in our example, at lower levels of the Product dimension. Next,
let's move into the creation of an example Report Parameter, referencing
our new Product Family picklist Dataset as part of the process.
Create a Report Parameter and Populate It with the Picklist Dataset
Having created our Product Family picklist Dataset (I named mine pX_ProdFamily, with the prefix "pX_" serving as an easy "reminder" that the Dataset exists to support a picklist), we are now ready to "connect" the Dataset with a new Report Parameter. We would go to the Layout tab of the Report Designer, and then select Report -à Report Parameters from the main menu atop the Designer, to start.
We would obviously either delete or modify the original ProductFamily report parameter, next creating a new parameter (I left mine with the same name in the samples I worked up for illustrations). We would then take the following actions within the Report Parameters dialog:
1. Ensure that ProductFamily appears in the Name box.
2. Ensure that Product Family: appears in the Prompt box.
3. Leave Data type at its default of "String."
4. Clear the checkboxes to the immediate left of the captions Allow null value and Allow blank value (appearing beneath the Prompt box).
5. Select the From query radio button underneath Available values.
6. Underneath the Dataset caption, select pX_ProdFamily, the Dataset we created earlier.
7. Underneath Value field, select UniqName.
8. Underneath Label field, select DispName.
9. Select the From query radio button underneath Default values.
10. Underneath the Dataset caption, select pX_ProdFamily, once again.
11. Underneath Value field, select UniqName.
We have now "connected" our relational picklist table with our report, via the Report Parameters dialog. Further, we have ensured the satisfaction of another consumer request, for the "All Products" picklist item to fall into place as the default at runtime. We have facilitated this by our placement of the Unique Name as the Value field from which the default value is selected. Placing UniqName in the Value selector will ensure that the available UniqName fields are retrieved, with the value from the first row in the dataset forced "to the top" by our sort key even if another, later addition alphabetically precedes naturally used as the default value.
The Report Parameters dialog for the new ProductFamily parameter appears as shown in Illustration 5.
Illustration 5: Report Parameters Dialog for ProductFamily
12. Click OK to save the new Report Parameters settings and to dismiss the Report Parameters dialog.
The dialog closes, leaving us with a new parameter in place. Our next step would be to add the parameter into the axis specification (or into the WHERE statement, possibly) of the MDX query that underlies the primary Dataset. Once the query was entered correctly, and the report's data fields were updated, we would string the query appropriately with the "=" sign preceding it, as well as enclosing it in quotation marks, as I show in many, many instances within my MSSQL Server Reporting Services series, most specifically within the Mastering OLAP Reporting subset of the series.
We would then proceed to preview the report, where we would see a picklist appear within the Product Family selection box that resembles that depicted in Illustration 6.
Illustration 6: The Product Family Picklist, with New Display Names in Place
As we have stated earlier, numerous changes would have to be made to the FoodMart Sales report to make it functional with the alterations we have examined. We would have to alter the primary Dataset to reference the parameter correctly, as well as to either supply Datasets to support parameters for lower levels of the Product dimension, to alter the query to populate the presented levels by taking the necessary record sets into consideration in the query, to eliminate the levels from the report, or otherwise reconcile the primary Dataset with the original report layout. My intent here was to discuss concepts more than to provide a hands-on recreation of the sample report.
In this article, we extended our exploration of parameter picklist support for our Reporting Services OLAP reports to examine a simply constructed, relationally based solution. In a departure from our typical, detailed "step by step" approach, we discussed a general procedure that allows for a great deal of flexibility in customization and extension for varying business environments. We began with a description of several expressed business needs within the OLAP reporting environment of a hypothetical client, and then discussed the use of a relational table to house the names desired for runtime display in report picklists, together with the associated "qualified" MDX names required to generate the primary OLAP Dataset. (The ultimate purpose of the primary Dataset, of course, is to provide the information presented in the report).
Next, we explored a simple layout for a table to support parameter picklists in our OLAP reports, providing examples of the data with which we would populate the table to support a given picklist level within the sample FoodMart Sales report. We then examined the settings required for referencing the picklist support table within the report via the Report Parameters dialog. Throughout our discussion, we highlighted strengths of using a relational table for picklist support, focusing upon ways that it could be used to meet business requirements in a flexible way that might be useful in many operating scenarios.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.