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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 17, 2003

MDX in Analysis Services: Intermediate Concepts - Part 1 - Page 5

By William Pearson

13.  Select the Store dimension within the Parent Dimension property, as shown below:

Illustration 6: Select Store as the Parent Dimension

We see from the dynamic caption at the bottom of the Properties pane that we are selecting "the dimension that contains the calculated member."

14.  Press Enter.

15.  Select the Value property on the Basic tab, once again.

16.  Click the ellipsis button ("..") to raise the Calculated Member Builder dialog.

17.  Type the following into the Value Expression box.

[Store].[Store Country].[USA]/12

18.  Click OK, and compare the result set to that pictured in Illustration 7.

Illustration 7: The Value for MyCalcMem2 Reflects the Entered Formula

19.  Select 1998, in the filter field for the Year dimension atop the Data Viewing pane. The Data Viewing pane presents the results set shown below.

Illustration 8: Results Set for Year 1998

The amounts are now negative, as the sample Budget cube only reflects actual expense items in its measures for 1998 (the "current year" of the cube). We will see this more clearly as we work with lower levels of the Account dimension hierarchy in later lessons. (Our focus here is not the composition of the Net Income number, but the interaction of the various cubes members between themselves.)

20.  Swap (by dragging and dropping, as we did before) the Account dimension from the top pane down to replace the Measures dimension in the columns axis.

The value of MyCalcMem2 is automatically filled with the contents of the corresponding Net Income cell (the Assets and Liabilities amounts are not populated in the Budget cube). The value for the Net Income member of the current account has been retrieved and displayed.

21.  Swap the Store dimension in the rows axis with the Measures dimension.

Now let's specify MyCalcMem2 as the dimension for which we are retrieving values. (This became possible when we made Store the Parent dimension of MyCalcMem2.)

22.  Select MyCalcMem2 within the Store dimension filter box.

The results set appears as below.

Illustration 9: The Results Set, Filtering for MyCalcMem2.

We can see, in each cell, the value retrieved by the expression that composes MyCalcMem2. For Net Income, whose value is reflected as ($18,153.64) (again, negative because only expenses are included in the cube), the current member of the Measures dimension ([Amount]) is derived in the context of the row, while the current member of the Account dimension ([Net Income]) is derived in the context of the column. The filter fields in the top pane define the context of the dimensions with which they are associated. MyCalcMem2 represents the current member of the Store dimension, pointing to the [USA] member through the expression ([USA]/12), which defines the calculated member.

MS SQL Archives

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