Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 29, 2008

Support Parameterization from Analysis Services - Parameter Defaults - Page 4

By William Pearson

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 setLast 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”).


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:

   {[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)
   [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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM