MSSQL Server Reporting Services: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting - Page 2

Relationally-Based Picklists for OLAP Reporting

Introduction and Business Scenario

As I explored in my articles String Functions: The .Name Function and String Functions: The .UniqueName Function, both members of the Database Journal MDX Essentials 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.

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

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

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

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