MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts - Page 6
July 27, 2004
Add Datasets and Parameters
The FoodMart 2000 Sales report that we have modified is supported by a dataset, called ProductData, which is generated by an MDX query.
1. Click the Data tab of the Report Designer.
2. If necessary, select ProductData in the dataset selector just underneath the Data tab.
The MDX query appears, as depicted in Illustration 21.
Click for larger image
We would almost certainly remove the Cost measure from the query, as well as the Profit calculated field from the Fields tab (located either atop the report, or within the Dataset dialog of the ProductData dataset), but we will leave all in place for now for purposes of our practice exercise. (Were this a real world report, there are several items that I would modify / eliminate, but they will not interfere with our present purposes).
3. Add the following WHERE clause at the end of the MDX query:
WHERE ([Store].[All Stores].[USA].[OR].[Portland].[Store 11])
The modified query appears in the Query view of the Dataset tab as shown in Illustration 22.
In adding the WHERE clause, we have sliced the cube by a member of the Store dimension. Although we have been asked to create a parameter that allows information consumers to specify the identity of the store upon which they wish to slice the data at runtime, for the time being we are getting a working, "hard-coded" query into place that does what we wish, before adding parameters.
This is a good practice, as we shall see, because once we add parameters into the mix, we lose the flexibility of being able to generate datasets. Furthermore, to create a cascading picklist scenario, we will need to create a dataset for each level of "drilling" involved in getting to the lowest level member, an individual store, in our present case. Portions of the results are passed from one to the next, with each passed component acting as the parameter for the next subordinate dataset filter. The output of the lowest level dataset, the individual store in our case, forms the "qualified," unique MDX name that is then fed to the WHERE clause of the core dataset. The flow of the process conceptually resembles that depicted in Illustration 23.
As we can see in the illustration above, we will require four separate datasets, in addition to the core dataset (labeled in the illustration), to which we will return after assembling the additional datasets. The core dataset will be modified to insert the parameter output of the added datasets to the WHERE clause of the core dataset. While the order in which the components are created is not rigidly dictated, we will do them in order of intended operation, in an effort to make the steps involved more memorable, and their overall integration in the report clearer.
First, let's clean house further and delete the old parameter picklist dataset, ProductList.
4. Click the Data tab, if necessary.
5. Select the ProductList dataset in the Dataset selector atop Data tab.
The MDX query for the ProductList dataset appears in the query view.
6. Click the Delete icon once ProductList is selected, as shown in Illustration 24.
7. Click Yes on the confirmation message box that next appears, as depicted in Illustration 25.
The ProductList dataset disappears. We are ready to begin the creation of our cascading picklist datasets at this point.
8. Select <New Dataset ...> in the Dataset selector, as shown in Illustration 26.
The Dataset dialog appears, defaulted to the Query tab.
9. For the dialog boxes shown in Table 1, type the corresponding items: