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.
» 
Comment and Contribute
