Microsoft KB Archive/307945

= INF: How to Carry Balances Forward for Display in a Balance Sheet. =

Article ID: 307945

Article Last Modified on 2/20/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q307945



SUMMARY
Balance Sheets are a common means of reporting cumulative financial data for a distinct time period. This article explains how to use calculated members to provide the necessary functionality.



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:

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:

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:  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.) Create a calculated cell named &quot;Yearly Balances&quot; with the following characteristics:

 Create a second calculated cell named &quot;Quarterly Balances&quot; with the following characteristics:



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:

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

Additional query words: BALANCE MDX CALCULATED CELL OLAP

Keywords: kbinfo KB307945

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.