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

September 19, 2005

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.