cancel
Showing results for 
Search instead for 
Did you mean: 

evdre expansion question

Former Member
0 Kudos

Hi,

I have a requirement to create an evdre input schedule with two dimensions in rows, ACCOUNT and INTCO.

The account dimension should expand using a MEMBERSET of SELF,ALL with the appropriate member set in the CV.

The intco dimension should be selected depending on the account per row with an IF statement (IF(evpro(LEGAL;account;PINTCO)="Y";"INTCO.TOTAL";"I_NONE")

Is this possible? In a non expanding input schedule I can do it, but is it also possible to have the input schedule expand along the account dimension?

Thanks,

Arnold

Accepted Solutions (0)

Answers (2)

Answers (2)

JohnL
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Arnold,

One possible workaround is to use that formula in a CellKeyRange.

In the evDRE keyrange set the CellKeyRangevalue to =EVRNG(P8)

In cell P8, place your formula

=(IF(evpro(LEGAL;"account";PINTCO)="Y";"INTCO.TOTAL";"I_NONE")

You need to change the "account" word to use an Excel key reference which is dynamic. Test it out and you'll see what I mean.

In my test I had the following in cell P8

=IF(J8="CE0001000","Europe_IN","NA_IN")

Give it a try and you'll see what happens in the evDRE range just to right of the data range.

Note that using CellKeyRanges are a drag on performance so please use them carefully.

You can also use an evGET report to do what you want as well but they perform poorly as well.

Goodluck,

John

Former Member
0 Kudos

Hi John,

Let me explain my input schedule in a bit more detail.

My EVDRE in cell A1 references an expansion range with two expansions, both for rows, the first for accounts with a member set of ALL,PARENTAFTER and the second for INTCO with SELF.

This row key range is H17:I118 and the col key range is L15.

Amongst the accounts I see are some with PINTCO = TRUE and some with PINTCO = FALSE. I would like the have the INTCO member, instead of being I_NONE for all accounts, to be INTCO.TOTAL where the account in the same row is PINTCO = TRUE and I_NONE where the account in the same row is PINTCO = FALSE.

So, any formula ro result thereof needs to be in cells I17:I118 and not to the right of the data input area.

I have entered the CellKeyRange as EVRNG(M14) and the formula in M14 is =IF(EVPRO($I$2;H14;"PINTCO")="Y";"INTCO.TOTAL";"I_NONE")

Your solution gives me the correct entry (INTCO.TOTAL or I_NONE) in a column to the right of the data entry area, i.e. column M, however for my input schedule to work it would have to return those values in column I instead.

So, is there a way to get the information from column M to column I?

Thanks,

Arnold

JohnL
Product and Topic Expert
Product and Topic Expert
0 Kudos

I would remove the second INTCO expansion.

Make the PageKeyRange (page key range) set to I_NONE,

And then use the CellKeyRange as an override as follows:

=IF(EVPRO(I2;H14;"PINTCO")="Y";"INTCO.TOTAL";"")

Would that work?

Thanks,

John

Former Member
0 Kudos

But if I remove the INTCO expansion, then my rowkeyrange can only be one column wide, so each data cell is only defined by the account and not the account/intco dimension I am looking for.

Thanks,

Arnold

JohnL
Product and Topic Expert
Product and Topic Expert
0 Kudos

The CellKeyRange and the PageKeyRange will now be used to define the INTCO

Give it a try.

John

Former Member
0 Kudos

Hi,

I actually thought a lot on this.

1. We cannot keep this formula in the memberset. The account will expand to multiple members. So, the formula wont know, which member to be considered fro EVPRO. So, that wont work.

2. We could have kept only account dimension in the expansion. Just in the adjacent column of account expansion, you could have maintained the formula. Then include both the columns in the keyrange. However, this wont work, because there is expansion happening. If we have expansion, then we need to have both the dimensions in the expansion or none of them.

So, I am afraid this will not work.