Member Formula Solution Gap

[former_member544285]

Hi Experts,

We are currently faced with design dilemma as follow:

Requirement:

Within Profit and Loss Hierarchy (ACCOUNT dimension), the report need to show foreign exchange gain / loss in hierarchy as illustrated below

Net Profit

Total Revenue

Foreign Exchange Gain

Total Expense

Foreign Exchange Loss

Foreign Exchange Gain/Loss are determined by several base level accounts. If the total of those accounts is less than 0, then the total value must be shown in line "Foreign Exchange Gain", but if the total of those accounts is more than 0, then the total value must be shown in line "Foreign Exchange Loss"

Current Approach:

We tackle the requirement by using member formulas:

Foreign Exchange Gain:

IIF([AGM24401000] + [AGM24402000] + [AGM24403000] + [AGM24404000] + [AGM24405000] + [AGM24406000] + [AGM24407000] + [AGM24499000] < 0 , ([AGM24401000] + [AGM24402000] + [AGM24403000] + [AGM24404000] + [AGM24405000] + [AGM24406000] + [AGM24407000] + [AGM24499000]) * -1, 0) 

Foreign Exchange Loss:

IIF([AGM24401000] + [AGM24402000] + [AGM24403000] + [AGM24404000] + [AGM24405000] + [AGM24406000] + [AGM24407000] + [AGM24499000] > 0 , [AGM24401000] + [AGM24402000] + [AGM24403000] + [AGM24404000] + [AGM24405000] + [AGM24406000] + [AGM24407000] + [AGM24499000], 0) 

Implementing this approach has exposed us with several problems:

• Net Profit not containing correct value since the member formula will not be rolled up (Net Profit is used within some Account Based Calculations)

       Solution: Include all subset accounts directly to Net Profit

• In report, the Total Revenue and Total Expense does not roll up Forex Gain/Loss value

       Solution: Activate Calculate Parent in Hierarchy

After implementing "Calculate Parent in Hierarchy", we are faced with another issue: Because in column axis we have ENTITY dimension which is also in hierarchy, it calculate the ACCOUNT line item using formula from children ENTITY member. Illustration:

ACCOUNT	CHILD ENTITY 1	CHILD ENTITY 2	PARENT ENTITY
Net Profit	=Total Revenue + Total Expense	=Total Revenue + Total Expense	=Child Entity 1 + Child Entity 2
Total Revenue	=Forex Gain	=Forex Gain	=Child Entity 1 + Child Entity 2
Forex Gain	1000		=Child Entity 1 + Child Entity 2
Total Expense	=Forex Loss	=Forex Loss	=Child Entity 1 + Child Entity 2
Forex Loss	0	2000	=Child Entity 1 + Child Entity 2

All is good except that the requirement is for the Parent Entity to show Forex Gain = 0 and Forex Loss = 1000 (2000-1000).

Now we are forced with only 1 option to tackle this requirement with the current setup: Using macro to calculate PARENT ENTITY.

Any other suggestion would be very appreciated as we try to avoid macro for calculation in the hierarchy

Best Regards,

Hendry

[former_member186338 · Accepted solution]

Hi Hendry,

What about script calculation of the Forex Gain/Loss and placing the result in the correct place of normal hierarchy?

Vadim

[former_member544285 · Accepted solution]

Hi Vadim,

Script calculation is one solution that we're seriously looking at right now. This is the complete axis of my report currently:

Row Axis:

ACCOUNT

INTERCO

Column Axis:

TIME  (To show multiple period)

ENTITY (To show multiple entity)

AUDITTRAIL (To show Input, elimination, and consolidated separately)

GROUP (To show multiple consolidation group separately)

ENTITY Illustration:

TOP_PARENT

ENTITY 01

ENTITY 02

PARENT 01

ENTITY 03

ENTITY 04

PARENT 02

ENTITY 05

ENTITY 06

GROUP Illustration:

TOP_GROUP

GROUP 01

GROUP 02

AUDITTRAIL Illustration:

TOTAL

INPUT

ELIMINATION

ADJUSTMENT

Column Axis Illustration:

TIME

TIME

TIME

TIME

TIME

TIME

TIME

TIME

TIME

TIME

TIME

TIME

TIME

TIME

TIME

ENTITY 03

ENTITY 04

PARENT 01

PARENT 01

PARENT 01

ENTITY 05

ENTITY 06

PARENT 02

PARENT 02

PARENT 02

ENTITY 01

ENTITY 02

TOP_PARENT

TOP_PARENT

TOP_PARENT

INPUT

INPUT

ELIMINATION

ADJUSTMENT

TOTAL

INPUT

INPUT

ELIMINATION

ADJUSTMENT

TOTAL

INPUT

INPUT

ELIMINATION

ADJUSTMENT

TOTAL

GROUP 01

GROUP 01

GROUP 01

GROUP 01

GROUP 01

GROUP 02

GROUP 02

GROUP 02

GROUP 02

GROUP 02

TOP_GROUP

TOP_GROUP

TOP_GROUP

TOP_GROUP

TOP_GROUP

The challenge is actually to show different result in the consolidated level. Below is an example of desired result:

ACCOUNT	ENTITY 03	ENTITY 04	PARENT 01	PARENT 01	PARENT 01
	INPUT	INPUT	ELIMINATION	ADJUSTMENT	TOTAL
