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.
1.
Select File
---> New from the main menu, once again.
2.
Select Query
with Current Connection from the cascading menu that appears next, as
depicted in Illustration 10.
Illustration 10: Create a New Query with the Current
Connection ...
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.
3.
Type (or cut
and paste) the following query into 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.
Illustration 11: Our Second Query in the Query Pane ...
4.
Execute the
query by clicking the Execute button in the toolbar.
The Results pane is, once again, populated by
Analysis Services. This time, the dataset partially depicted in Illustration
12 appears.
Illustration 12: Results Dataset (Partial View) – Report
Parameter Picklist Support
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.
5.
Select File
--> Save MDXQuery2.mdx As ..., name the file MDX047-002.mdx,
and place it in the same location used to store the earlier query
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.
» 