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.