Net Profit	1000	-2000	0	0	-1000
Total Revenue	1000	0	0	0	0
Forex Gain	1000	0	0	0	0
Total Expense	0	2000	0	0	1000
Forex Loss	0	2000	0	0	1000

To achieve this requirement, I may need to add invisible AUDITTRAIL to calculate the TOTAL so it could show correctly in the report.

I also need to put the script for the Forex Gain / Forex Loss on every calculation point:

1. Right after Data Input

2. Right after Consolidation

3. Right after Journal

This could considerably impact our performance just to calculate the forex gain/loss at every turn of the data, but this will just work. Could you please share any idea to improve this solution?

Regards,

Hendry

[former_member186338 · Accepted solution]

Some general ideas:

1. Never use member formulas to simulate hierarchy aggregation - will result in "creative" accounting

2. Net Profit, Total Revenue/Expense, etc has to be a normal nodes aggregating normal sub nodes and base members. AGM24401000,AGM24402000,AGM24403000,AGM24404000,AGM24405000,AGM24406000,AGM24407000,AGM24499000 has to be under Net Profit, but not under Total Revenue/Expense.

3. For sure script calculations of Forex Gain/Loss (base members) has to be marked with some AUDITTRAIL ID and the adjustments has to be balanced: SUM(-[AGM244xxxxx])=[Forex Gain/Loss]

4. Data Input/Jornal - default.lgf, Cosolidation - DM package

Vadim

[former_member544285 · Accepted solution]

1. I learned this the hard way

2. To my understanding if I put Forex Gain and Forex Loss as normal base node which data are calculated by script, I don't have to put AGM24401000, AGM24402000, etc to the hierarchy anymore, do I?

Thanks,

Hendry

[former_member186338 · Accepted solution]

2. It's better to put it under Other node:

Net Profit

     Total Revenue

          ...

          Forex Gain

     Total Expense

          ...

          Forex Loss

     Other

          AGM24401000

          AGM24402000

          ...

To have Net Profit always correct!

With INPUT AUDITTRAIL you will see some figures in AGM244xxxxx, calculated adjustments will be with FXADJ AUDITTRAIL. INPUT+FXADJ will result in zero for AGM244xxxxx

Vadim

[former_member186338 · Accepted solution]

How to write fast default.lgf that will calculate sum of the list of members and store the result in some member:

1. Create new property of the ACCOUNT dimension: FXCALC

2. For AGM24401000, AGM24402000, AGM24403000, AGM24404000, AGM24405000, AGM24406000, AGM24407000, AGM24499000 fill this property with the ID of the first account in the list AGM24401000 (or any other unique ACCOUNT ID).

3. When user send data in the input template the scope for ACCOUNT dimension can contain zero or number of members from the list and other members.

4. Use the following code:

*SELECT(%C%,[FXCALC],ACCOUNT,"[ID]=%ACCOUNT_SET% AND [FXCALC]=AGM24401000")

//%C% will contain number of AGM24401000 strings if any of the accounts in the list is changed.

//Or %C% will be empty

*SELECT(%CALC%,[ID],ACCOUNT,"[ID]=%C%")

//%CALC% will contain single AGM24401000 string or will be empty

*FOR %CALCULATE%=%CALC% //loop will be executed once or not executed at all

*XDIM_MEMBERSET ACCOUNT=AGM24401000,AGM24402000,AGM24403000,AGM24404000,AGM24405000,AGM24406000,AGM24407000,AGM24499000

*WHEN ACCOUNT

*IS *

*REC(EXPRESSION=%VALUE%,ACCOUNT=FXLOSS)

*ENDWHEN

*NEXT

Vadim

P.S. Then you can analyze sign of the value in FXLOSS and based on sign move to FXGAIN...

[former_member544285 · Accepted solution]

Thank you Vadim,

I will be using account based calculation for the forex gain/loss, but basically we got the same picture on this.

Regards,

Hendry

[Former Member]

Hi Hendry,

I had a approach for this which will not effect your real FX Gain/Loss account .

For your requirement If you want to show whether your FX is totally gain/Loss & what is the gain/loss amount you can do that using Script logic by adding 1 Dummy Account member & 1 Dummy Entity member

Assume

FX G/L = Dummy Account member

EntityFX = Dummy Entity member under the Parent Entity1

*WHEN ACC

*IS FX GAIN

*WHEN ENTITY

*IS BAS(ENTITY1)

*REC(EXPRESSION=%VALUE%-[ACC].[FX LOSS],[ENTITY].[ENTITY1],ACC=FX G/L,ENTITY=ENTITYFX)

*ENDWHEN

*ENDWHEN

The result would be as below.....

Note:-

Entity Hierarchy should be as below

ENTITY1

     CHILD1

     CHILD2

     ENTITYFX

Account Hierarchy Must be as below

NETPROFIT

     TOTAL REVENUE

                         FX GAIN

      TOTAL EXPENSE

                          FXLOSS

FX G/L

Hope it would be helpful but not exactly meet your requirement.

Regards,

Aravind.

[former_member544285]

Hi Aravind,

Thank you for your input, but I think most client would not accept dummy entity in their master data for the sake of reporting. Especially in consolidation implementation where they would see dummy entity in ownership manager, control monitor, consolidation monitor, etc).

Regards,

Hendry