MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist - Page 7July 26, 2004 Creating the Core DatasetRecall, 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],
The Query pane appears as shown in Illustration 27.
2. Click the Run button in the toolbar (the "!" icon), shown in Illustration 28, to execute the query.
The dataset appears in the Results pane, below the query, as partially shown in Illustration 29.
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 Dataset1. 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.
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.
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.
This dataset will serve as the support for our parameter picklist, (hence its name, ds_px_LocationHier) which we will establish next. Creating the Parameter1. Select Report --> Report Parameters from the main menu atop the design environment, as depicted in Illustration 33.
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.
10. Click OK to accept and save settings, and to close the Report Parameters dialog. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
RS_ns_PX_GeogStore_Hier} ON COLUMNS,
{[Account].[All Account].
