Dimension property Question for reporting

[former_member182305]

Hello experts,

I have a question regarding setting a dimension as other dimension property in a EPM report.

If we have a Cost Center and I want to konw to wich Profit Center it belongs it is possible using Dimension property

The procedure it is Assign a propety called Profit Center in Dimension Cost Center and then set the Profit Center as Cost Center dimension, right?

I have a question, is it possible doing it the other way, lets say a Profit Center has multiple Cost Center, and what I want to select is the profit Center not the Cost Center, and bring just the cost Center that are part of that profit Center?

Is there a way to do it?

Thanks for the posts.

Best Regards.

[former_member186338 · Accepted solution]

Simply use EPMDimensionOverride for Executive with filter by BANK property!

[former_member182305 · Accepted solution]

Would that be using VBA?

[former_member186338 · Accepted solution]

What for to use VBA???

[former_member182305 · Accepted solution]

Ohhh I see....

The Bank is a selection but for instance if I select to dispaly a set of BANKS the formula EPMDimensionoverride won´t work it will work if it is just one bank, correct??

Regards.

[former_member186338 · Accepted solution]

First - selection of multiple bank was not mentioned in your original description 🙂

Second: you can have multiple property values like:

=EPMDimensionOverride("000";"EXECUTIVE";"BANK=BANK1 OR BANK=BANK2")

String like: BANK=BANK1 OR BANK=BANK2 OR ...

Can be combined using Excel formulas if you have a list of BANK ID's in some column (extra hidden report)

P.S. Instead of extra hidden report VBA can be used to create the same string 🙂

[former_member182305 · Accepted solution]

Hello Vadim,

Thank you very much for your post, you are right about the first comment, I asked "how to" if I select the bank, but in the image I showed 2 BANKS Id, but it was my mistake.

About the second thing, if you I use OR, and I have in my report for Instance all the BANK dimesion members, all the Executives will be shown for each bank, right, since the condition for Executive property BANK is fullfilled(They are parte either of BANK 1 or BANK 2 or BANK 3 or BANK n, righ?

Anyways your comments were very helpful, I am making your answer as RIGHT.

How I solve it was the way explained in previous comments :D. Set Bank as property, call banks related to executive property BANK and set bank as outer dimension and sort by ID. That way in the report seems that bank is the main Dimension.

Thanks again for all your help and time.

Best Regards, Guru.

[former_member186338 · Accepted solution]

In general the relation between bank and executive has to be one-to-many (each executive has to be related to single bank using property). To show all banks you have to show all executives with not empty bank property.

P.S. And for sure - sort by bank ID

[former_member186338]

Sorry, but not clear! Please explain again what do you want to achieve! Report sample, please!

[former_member182305]

Thanks for the anwer Vadim,

I was trying to give an example for the requirement, but actually the real scenario is I have dim BANK and dim Executive. If i set a property for DIM Executive called BANK in the reporte I we will know what is the bank for each one of the Executives as in the next example:

EJECUTIVO = Executive

Sucursal = BANK

The report looks like:

So I have in Columnt F (Bank) the Bank Id of each Executive

BUT for that the selection needs to be By executive.

What I want to achive is:

Instead of Select the Executive, selectiing the BANK and the BANK should bring all the executives that are part of that BANK (Dimension Member).

I tried to include the executives separated by ";" in the property Executive in DIM BANK, but didin't work. The last thing I was thinking was: to set an H2 with all the Executives per BANK in a Hier and asign that Hier to DIM Bank in Property Executive; it kind of work bc it showed the hier but not at base level, I need it at base level to write records.

I was reading that a VBA should be developed to achieve that requirement,

[former_member182305]

I did a little trick 😄 to avoid VBA :D.....

Since in my Bank I have certaing Executives, and an executive belongs to just one BANK:

1. I set the property bank on each Executive

2. Set Bank as Executive property "Bank"

3. Sort by Bank and Bank is my outer dimension.

The report seems like each bank has just the Executives that belong to that bank 😄

Regards