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 shown in Illustration 27.
Illustration 27: 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 depicted in Illustration 28.
Illustration 28: Saving
Changes First ...
The Process
Database – Adventure Works DW dialog appears, as shown in Illustration 29.
Illustration 29: 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 depicted in Illustration
30.
Illustration 30: 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 Parameters 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 Parameterization Support via New Datasets
We will
return to the Data tab of our still open report within Reporting
Services, where we will create three new datasets, one for each of
the Report Parameters for Year, Quarter, and Month,
within the following steps. (While we might achieve our ends through other
arrangements, we will provide a straightforward solution example in this
practice session, which we will build upon in subsequent articles).
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
downward selector arrow on the right side of the Dataset selector.
3.
Select <New
Dataset> within the selector, as shown in Illustration 31.
Illustration 31: Creating
a New Dataset
The Dataset dialog opens.
4.
Type the
following into the Name box of the dialog (replacing the default name of “DataSet1”).
AS_DateYrParams_Support
5.
Click OK
to dismiss the dialog and to open the graphical
query designer.
6.
Click the Design Mode
button to shift to the generic query designer, as depicted in Illustration 32.
Illustration 32: Shifting
to the Generic Query Designer ...
7.
Click the Refresh
button on the Data tab toolbar, as shown in Illustration 33.
Illustration 33: Refresh
the Environment for the Newly Processed Data Source ...
8.
Type (or cut
and paste) the following into the Query pane of the generic query designer, replacing the default, shell syntax:
SELECT
{[Measures].[YearParamCaption], [Measures].[YearParamValue] } ON COLUMNS,
{[Date].[Calendar Year].MEMBERS} ON ROWS
FROM
[Adventure Works]
9.
Click the Execute Query
(!) button to run the query against the Analysis Services data
source, as depicted
in Illustration 34.
Illustration 34: Execute
the New Query ...
The query
runs, and the data is returned, as shown in Illustration 35.
Illustration 35: The New
Dataset Appears ...
As we can
see, we have the workings of a good support dataset for the Year parameter
picklist – both a caption and a value. Let’s assume we will
want to eliminate the “All Periods” option within this (and the other)
datasets. One way to accomplish this can be handled with a query filter.
10.
Click the ellipses
(...) button to the immediate right of the Dataset selector
(currently showing our new AS_DateYrParams_Support dataset), as depicted
in Illustration 36.
Illustration 36: Click
the Ellipses Button to the Right of the Dataset Selector ...
The
tabbed Dataset dialog opens.
11.
Click the Filters
tab.
12.
Type the following syntax into
the Expression column of the Filters table, top row:
=CSTR(Fields!Calendar_Year.Value)
13.
Select the “>” (“greater
than”) sign in the adjacent Operator box of the top row.
14.
Type the following into the Value
column to the immediate right of the Operator column, in the top
row:
=”0”
The Filters
tab, with our input, appears as shown in Illustration 37.
Illustration 37: Our
Newly Added Filter ...
15.
Click OK to accept our
input, and to dismiss the Dataset dialog.
In
like manner, we will add a filtered dataset to support each of the two
remaining Report Parameters we have selected for our practice exercise, DateCalendarQuarterofYear
and DateMonthofYear.
16.
Click the
downward selector arrow on the right side of the Dataset selector, once
again.
17.
Select <New
Dataset> within the selector, as we did earlier.
The Dataset dialog opens.
18.
Type the
following into the Name box of the dialog (replacing the default name of “DataSet1”).
AS_DateQtrParams_Support
19.
Click OK
to dismiss the dialog and to open the graphical query designer.
20.
Click the Design
Mode button to shift to the generic query designer, as before.
21.
Type (or cut
and paste) the following into the Query pane of the generic query designer, replacing the default, shell
syntax:
SELECT
{[Measures].[QtrParamCaption], [Measures].[QtrParamValue] } ON COLUMNS,
{[Date].[Calendar Quarter of Year].MEMBERS} ON ROWS
FROM
[Adventure Works]
The query
appears in the Query pane of the query designer, as depicted in Illustration
38.
Illustration 38: The
Query within the Query Pane
22.
Click the Execute
Query (!) button to run the query against the Analysis Services
data source, as we
did for the earlier dataset.
The query
runs, and the data is returned, as shown in Illustration 39.
Illustration 39: The New
Dataset Appears ...
We will
again eliminate the “All Periods” selection from our parameter picklist
at runtime by adding a query filter entry.
23.
Click the ellipses
(...) button to the immediate right of the Dataset selector
(currently showing our new AS_DateQtrParams_Support dataset), as we did
before.
24.
Click the Filters
tab on the Dataset
dialog that opens
next.
25.
Type the
following syntax into the Expression column of the Filters table,
top row:
=CSTR(Fields!Calendar_Quarter_of_Year.Value)
26.
Select the “>”
(“greater than”) sign in the adjacent Operator box of the top
row.
27.
Type the
following into the Value column to the immediate right of the Operator
column, in the top row:
=”0”
The Filters
tab, with our input, appears as depicted in Illustration 40.
Illustration 40: Our
Newly Added Filter ...
28.
Click OK
to accept our input, and to dismiss the Dataset dialog.
29.
Click the
downward selector arrow on the right side of the Dataset selector, once
again.
30.
Select <New
Dataset> within the selector, as we did earlier.
The Dataset dialog opens.
31.
Type the
following onto the Name box of the dialog (replacing the default name of “DataSet1”).
AS_DateMoParams_Support
32.
Click OK
to dismiss the dialog and to open the graphical query designer.
33.
Click the Design
Mode button to shift to the generic query designer, as before.
34.
Type (or cut
and paste) the following into the Query pane of the generic query designer, replacing the default, shell
syntax:
SELECT
{[Measures].[MoParamCaption], [Measures].[MoParamValue] } ON COLUMNS,
{[Date].[Month of Year].MEMBERS} ON ROWS
FROM
[Adventure Works]
The query
appears in the Query pane of the query designer, as shown in Illustration
41.
Illustration 41: The
Query within the Query Pane
35.
Click the Execute
Query (!) button to run the query against the Analysis Services
data source, as we
did for the earlier two datasets.
The query
runs, and the data is returned, as depicted in Illustration 42.
Illustration 42: The New
Dataset Appears ...
We will
again eliminate the “All Periods” selection from our parameter picklist
at runtime by adding a query filter entry.
36.
Click the ellipses
(...) button to the immediate right of the Dataset selector
(currently showing our new AS_DateMoParams_Support dataset), as we did
before.
37.
Click the Filters
tab on the Dataset
dialog that opens
next.
38.
Type the following
syntax into the Expression column of the Filters table, top row:
=CSTR(Fields!Month_of_Year.Value)
39.
Select the “>”
(“greater than”) sign in the adjacent Operator box of the top
row.
40.
Type the
following into the Value column to the immediate right of the Operator
column, in the top row:
=”0”
The Filters
tab, with our input, appears as shown in Illustration 43.
Illustration 43: Our
Newly Added Filter ...
41.
Click OK
to accept our input, and to dismiss the Dataset dialog for the final
time.
We are now ready to “hook up” our
new datasets to the Report Parameters they will support. We will
accomplish this within the next subsection.
» 
Comment and Contribute
