Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 5, 2006

MDX Numeric Functions: The .Ordinal Function - Page 4

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM