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

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.