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.
Illustration 22:
Processing the Adventure Works DW Project
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.
Illustration 23: Saving
Changes First ...
The Process
Database – Adventure Works DW dialog appears, as depicted in Illustration
24.
Illustration 24: The
Process Database – Adventure Works DW Dialog
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.
Illustration 25: Process
Succeeded Message Appears ...
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.
Illustration 26: Refresh
the Environment for the Newly Processed Data Source ...
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.
Illustration 27:
Creating a New Dataset
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.
Illustration 28:
Shifting to the Generic Query Designer ...
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.
Illustration 29: Execute
the New Query ...
The query
runs, and the data is returned, as partially depicted in Illustration 30.
Illustration 30: The New
Dataset Appears ... (Partial View)
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.
» 