The Dataset
dialog, Query tab appears as depicted in Illustration 27.
10.
Click OK
to accept our input and close the Dataset dialog.
We are
returned to the design environment, Data tab, where we see the new MDX
query appear in the Query view in the upper section of the tab.
11.
Execute the
query by clicking the Run button, as shown in Illustration 28.
Illustration 28: Execute
the Query with the Run Button ....
The query
executes, and returns the dataset depicted in Illustration 29.
Illustration 29: The
Returned Dataset
Having
created the first of four datasets, we follow the same process with three
additional datasets, one for each drilldown level of the Store hierarchy.
12.
Create the
following datasets:
- ds_pX_State
- ds_pX_City
- ds_pX_Store
using the corresponding details contained within Tables 2,
3 and 4 below.
|
In this Dialog Box:
|
Type the following:
|
|
Name:
|
ds_pX_State
|
|
Data source:
|
FoodMart 2000
|
|
Command
type:
|
Text
|
|
Query string:
|
-- RS007-2 MDX Query to Support State Picklist
WITH
MEMBER
[MEASURES].[State_Qualified_Name]
AS
'[Store].CurrentMember.UniqueName'
MEMBER
[MEASURES].[State_Report_Name]
AS
'[Store].CurrentMember.Name'
SELECT
{[MEASURES].[State_Qualified_Name],
[MEASURES].[State_Report_Name]} ON AXIS(0),
{[Store].[Store State].Members} ON AXIS(1)
FROM
[Sales]
|
Table 2: ds_pX_State Dataset Details
|
In this Dialog Box:
|
Type the following:
|
|
Name:
|
ds_pX_City
|
|
Data source:
|
FoodMart 2000
|
|
Command
type:
|
Text
|
|
Query string:
|
-- RS007-3 MDX Query to Support City Picklist
WITH
MEMBER
[MEASURES].[City_Qualified_Name]
AS
'[Store] .CurrentMember.UniqueName'
MEMBER
[MEASURES].[City_Report_Name]
AS
'[Store].CurrentMember.Name'
SELECT
{[MEASURES].[City_Qualified_Name],
[MEASURES].[City_Report_Name]} ON AXIS(0),
{[Store].[Store City].Members} ON AXIS(1)
FROM
[Sales]
|
Table 3: ds_pX_City Dataset Details
|
In this Dialog Box:
|
Type the following:
|
|
Name:
|
ds_pX_Store
|
|
Data source:
|
FoodMart 2000
|
|
Command
type:
|
Text
|
|
Query string:
|
-- RS007-4 MDX Query to Support Store Picklist
WITH
MEMBER
[MEASURES].[Store_Qualified_Name]
AS
'[Store] .CurrentMember.UniqueName'
MEMBER
[MEASURES].[Store_Report_Name]
AS
'[Store].CurrentMember.Name'
SELECT
{[MEASURES].[Store_Qualified_Name],
[MEASURES].[Store_Report_Name]} ON AXIS(0),
{[Store].[Store Name].Members} ON AXIS(1)
FROM
[Sales]
|
Table 4: ds_pX_Store Dataset Details
Be sure,
once you have added each dataset, to execute it via the Run button, as
we did for ds_pX_Country above, to populate the fields of the dataset
and to ensure accuracy of query input. Once the datasets are in place and have
been executed, click the Fields tab, and choose each to verify its
contents, from the selector shown in Illustration 30.
Illustration 30:
Datasets Appear on Fields Tab
|
TIP:
In setting up picklist
datasets for Reporting Services, I often like to leverage MSAS and take
advantage of the cube structure to store the definitions instead of building
the datasets as shown. This can be done through the creation of calculated members
in MSAS, which are constructed to contain the MDX queries that define the
datasets. The obvious benefit is the always-welcome reusability
factor; if we create calculated members to contain queries that we reference
frequently in our reports, we need only reference each member after typing
the code once.
This also promotes
consistency, and helps us to avoid errors. Named sets can act as a
good complement to this approach, allowing us to go a step further and group
the calculated members into a single reference that we can pull into a report
axis easily. Other, perhaps less obvious benefits can also accrue to the
innovative user. (This is another example of a technique that can be
accomplished within the MSAS / Reporting Services combination, but which
would be difficult or impossible in other enterprise OLAP reporting
packages).
For examples along these
lines, see my Database Journal article
Create a Cube-Based
Hierarchical Picklist, which focuses on the MSAS side of the subject.
|
» 
Comment and Contribute
