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.
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.
Illustration 22: The MDX
Query with our Added WHERE Clause
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.
Illustration 23: Cascaded
Datasets "Assemble" the "Qualified" Name ...
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.
Illustration 24: Select
and Delete the ProductList Dataset
7.
Click Yes
on the confirmation message box that next appears, as depicted in Illustration
25.
Illustration 25: Click
Yes to Confirm Deletion ....
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.
Illustration 26: Select
<New Dataset> ....
The Dataset
dialog appears, defaulted to the Query tab.
9.
For the dialog
boxes shown in Table 1, type the corresponding items:
|
In this Dialog Box:
|
Type the following:
|
|
Name:
|
ds_pX_Country
|
|
Data source:
|
FoodMart 2000
|
|
Command
type:
|
Text
|
|
Query string:
|
-- RS007-1 MDX Query to Support Country Picklist
WITH
MEMBER
[MEASURES].[Country_Qualified_Name]
AS
'[Store] .CurrentMember.UniqueName'
MEMBER
[MEASURES].[Country_Report_Name]
AS
'[Store].CurrentMember.Name'
SELECT
{[MEASURES].[Country_Qualified_Name],
[MEASURES].[Country_Report_Name]} ON AXIS(0),
{[Store].[Store Country].Members} ON AXIS(1)
FROM
[Sales]
|
Table 1: ds_pX_Country Dataset Details
» 
Comment and Contribute
