EVDRE query

[Former Member]

Hi All,

I have an scenario -

In row key range, I need to list down 2 dimensions - PCA(profit centre) and CustomerAcct

• PCA - In parameter table - Build a filtre to list down all the PCA that belongs to a particular Entity(based on property entity of PCA dimension)
• CustomerAcct - This should be derived from property CustAcct of dimension PCA.

How can this scenario be achieved?

Regards,

Swetha

[Former Member · Accepted solution]

Hi Swetha,

Are PCA and CustomerAcct dimensions?

You can have these 2 dimensions in the rows. In the expansion for PCA, you need to have something like "ENTITY = $D$3", where $D$3 is the cell which has the Entity member. You can have a similar filter for the other dimension also.

Alternatively, you can right click on the expansion cell of the dimension and set the filter.

Hope this helps.

[Former Member · Accepted solution]

YES, both PCA and customer account are dimensions.

But, In first row i want to display all the PCA whcih have entity=$X$Y. and in second row i want to display the property CUstAcct of PCA.

The catch is both of this should be in evdre range. In expansion range, you can give  "ENTITY = $D$3" for pCA. But what do i have to specify in expansion range to retreive custacct property of PCA??

Regards,

Swetha

[former_member186338 · Accepted solution]

As far as I know, It's not possible to archive this result using expansion. You can use static template:

1. Expand PCA with the filter

2. Add column and fill it with EVPRO manually

B.R. Vadim

[Former Member · Accepted solution]

you are perfectly right.

BUt

1. filter you can specify in expansion table.

2. what selection will u give for member set for CustomerAccoutn in expansion table?

[former_member210696 · Accepted solution]

Hi Swetha,

You can fetch the PCA members based on custacct property in a blank excel cell of your report (a cell which is not getting used). and then, in the memberset for your CustomerAcct dimension expansion, you can provide a reference to the same cell.

Hope it helps!!

[Former Member · Accepted solution]

in this case also, what selection will u give for member set for CustomerAccoutn in expansion table

[Former Member · Accepted solution]

Hi Swetha,

Do you want to display the PCAs and their corresponding CustAccount? If that is the case, then you can just use the EVPRO function in the report area to fetch the customer property of PCA.

Hope this helps.

[former_member210696 · Accepted solution]

Swetha,

This might not be a good way to achieve what you are saying but still if you may want to try -

Write one more EVDRE definition in a different sheet in your report that will have expansion on

1. PCA and 2. take any other dimension

This is just to fetch all the dimension members of PCA based on property CustAcct.

Take all these dimension members in a single cell (say A1) using excel formula

In the main EVDRE, for member set for CustomerAccoutn, point it to A1.

Hope it helps!!

[former_member186338 · Accepted solution]

1. You can have only one Row expansion (on PCA) and add column manually after expand (don't forget to correct RowKeyRange)

2. Or you can have additional expansion for CustomerAccount with the SELF in MemberSet - to have one Account per line. Then replace this account with EVPRO

[Former Member · Accepted solution]

Nilanjan..your understanding is correct and your answer is also right.

But, this property row needs to be in evdre range. how do u bring this?

If u inlcude both rows in row key range, then you need to specify member set for both the row dimensions.........

[former_member186338 · Accepted solution]

Or, if you want to avoid manual paste formula with EVPRO you can create two EVDE:

1. With one Row Key - PCA, with additional column with EVPRO (this column will not be in the row key range) and with the expansion on PCA based on filter.

2. Static EVDRE, with 2 row keys, where both cells are linked to the corresponding values in the first EVDRE. Add enough rows!

[former_member186338 · Accepted solution]

Read my answers.

[former_member210696 · Accepted solution]

Swetha,

As I and Vadim have already suggested, you may achieve it with the help of 2 EVDRE definition.

Try that once.

[Former Member · Accepted solution]

Hi Vadim,

In your second option, how will you replace the accoutn with EVPRO? When it expands will the EVDRE not delete all EVPRO formula?

[Former Member · Accepted solution]

Hi Swetha,

The function wont be removed. In fact, if you write the formula for the first PCA member, after expansion, the formula will be copied till the last member of PCA.

You can try it.

Hope this helps.

[former_member186338 · Accepted solution]

In my second option:

"2. Or you can have additional expansion for CustomerAccount with the SELF in MemberSet - to have one Account per line. Then replace this account with EVPRO"

You are creating static template:

- First expand to have all PCA filled with some (incorrect!) CustomerAccount in the second cell in the Row Key

- Replace manually incorrect CustomerAccount with =EVPRO(...)

Static template is done, remove expansion range from EVDRE formula!

[Former Member · Accepted solution]

Hi Vadim,

Did the same as per your instructions.

But, when Static template is done and removed expansion range from EVDRE formula (remember that i removed both the row [PCA and custacct] from expansion range)- how will the PCA member set expand?. We would have given  "ENTITY = $D$3" for PCA member set. THis would not expand.

[Former Member · Accepted solution]

I tried out this option also.

When we link 2nd EVDRE to corresponding values in first EVDRE - then do refresh for both sheets- lines in 2nd EVDRE will alter when number of lines differ in each refresh.

Eg: When i select Entity = "India", then 20 PCA would pop up. But when i select ENtity = "china", then only 5 PCA will pop up. In this case, the reference will not be retained in second EVDRE.

[former_member186338 · Accepted solution]

"Static" - by definition means NO expand! Static template is prepared for the fixed set of members (PCA in this case). If you want to give user the ability to select set of PCA members on the fly - then you need the setup with 2 EVDRE.

[former_member186338 · Accepted solution]

What's the issue? You have for example 50 lines in the 2nd EVDRE (max possible lines for PCA set). All Row Key cells are linked to the 50 lines on the 1st EVDRE using Excel formula, that retain absolute reference:

=IF(INDIRECT("Sheet1!E"&ROW())<>"",INDIRECT("Sheet1!E"&ROW()),"")

Where Sheet1 - is the Sheet with 1st EVDRE.

To get results you have to:

1. Expand All - to update members on the 2nd EVDRE

2. Refresh Workbook - to fill the 2nd EVDRE

Message was edited by: Vadim Kalinin Replaced ";" with "," in Excel formula - as in English version of Excel

[Former Member · Accepted solution]

Thanks a lot vadim. I was able to atain the expected layout with your guidance.

Thanks a lot!! Very good explanation ....

Regards,

Swetha