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"), 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.

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

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

About the Mastering OLAP Reporting Articles...

As I 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.)

As I 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 Intelligence.

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

Overview

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

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

We will 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, we will:

• Discuss example business needs within the OLAP reporting environment of a hypothetical client;
• Discuss the use of relational picklist support as a possible solution for the expressed business needs, highlighting advantages in this approach;
• Explore a sample layout for a table to support parameter picklists in our OLAP reports;
• Investigate 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

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.

Considerations and Comments

Because 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 Services cube.

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.

Procedure

Preparation

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

Create a Relational Table to Contain the Picklist Items

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

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

The columns I have defined in MSAS_PickListMaster represent but a few of many possibilities. Their purposes are detailed in Table 1.

The populated table (using an MSSQL Server table as an example) appears as partially shown in Illustration 3.

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

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

The query itself would look something like the following:

SELECT DispName, UniqName

FROM         MSAS_PickListMaster

WHERE (PL_Code='PROD' AND NameType='Family')

ORDER BY Sort1

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

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

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

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

We will 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:

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.

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.

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.

Conclusion ...

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.