dcsimg

Support Parameterization from Analysis Services - Page 5

February 26, 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 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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers