MDX Numeric Functions: The .Ordinal Function - Page 4

Procedure: Use the .Ordinal Function to Generate a Dataset to Support a Report Parameter Picklist

The point of our next effort is to generate the fields we need for report parameter picklist support, this time for the Geography dimension of the cube. The authors / developers have asked specifically for all geographical levels to show up as selections within the parameter picklists for various reports in Reporting Services.

When we venture upon parameter picklist support in an OLAP scenario (at least via this method), we need to provide two main ingredients within the returned dataset: a name that consumers can select at report runtime and the MDX-equivalent, qualified name, which is passed to Analysis Services via placeholders in the underlying dataset query. An index (again, the zero-based numeric value of a given dimensional level), can also be useful for grouping, sorting, and other presentation purposes. As we shall see in the steps that follow, the .Ordinal function is again useful in helping us to meet the business need.

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane, once again) appears in the Query pane.

--MDX047-002 Parameter Picklist Support using .Ordinal;  Display Name
-- for Report Parameter selector - Unique Name for passage to 
-- Analysis Services as MDX-intelligible equivalent.
WITH 
MEMBER [Measures].[LevelNo] 
AS 
   '[Geography].[Geography].CURRENTMEMBER.LEVEL.ORDINAL' 
MEMBER 
   [Measures].[DisplayName] 
   
AS 
   '[Geography].[Geography].CURRENTMEMBER.MEMBER_CAPTION' 
   
MEMBER 
   [Measures].[UniqueName] 
   
AS 
   '[Geography].[Geography].CURRENTMEMBER.UNIQUENAME' 
   
SELECT 
   {[Measures].[LevelNo], [Measures].[DisplayName], 
      [Measures].[UniqueName]} ON AXIS(0) , 
   {[Geography].[Geography].MEMBERS} ON AXIS(1)
FROM 
  [Adventure Works]

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

The Results pane is, once again, populated by Analysis Services. This time, the dataset partially depicted in Illustration 12 appears.

In the returned dataset, we see the columns we have discussed. Of primary importance are DisplayName and UniqueName. While we might certainly simply display the UniqueName to consumers for selection, as well as for insertion into our MDX query. (I see this done often when a solution is developed by a practitioner new to MDX, or perhaps to components of the Microsoft integrated BI solution, particularly when they are attempting to generate the parameter support wholly within Reporting Services, etc.) More user-friendly names (hopefully those chosen for the member captions during development of the Analysis Services components) tend to meet with more consumer acceptance.

The client developers and report authors express satisfaction with the results, and confirm their understanding in the operation of the .Ordinal function within the contexts we have presented thus far. All agree that we are ready to examine the next, somewhat different scenario, which likely has far-reaching potential: the conditional presentation of a value based upon "where we are in a hierarchy" (in this case, where we are in the Date dimension, Calendar hierarchy). We will undertake an example to show how we might approach the presentation of the data containing the "conditional moving average," which is calculated and presented at the month level only, while the pre-existing measure is shown at all hierarchical Date levels down to month in the same results dataset.