Support Parameterization from Analysis Services - Parameter Defaults - Page 4April 29, 2008 Process the Analysis Services Database Our next step will be to process the sample database, so as to make the new calculated members available to Reporting Services. To do so, we will take the following steps. 1. Within the Solution Explorer, right-click the Adventure Works DW project. 2. Select Process ... from the context menu that appears, as depicted in Illustration 22.
3. Click the Yes button on the message box appearing next, asking if we wish to save all changes before processing, as shown in Illustration 23.
The Process Database – Adventure Works DW dialog appears, as depicted in Illustration 24.
4. Click Run ... on the Process Database – Adventure Works DW dialog, to begin processing. The Process Progress viewer appears, and details the processing steps as they take place. Once processing is completed, a Process succeeded message appears in the Status bar at the bottom of the dialog, as shown in Illustration 25.
5. Click Close to close the Process Progress viewer. 6. Click Close on the Process Database – Adventure Works DW dialog. We can now move back to our report to complete the steps required to access our new Analysis Services parameter support objects, and to reference them in our targeted Report Parameters. Practice: Support Parameter Defaults in Reporting Services via Analysis Services Objects We will next access and reference our new Analysis Services support objects from within our sample report. Access Analysis Services Parameter Default Support via a New Dataset We will return to the Data tab of our still open report within Reporting Services, where we will create a new dataset, designed to support parameter defaults for Year, Quarter, and Month, within the following steps. Our intent, as we have alluded earlier, is to create a dataset that juxtaposes our new named set “Last Period Sales,” (as a row axis in the new Reporting Services dataset) and each of the Year, Quarter, and Month caption and value calculated members. As we noted earlier, because we are retrieving data at the Month level, the corresponding row set returned by Reporting Services will also include, as we shall see, higher levels within the Date dimension (Calendar hierarchy) - a characteristic of Reporting Services that produces a dataset ideal for many purposes. 1. Leaving the Cube Designer open at the present position, return to the Data tab of the report with which we are working in Reporting Services. 2. Click the Refresh button on the Data tab toolbar, as depicted in Illustration 26.
3. Click the downward selector arrow on the right side of the Dataset selector. 4. Select <New Dataset> within the selector, as shown in Illustration 27.
The Dataset dialog opens. 5. Type the following into the Name box of the dialog (replacing the default name of “DataSet1”). AS_DateParamsDefault_Support 6. Click OK to dismiss the dialog and to open the graphical query designer. 7. Click the Design Mode button to shift to the generic query designer, as depicted in Illustration 28.
8. Type (or cut and paste) the following into the Query pane of the generic query designer, replacing the default, shell syntax:
SELECT
{[Measures].[Year Param Default Caption],
[Measures].[Year Param Default Value],
[Measures].[Qtr Param Default Caption],
[Measures].[Qtr Param Default Value],
[Measures].[Month Param Default Caption],
[Measures].[Month Param Default Value]}
ON AXIS(0),
[Last Period Sales] ON AXIS(1)
FROM
[Adventure Works]
9. Click the Execute Query (!) button to run the query against the Analysis Services data source, as shown in Illustration 29.
The query runs, and the data is returned, as partially depicted in Illustration 30.
As we can see, we have the workings of a support dataset for the parameter defaults – both a caption and a value for each of the Year, Quarter, and Month parameters. (Note the way that the single named set –defined axis row generates the default for all parameter levels of the Date dimension under consideration). We are now ready to “hook up” the Default Value columns of the new dataset to the Report Parameters they will support. We will accomplish this within the next subsection. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
