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 22, 2003

MDX in Analysis Services: Measuring Change over Time - Page 3

By William Pearson

Now that we have the number of units sold, let's create a calculated member that we will use as the vehicle to illustrate how MDX can help us support time-based analysis. But first, let's vary our requirement from the last lesson and change to a view of expenses, versus the current revenues perspective.

14.  Select Total Expense in the filter field for the Account dimension atop the Data Viewing pane.

The contents of the Data Viewing pane now appear as shown in Illustration 7.

Click for larger image

Illustration 7: The Data Viewing Pane Contents after Changing the Accounts Filter (Compressed View)

15.  Select Insert -> Calculated Member from the main menu once again.

The Calculated Member Builder appears.

16.  Type Avg Total Cost Per Unit into the Member Name box. In the Value Expression box, type the following expression:

[Amount]/[Units Sold]

17.  Click OK to accept the expression entered.

The Data Viewing pane appears as shown in Illustration 8.


Illustration 8: The Total Expense Amount with the Avg Total Cost per Unit Calculated Measure

We note that, while all else appears to be working reasonably (at least from a cursory level), the Avg Total Cost Per Unit for Canada and Mexico is suffering from the "divide by zero" malady we discussed in Lesson Three: Intermediate Concepts - Part 2. Let's handle this "undefined" condition by adapting the expression behind our Avg Total Cost Per Unit calculated member to handle the occasions where we are presented with zeros in the member denominators.

18.  Right-click the Avg Total Cost Per Unit calculated member.

19.  Select Edit from the context menu that appears

The Calculated Member Builder reappears.

20.  In the Value Expression box, replace the existing expression with the following:

IIF(IsEmpty(([Units Sold],[Store].CurrentMember)), 0, [Amount]/ ([Units Sold], [Store].CurrentMember))

 

21.  Click OK to apply the changes.

The result set that is retrieved should appear as shown in Illustration 9.


Illustration 9: The New Result Set from the Data Viewing Pane

(Remember to take advantage of the Check button to make sure that the correct number of parentheses, etc., is placed anytime you work with the Value Expression in the Calculated Member Builder.)

We obtain the desired results for Avg Total Cost Per Unit for the Stores depicted, noting that the Canadian and Mexican stores now display zeros instead of confusing results, both at this and the various drill-down levels.

Let's make a few cosmetic adjustments while we're here, before moving on to our primary focus.

22.  Ensure the Avg Total Cost Per Unit calculated member is selected in the Cube tree.

23.  Select the Basic tab of the Properties pane, which appears underneath the Cube tree, if not already selected.

24.  Ensure that the Measures dimension is selected for the Parent Dimension property.

25.  Click the Advanced tab in the Properties pane.

26.  Select the Format String property of Avg Total Cost Per Unit.

27.  Click the drop-down arrow.

28.  Select Currency as the format for the string.

The Advanced tab in the Properties pane appears as shown in Illustration 10.


Illustration 10: The Format String Setting in the Advanced Properties Pane

29.  Select Units Sold in the Calculated Members folder for the Budget cube.

30.  Select the Basic tab in the Properties pane.

31.  Select the Parent Dimension property of Units Sold, on the Basic tab.

32.  Ensure that the Measures dimension is selected (via the drop-down arrow).

33.  Click the Advanced tab in the Properties pane.

34.  Select the Format String property of Units Sold.

35.  Click the drop-down arrow.

36.  Select #,# (whole units with thousands separator - no decimal placed) as the format for the string.

37.  Press Enter.

The updated Data Viewing Pane appears as shown in Illustration 11.


Illustration 11: The Dataset with New Formatting Refinements

We see both the Units Sold and the Avg Total Cost Per Unit calculated members appear, complete with our formatting specifications.

The forgoing steps have served as both preparation for primary objective of our lesson and a hands-on review and practice of some of the things we have learned about calculated members before this lesson. We will move now to our exploration of the MDX time functions.



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