MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters - Page 4

May 16, 2005

Enhance the Foodmart Sales Cube to Support a Hierarchical Date Picklist in Reporting Services

The Cube Editor opens.

The Calculated Member Builder opens.

RS_pX_TimePLDispName_Hier

While the name of the calculated member can obviously be anything useful in the environment in which we are creating it, I typically use a name similar to the above, in order to make it apparent, both inside Analysis Services and in the reporting application (Reporting Services in this article), that the calculated member exists for use in the reporting ("RS") application. In addition, it makes it apparent that it has been created to support picklists ("pX"), and that this particular calculated member exists to generate a picklist display name ("PLDispName") in our report(s) for the various levels of the Time dimension. The specific convention we use in our own business environments should conform to the needs and conventions of the respective environment, of course.

Space([Time].Currentmember.Level.Ordinal * 3) + [Time].CurrentMember.Name

The MDX expression above returns, via our calculated member, a display name. We will use this expression to generate the picklist that the information consumers see, as selection options upon being prompted, each time they attempt to generate a report in which we insert the picklist to support a parameter. An explanation of the components of the above expression appears in Table 2.

NOTE: For an explanation of the .CurrentMember function, see my article MDX Essentials: MDX Member Functions: "Relative" Member Functions in the MDX Essentials series at Database Journal.

The Calculated Member Builder appears with our input, as partially shown in Illustration 7.

The new Calculated Member appears in the tree within the Calculated Members folder.

The Calculated Member Builder opens.

RS_pX_TimeMSASName_Hier

We are again using a naming convention for the member to make it apparent, both inside Analysis Services and in the reporting application, that the calculated member exists for use in the reporting ("RS") application, that it has been created to support picklists ("pX"), and that this particular calculated member exists to generate a "qualified name" (the Unique Name) within MDX.

[Time].CurrentMember.UniqueName

The MDX expression will return, via our calculated member, the unique name (a member property) that we will use as the value field in a Dataset query, upon which we base our ultimate report. In other words, it generates the qualified / full name that corresponds to the "user-friendlier" picklist name that the user selects at runtime.

The Calculated Member Builder appears, with our input, as partially depicted in Illustration 8.

The new Calculated Members appear in the tree within the Calculated Members folder as shown in Illustration 9.

(Compressed View)

Now that we have the two calculated members in place, let's create a named set to contain them conveniently for use in the reporting application.

The Named Set Builder opens.

RS_pX_Time_Hier

As we saw to be the case with the calculated members earlier, the name of the set can obviously be anything useful in the environment in which we are creating it. I typically use a name similar to the above, in order to make it apparent, both inside Analysis Services and from the reporting application (Reporting Services in this article), that the named set exists for use in the reporting ("RS") application. IN addition, it makes it apparent that it has been created to support picklists ("pX"), and that this particular named set exists to support picklists in our reports for the various hierarchical levels of the Time dimension.

{[Measures].[RS_pX_TimePLDispName_Hier],[Measures].[RS_pX_TimeMSASName_Hier]}

The set we specify above will return, via our named set, both the picklist name (which the consumers will see) and the "qualified" MSAS name. The former name provides the list from which the intended audience can select the Time levels they wish to appear in the report; the latter name supplies the MDX- "qualified" name required to filter the report appropriately.

The Named Set Builder appears, with our input, as partially shown in Illustration 11.

A confirmation dialog should appear, indicating that Syntax is OK, as depicted in Illustration 12.

The new named set appears in the tree as shown in Illustration 13.

NOTE: The cube must be processed before we can designate it as a data source in the next section.

The Process a Cube dialog appears, as depicted in Illustration 14, with the processing method defaulted to Full Process. Full processing is the only option, as this is the first processing cycle of our cloned cube.

Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as shown in Illustration 15.