Time Balance and Formula on Summary Time Period

System ignores the formula on summary time period when Time Balance is not "Flow"

For P&L (Profit and Loss) and BS (Balance Sheet) accounts, we set the Time Balance property depending upon the account type. Generally, Flow for P&L accounts and "Balance" for BS accounts.

We had a Measure dimension that mainly had 2 members.The requirement was that for one measure member users wanted to see data as "Flow" (Q1 = Jan + Feb + Mar, Q2 = Apr + May + Jun) where as for another member they wanted to see data as "Weighted Average - Actual_Actual" i.e.

(Weighted daily average, based on the actual number of days in a year; accounts for leap year, in which February has 29 days. In the example, the average for Q1 is calculated: (1) Multiply each month’s value in Q1 by the number of days in the month, (2) Sum these values, (3) Divide the total by the number of days in Q1. Assuming it is a leap year, the result is calculated: (10 * 31 + 15 * 29 + 20 * 31) / 91 = 15)

In this case we decided to have custom formula on summary time period with appropriate condition on measure members. We kept the Time Balance for all the accounts as "Balance" as we normally have.

While testing, it was observed that the formula on summary time period was ignored and Balance (TB Last) data was displayed on summary time period.

When we changed the Time Balance property to "Flow" system displayed the expected output based on the formula defined on summary time period.

Essbase DataExport

We all know about the limitation on the Dataexport command as we do not have much control over the formatting and ordering of columns. But one is sure that this is one of the fastest way to get the output as compared to Report Script.

An interesting observation was when using DATAEXPORTOPTIONS.

I wanted to extract level zero data from all dimensions except one upper level member from measure dimension. Measure hierarchy was something as follows and wanted to extract value of Balance which is upper level member.

Balance
 - Calculated Balance (+)
 - Adjusted Balance (+)

To my surprise, following code gave the required output:

SET DATAEXPORTOPTIONS
{
DataExportLevel LEVEL0;
}

FIX("Balance")

  DATAEXPORT "File" "," "output1.out";

ENDFIX

Based on this, one can extract upper level member even when DataExportLevel is set to LEVEL0.