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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 31, 2008

Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets - Page 3

By William Pearson

Practice

Having already created a clone report containing date-related parameters, we can go directly to create a dataset that we can use, in much the same manner as we used those automatically created to support our Report Parameters (see Pt. I for an in-depth examination of the relevant objects), this time to support the default fields within each of the associated Report Parameter settings.

Procedure: Create a Dataset to Support Dynamic Parameter Picklist Defaults at Runtime

Recall that we noted, within our earlier discussion, that there are multiple ways to support dynamic parameter defaults within Reporting Services, as well as within other layers of the integrated Microsoft business intelligence solution. Our client colleagues have requested that we deliver a means of supporting defaults for the time parameters that reflect the “most recent period” (including Year, Quarter, and Month) for which our cube contains data.

One of the many versatile features of Reporting Services lies within its support of expressions throughout the various objects with which it accomplishes its mission. We can easily exploit this utility by creating a dataset, based upon an MDX query that we construct, to retrieve the Year, Quarter, and Month labels and values identifying the “most recent” of each of these periods for which data exists within our cube. We will obtain some practical experience with this within the following steps.

1.  Click the Data tab.

We arrive at the Data view.

2.  Using the Dataset selector on the left side of the Data tab toolbar, select the <New Dataset> selection at the bottom of the selection, as depicted in Illustration 2.


Illustration 2: Select <New Dataset> ...

The Dataset dialog opens with the cursor defaulted at the Name input box.

3.  Type the following into the Name input box:

DefaultDateSupport

The Dataset dialog, with our input, appears as shown in Illustration 3.


Illustration 3: Dataset Dialog with Our Input ...

4.  Click OK to accept our input, and to enter the MDX Query Designer in Design Mode.

5.  Switch to Query Mode by clicking the Design Mode toggle button on the toolbar, as depicted in Illustration 4.


Illustration 4: Switch to Query Mode ...

6.  Replacing the code snippet that appears by default, type (or cut and paste) the following into the Query pane:

WITH 
SET
   [Last Period Sales]
AS
   'TAIL(
      FILTER(
         [Date].[Calendar].[Month].MEMBERS,
            NOT ISEMPTY ([Measures].[Internet Sales Amount])
          ),
      1
   )'

MEMBER
   [Measures].[YearParamDefaultCaption] 
AS
   '[Date].[Calendar Year].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER 
   [Measures].[YearParamDefaultValue] 
AS 
   '[Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME' 

MEMBER
   [Measures].[QtrParamDefaultCaption] 
AS
   '[Date].[Calendar Quarter of Year].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER 
   [Measures].[QtrParamDefaultValue] 
AS 
   '[Date].[Calendar Quarter of Year].CURRENTMEMBER.UNIQUENAME' 

 style="color: green; background: transparent;"MEMBER
   [Measures].[MonthParamDefaultCaption] 
AS
   '[Date].[Month of Year].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER 
   [Measures].[MonthParamDefaultValue] 
AS 
   '[Date].[Month of Year].CURRENTMEMBER.UNIQUENAME' 



SELECT 
   {[Measures].[YearParamDefaultCaption],  [Measures].[YearParamDefaultValue],
       [Measures].[QtrParamDefaultCaption], [Measures].[QtrParamDefaultValue],         
           [Measures].[MonthParamDefaultCaption], [Measures].[MonthParamDefaultValue]} 
       ON AXIS(0),

   [Last Period Sales]  ON AXIS(1)

FROM  
   [Adventure Works]

The Query pane, with our input, appears as shown in Illustration 5.


Illustration 5: Our Query within the Query Pane

Our query accomplishes several things. First, it creates a named set (“Last Period Sales”), via the WITH SET keywords, within which is the logic to determine the “most recent period” (including Year, Quarter, and Month) for which our cube contains data.

WITH 
SET
   [Last Period Sales]
AS
   'TAIL(
      FILTER(
         [Date].[Calendar].[Month].MEMBERS,
            NOT ISEMPTY ([Measures].[Internet Sales Amount])
          ),
      1
   )'

The expression that defines Last Period Sales leverages the MDX Tail() function in conjunction with the Filter() function, along with NOT ISEMPTY(), to determine the latest (in time) Calendar Month in the cube containing data. (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.

NOTE: For more details surrounding the MDX Tail() function, see Basic Set Functions: Subset Functions: The Tail() Function. For more information about the Filter() function, see Basic Set Functions: The Filter() Function. Examples of the foregoing functions in action can also be found in other member articles throughout my MDX Essentials series at Database Journal.

Many will recognize the WITH MEMBER keywords as representing the creation of various calculated members, which we also specify within our SELECT clause for the purposes of supporting our parameter defaults. These work in a manner that is very similar to the calculated members that appeared within the automatically generated dataset queries that we examined in Pt. I. They create the following six data columns within the returned dataset:

  • YearParamDefaultCaption
  • YearParamDefaultValue
  • QtrParamDefaultCaption
  • QtrParamDefaultValue
  • MonthParamDefaultCaption
  • MonthParamDefaultValue

The “default caption” columns will not be used within our parameter definitions, but they represent a possibly useful “label” that I would typically add into my design of datasets of this sort - mostly for use in labeling within the report, where it might be helpful to list our parameters so that “secondary” information consumers are made aware of the filters we have placed upon the data when looking at, say, a printed or exported version of the report. The “caption” version of the “default value,” (the value itself represents the actual qualified names that we pass as a filter to Analysis Services at runtime) would perhaps serve as a more understandable form of the values when used in this manner. We will see how the default values are used within the Report Parameters in our next steps.

Let's test our query for general operability at this stage, where it's convenient to do so, before going forward.

7.  Click the Execute Query button within the toolbar of the Data tab, as depicted in Illustration 6.


Illustration 6: Execute the Query ...

Data populates the Data pane, and appears similar to that partially shown in Illustration 7.


Illustration 7: The Retrieved Parameter Defaults Support Dataset (Partial View)



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date