dcsimg

Support Parameterization from Analysis Services - Parameter Defaults - Page 3

April 29, 2008

We next need to add three pairs of calculated members (for a total of six individual members) – one pair, consisting of a default caption and a default value, for each of our three date-related parameters. In addition to the named set we created earlier, we will also specify these calculated members within the SELECT clause of a new dataset in Reporting Services, for the purposes of supporting our parameter defaults. (We’ll see how all this aligns later). These calculated members will 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 - possibly 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 the section that follows.

13.  Click the bottom entry in the Script Organizer pane, to anchor the cursor, as we did before creating our named set above.

NOTE: Once a calculation is created, we can always move it up or down within the Script Organizer pane using the Move Up or Move Down buttons, respectively. These buttons appear together atop the Calculations tab, as depicted in Illustration 14.


Illustration 14: Move Up and Move Down Buttons, atop the Calculations Tab

14.  Click the New Calculated Member button atop the Calculations tab, as shown in Illustration 15.


Illustration 15: Click the New Calculated Member Button

Clicking the New Calculated Member button adds a new calculation to the Script Organizer pane (by default named “[Calculated Member]”) and displays fields for its definition in the calculations form in the Calculations Expressions pane, just as we saw with the named set we created earlier.

15.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

[Year Param Default Caption]

16.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Calendar Year].CURRENTMEMBER.MEMBER_CAPTION

The new Year Param Default Caption calculation appears in the Calculations Expressions pane, with our input, as depicted in Illustration 16.


Illustration 16: The Year Param Default Caption Calculation in the Calculations Expressions Pane

17.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the first calculated member we added.

18.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

We have successfully added support for the default caption of the Year Report Parameter. As stated earlier, we will not reference this value within our ultimate report layout. The default caption simply affords us an opportunity to do so at a later time if convenient, and might mean less adjustment to our working datasets, assuming we later meet the common client request to add this to our report for various reasons, instead of, or in addition to, the qualified name that we already have there. (The caption field in the dataset also makes clear the “English” name of the corresponding Default Value column in the dataset that is retrieved).

19.  Click the New Calculated Member button, once again.

20.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

[Year Param Default Value]

21.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME

The new Year Param Default Value calculation appears in the Calculations Expressions pane, with our input, as shown in Illustration 17.


Illustration 17: The Year Param Default Value Calculation in the Calculations Expressions Pane

22.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the first calculated member we added.

23.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

Now let’s repeat the calculated member creation process with the next pair of calculated members – for support of the Quarter parameter defaults.

24.  Click the New Calculated Member button, once again.

25.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”, as before).

[Qtr Param Default Caption]

26.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Calendar Quarter of Year].CURRENTMEMBER.MEMBER_CAPTION

The new Qtr Param Default Caption calculation appears in the Calculations Expressions pane, with our input, as depicted in Illustration 18.


Illustration 18: The Qtr Param Default Caption Calculation in the Calculations Expressions Pane

27.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the calculated member we added earlier.

28.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

29.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”, as before).

[Qtr Param Default Value]

30.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Calendar Quarter of Year].UNIQUENAME

The new Qtr Param Default Value calculation appears in the Calculations Expressions pane, with our input, as shown in Illustration 19.


Illustration 19: The Qtr Param Default Value Calculation in the Calculations Expressions Pane

31.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the calculated member we added earlier.

32.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

33.  Click the New Calculated Member button, once again.

34.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”, as before).

[Month Param Default Caption]

35.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Month of Year].CURRENTMEMBER.MEMBER_CAPTION

The new Month Param Default Caption calculation appears in the Calculations Expressions pane, with our input, as depicted in Illustration 20.


Illustration 20: The Month Param Default Caption Calculation in the Calculations Expressions Pane

36.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the other calculated members we added.

37.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

38.  Click the New Calculated Member button, once more.

39.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

[Month Param Default Value]

40.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Month of Year].CURRENTMEMBER.UNIQUENAME

The new Month Param Default Value calculation appears in the Calculations Expressions pane, with our input, as shown in Illustration 21.


Illustration 21: The Month Param Default Value Calculation in the Calculations Expressions Pane

41.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the first calculated member we added.

42.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

We have completed the addition of the Analysis Services objects required to support the targeted date-related Report Parameters in the Reporting layer.








The Network for Technology Professionals

Search:

About Internet.com

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