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

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.

We arrive at the Data view.

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

DefaultDateSupport

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

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.

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.

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.

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