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 Jul 26, 2004

MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist - Page 3

By William Pearson



Procedure



1.  Right-click the new Exp_Finance cube



2.  Select Edit ... from the context menu that appears, as depicted in Illustration 5.




Illustration 5: Select Edit from the Context Menu



The Cube Editor opens.



3.  Right-click the Calculated Members folder within Cube Editor.



4.  Select New Calculated Member ... from the context menu that appears, as depicted in Illustration 6.




Illustration 6: Select New Calculated Member from the Context Menu

The Calculated Member Builder opens.

5.  Type the following into the Member name box:

RS_PX_StoreGeogPLName_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, that it has been created to support picklists ("PX"), and that this particular calculated member exists to generate a picklist display name ("PLName") in our reports for the various levels of the Store dimension.

6.  Type the following MDX into the Value Expression section of the Calculated Member Builder:

Space([Store].Currentmember.Level.Ordinal * 6) + [Store].CurrentMember.Name

The MDX expression above returns, via our calculated member, a display name that we will use to generate the picklists that the information consumers see, as part of 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 1.

Expression

Meaning

Space([Store].Currentmember.Level.Ordinal * 6)

The Space() VBA function is used together with the level ordinal property of the current member to generate a "space size." We then multiply that size value by six (6).

Our objective here is cosmetic - we are simply making the geographical levels of the Store dimension appear more intuitively hierarchical for the consumers, by "indenting" each of the various levels of the Store dimension in accordance with its levels in the hierarchy.

+ [Store].CurrentMember.Name

This section of our expression actually generates the name of the current member ...


Table 1: Expression Components Summary

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 depicted in Illustration 7.


Illustration 7: Calculated Member Builder with Complete MDX Expression (Compressed View)

7.  Click OK to close the Calculated Member Builder.

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

8.  Right-click the Calculated Members folder within Cube Editor, once again.

9.  Select New Calculated Member ... again, from the context menu that appears.

The Calculated Member Builder opens.

10.  Type the following into the Member name box:

RS_PX_StoreGeogMSASName_Hier


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


















Thanks for your registration, follow us on our social networks to keep up-to-date