INF: How to Carry Balances Forward for Display in a Balance Sheet. (307945)

MORE INFORMATION

NOTE: This example uses the Sales cube of the FoodMart 2000 sample.

Launch the MDX Sample Application, connect to the OLAP Server, select the FoodMart sample database, and execute the following MDX query:

SELECT {[STORE].[ALL STORES].[USA]} ON COLUMNS,
   MEASURES.ALLMEMBERS ON ROWS
   FROM SALES
				

The values returned are as follows:

	USA
Unit Sales	266,733.00
Store Cost	225,627.23
Store Sales	$565,238.13
Sales Count	86837
Store Sales NET	339,610.90
Profit	339,610.90
Sales Average	6.51

The values returned from the query are summed values, and do not reflect the most recent value (which would be the desired result in a balance sheet). To return the most recent values for the USA member for calendar year 1997, execute the following MDX query:

SELECT {[STORE].[ALL STORES].[USA]} ON COLUMNS,
   MEASURES.ALLMEMBERS ON ROWS
   FROM SALES
   WHERE (TIME.[1997].[Q4].[12])
				

The values returned by this query are as follows:

	USA
Unit Sales	26,736.00
Store Cost	22,746,13
Store Sales	$56,965.64
Sales Count	8717
Store Sales NET	34,219.51
Profit	34,219.51
Sales Average	6.54

These values are the most recent values for the time period in question, and would be the desired result for a balance sheet.

To present cumulative balance values in the manner typically expected on a balance sheet, follow these steps:

1. Open the Time dimension for editing and set the All Level property of the time dimension to NO. (If the All Level property is set to YES, the values of the children are automatically rolled up and summed.)
2. Create a calculated cell named "Yearly Balances" with the following characteristics:
   
   

   Property	Value
   Calculation SubCube	[Time].[Year].MEMBERS
   Calculation Condition	Applies to entire calculation subcube.
   Calculation Value	IIF(IsEmpty(Time.CurrentMember.Children(3)), IIF(IsEmpty(Time.CurrentMember.Children(2)), IIF(isEmpty(Time.CurrentMember.Children(1)), IIF(IsEmpty(Time.CurrentMember.Children(0)), null,Time.CurrentMember.Children(0)), Time.CurrentMember.Children(1)), Time.CurrentMember.Children(2)), time.currentmember.children(3))

3. Create a second calculated cell named "Quarterly Balances" with the following characteristics:
   
   

   Property	Value
   Calculation SubCube	[Time].[Quarter].MEMBERS
   Calculation Condition	Applies to entire calculation subcube.
   Calculation Value	IIF(IsEmpty(Time.CurrentMember.Children(2)), IIF(isEmpty(Time.CurrentMember.Children(1)), IIF(IsEmpty(Time.CurrentMember.Children(0)), null,Time.CurrentMember.Children(0)), Time.CurrentMember.Children(1)), Time.CurrentMember.Children(2))

Launch the MDX Sample Application, connect to the OLAP Server, select the modified FoodMart 2000 sample database, and execute the following MDX query:

SELECT {[STORE].[ALL STORES].[USA]} ON COLUMNS,
   MEASURES.ALLMEMBERS ON ROWS
   FROM SALES
				

The values returned by the query, after adding the two calculated cells, are as follows:

	USA
Unit Sales	26,736.00
Store Cost	22,746,13
Store Sales	$56,965.64
Sales Count	8717
Store Sales NET	34,219.51
Profit	34,219.51
Sales Average	6.54

These values are the most recent values for the time period in question, and represent the desired result for a balance sheet.