Creating the Core Dataset
Recall, for purposes
of our practice session in this article, that the Finance department has
requested a basic expense summary report, which lists various store
location's expense totals. The consumers have expressed the need for a single
prompted parameter, based upon geographical store location, which allows
them to filter on location at runtime. An ideal scenario, they have told us,
would include the capability to select, within the same report, various
other hierarchical "rollups" surrounding location, such as city,
state and so forth.
We will
design the basic report, beginning with the unparameterized dataset, prior to
adding the parameter. This allows us to gain an appreciation for design first,
for, as we will see, addition of the parameter makes dataset refreshment a bit
less "elastic."
1.
Type (or cut and paste) the
following basic MDX into the Query pane - Data tab of the Report
Designer.
SELECT
{[Measures].[Amount],
RS_ns_PX_GeogStore_Hier} ON COLUMNS,
{[Account].[All Account].
[Net Income].[Total Expense]} ON ROWS
FROM
[Exp_Finance]
The Query
pane appears as shown in Illustration 27.
Illustration 27: MDX
Query in the Query Pane
2.
Click the Run
button in the toolbar (the "!" icon), shown in Illustration
28, to execute the query.
Illustration 28: The Run
Icon
The
dataset appears in the Results pane, below the query, as
partially shown in Illustration 29.
Illustration 29: Results
Dataset Appears (Collapsed, Default View)
3.
Click the Refresh
icon in the toolbar (circled in blue, to the left of the Run button),
shown in Illustration 28 above, to update the data fields.
NOTE: It is important that this step
is accomplished, and that the fields appear in the Fields tab (possibly
hidden / undocked), before proceeding.
Creating the Parameter Picklist Dataset
1.
In the Dataset
selector atop the Data tab, (which is now occupied by FoodMart 2000,
the dataset we just created), select New Dataset, as depicted in Illustration
30.
Illustration 30: Select
New Dataset in the Dataset Selector
The Dataset
dialog appears.
2.
Type the
following into the Name box.
ds_px_LocationHier
3.
Type (or cut
and paste) the following basic MDX into the Query string box.
SELECT
{RS_ns_PX_GeogStore_Hier} ON COLUMNS,
{[Store].Members} ON ROWS
FROM
[EXP_FINANCE]
The Dataset
dialog appears as shown in Illustration 31.
Illustration 31: Completed
Dataset Dialog
4.
Click OK
to accept our input.
The Dataset
dialog closes.
5.
Click the Run
icon in the toolbar, to execute the query.
The new dataset
appears in the Results pane, below the query, as partially shown
in Illustration 32.
Illustration 32: Results
Dataset Appears (Partial View)
This
dataset will serve as the support for our parameter picklist, (hence its name, ds_px_LocationHier)
which we will establish next.
Creating the Parameter
1.
Select Report
--> Report Parameters from the main menu atop the design environment, as
depicted in Illustration 33.
Illustration 33: Select
Report --> Report Parameters
...
The Report
Parameters dialog appears.
2.
Click Add.
3.
Type the
following into the Name box.
px_LocationHier
4.
Type the
following into the Prompt box.
Location:
5.
In the Available
Values section, click / select the radio button to the left of From
query.
6.
In the Dataset
selector to the right, select ds_px_LocationHier.
7.
In the Value
field selector, select Measures_RS_PX_StoreGeogMSASName_Hier.
8.
In the Label
field selector, select Measures_RS_PX_StoreGeogPLName_Hier.
9.
In the Default
Values section, click / select the radio button to the left of None.
The Report
Parameters dialog appears, as shown in Illustration 34.
Illustration 34: Results
Dataset Appears (Partial View)
10.
Click OK
to accept and save settings, and to close the Report Parameters dialog.
» 
Comment and Contribute
