EPM Fucntionality - using variables to calculate the sum of two fields.

[Former Member]

Hello Everyone !

I have a requirement where i have two GL values,for which i have to calculate the sum and display in a seperate field.

later, i will hide the two GLs and display only the sum as a part of my report output.

How can this be done - please suggest....

Example :

                                                  A                    B                         C

                                             Salary               Bonus                  Total Staf expense

Permanent Staff                   56000                10000                  66000

Temporary Staff                   12000                 3500                    15500

How can i get the sum in c column and later if i hide column a and b, display C only.

I thought of requesting the business to add a new parent node, but this has to go through a series of approval process it seems, hence, is there any other alternative method of achieving this please?

Regards,

Vicki

[Former Member · Accepted solution]

Hi Vicki,

You can use local member in the edit report.

simply using excel formula to add those 2 fields together in the local member formula field.

Andy

[Former Member · Accepted solution]

Thank you Andy, but i dont want the source two columns to be available in the report, nor do i want to hide it, cause, when i try to print the report the hiden columns might create problems.

Is there any other way of doing this please ?

The other options which comes to my mind are :

1. have another report which fetches the two columns of data, from this report pass these values to the target fields in the final report - is this possible?

2. can we use macros? is there any documentation/sites on macros, which could provide step by step guide?

Regards,

Vicki

[Shrikant_Jadhav · Accepted solution]

Hi Vicki,

If you report structure is static (number of row/ column are fixed ) then you use first approach i.e. create one report, do the sum and hide it, in other sheet refer values from hidden sheet. You can use vlookup.

For macro just simply do search on google, you will find related links.

Or you can use EPMRetriveData function in one local member to display addition of two.

But performance will be affected.

Shrikant

[Former Member · Accepted solution]

Hi Vicki,

There are other options, it all depends on your overall solution.

1. create a calculated member in Rules -> member Formulas, that will sum those members for you during run time, you need to do some performance testing to see if the reports are acceptable,

2. create a new member, use logic scripts to sum 2 values for you and post into the new member.

always try to use hierarchy first, if that is not possible then look into logics or calculated member, I would consider marcos as the last option.

Andy

[former_member186338]

Hi Vicki,

"I thought of requesting the business to add a new parent node, but this has to go through a series of approval process" - new parent node is the correct approach! The other alternatives will result in errors and additional complexity.

Vadim