Excel formula in evdre

[Former Member]

I have created a report by using evdre, the column is static with time dimension and the row is expand with account dimension's 20 account numbers, there are some calculation rows between the account numbers, however when I run the report by click " expand all", the excel formula is gone. although I can use multiple evrng to exclude those calculation rows, but I remember this can be done in my previous BPC M project. Did I missing anything?

Is evdre still working if we do not include the expansion parameter in evdre such as =EVDRE($I$2,A4:B11), here I delete the expansion parameters,

Edited by: Jianbai on Feb 23, 2011 1:51 AM

[Former Member]

Currently I am using the way of making the columns static and row expansion with mutiple evrng functions seperate by | and leave the excel function between them, but I just want toknow if can achieve the same withoutusing the mutipel range seperate by |

[Former Member]

Hi ,

In that Case You give entire Data Region in The range and make sure that Against Formulae Columns you need to take the KEY Values out. So that BPC will not send those values when Sending Data.

If it is reports u can also do the same.

Chaithanya

[Former Member]

This is a Sample of what i have explained .

First 2 Rows are KEY Range

Next 2 Rows are Description . Here u can see the TOTAL Col Added manually and used Native SUM Functions here.

So in this way u can have single Range provided in Column Range and Excel formulaes are also working fine.

2011.DEC 2012.JAN 2012.FEB 2012.MAR

REV REV REV REV

2011.DEC 2012.JAN 2012.FEB 2012.MAR 2011.TOTAL

Revenue Revenue Revenue Revenue Revenue

13,896 13,896

14,000 14,000

HOPE THIS HELPS

Chaithanya

[boo_yenli]

Hi,

If you could define the properties or any expansion memberset for the list of accounts before and after the excel formula row(s) , probably you could try using pipes "|" to separate the two ranges and insert your excel formulas in between there.

Hope this helps.

Thanks,

Yen Li

[Former Member]

Well, I did some test without the expansion parameters, if you change the current view, the data selection criteria will change correspondingly, which means that we have to have expansion parameters either in columns or in rows.

I am ok with that since I can only keep row expansion with hard code value in the member set cell, but the only thing bother me is that I have some excel formula and ev function in the data cell which going to be cleared after the expansion, how can we avoid that?

[Former Member]

Hi,

You can make the Column Static. That is suppose you have Time in Col. in the Expand Parameters Jus clear those Col parameters, and then Manually Type the KEYS of Column and give Ranges only to those column which u want the expansion to happen and keep all other coloumn out of Range.

In this way your problem might get solved. The same we have used for our Templates where we want to Display 60 Months Data at a time.

But Here you have to remember that when you are HARDCODING COL KEYS , you have to keep the formulae so that the Values are taken from Current View and the Changes to the time period is also automatically Taken. Here you can use REPLACE ( NATIVE EXCEL ) formulae for First Column and then after EVTIM with Offset 1. So venthough you are making column static, The KEY VALUES are taken from Current View.

This is little bit difficult to explain , but i hope i have given a basic understanding of how to go about it.

Hope This helps

Chaithanya

[Former Member]

Thanks, can we still use excel formula or other ev function in evdre with expansion?

[former_member190501]

Hi,

EVDRE will work with out expansion range(parameter). Any how you are running report for only 20 fixed accounts , so you can make EVDRE static in col and row by removing third parameter from EVDRE.

hope it works...

regards,

Raju