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:
1. Ensure that ProductFamily appears in the Name box.
2. Ensure that Product Family: appears in the Prompt box.
3. Leave Data type at its default of "String."
4. Clear the checkboxes to the immediate left of the captions Allow null value and Allow blank value (appearing beneath the Prompt box).
5. Select the From query radio button underneath Available values.
6. Underneath the Dataset caption, select pX_ProdFamily, the Dataset we created earlier.
7. Underneath Value field, select UniqName.
8. Underneath Label field, select DispName.
9. Select the From query radio button underneath Default values.
10. Underneath the Dataset caption, select pX_ProdFamily, once again.
11. Underneath Value field, select UniqName.
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.
Illustration 5: Report Parameters Dialog for ProductFamily
12. Click OK to save the new Report Parameters settings and to dismiss the Report Parameters dialog.
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.
Illustration 6: The Product Family Picklist, with New Display Names in Place
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.
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.