2000 Member for EPMDimensionOverride Function

[Former Member]

Dear BPC Friends,

I am building a report in BPC 10.1 (Standard) NW. The report has two sheets (i.e Sheet 1 & Sheet 2).

Sheet 1  has Cost_Center (Base Member) in Row and Time and Category in Column. This also has drop down column of YES/NO (next to Cost_Center dimension). This column is used to select any Cost_Center  that the user want to pull in Sheet 2 report.

Sheet 2 has Cost_Center and Account in Row and Time and Category in Column. The Cost_Center  member will come from drop down column in Sheet 1 and Account dimension has fixed  members.

The Cost_Center expansion (in Sheet 2) has be written using EPMDimensionoverride function.

The problem is - The user can select one Cost_Center or all Cost_Center (aprox 3000 Cost Centers). If  the user want to select 2000 Cost Center then the challenge how to pull 2000 members in Cost_Center EPMDimensionoverride function.

Kindly Advice,

Rahul

[former_member186338 · Accepted solution]

I think that in general the idea to select 2000 costcenters using dropdown column is bad Use normal member selector...

[Former Member · Accepted solution]

Hi Vadim,

I agree with you that drop down for 2000 is not a good idea. But  this is the client requirement and I suggested them for member selector,  but not very keen on this solution.

Let me know if you know how to put these 2000 in EPMDimensionOverride Function.

Thanks,

Rahul

[former_member186338 · Accepted solution]

No way to use epmdimensionoverride! By the way if next time the client will ask you to make 2x2=5 in the calculations you will also support him ?

[Former Member]

Hi Rahul,

Could you please explain the scenario behind these sheets? Why the user wants to pass the cost center from 1st sheet to next?? Functional logic.....Maybe this can be designed other way too. How have you provided the drop down for Yes/NO in first report? VB macro?

Regards,

JP

[Former Member]

Hi JP,

The reason user want to pass the Cost_Center form Sheet 1 to Sheet 2 is that  in sheet 1 they can select any Cost Center (our of aprox 300 Cost Center)  and then in the  Sheet 2  user can pull data from Account dimension  for these selected  Cost Center (in sheet 1).

So sheet 1 has only Cost Center dimension in row axis, and

sheet 2 has Cost Center in outer dimension and  Account dimension in inner dimension in row axis. The Cost Center in this sheet come from YES/No drop down in Sheet 1.

The drop down is provided using data validation.

Thanks

Rahul

[Former Member]

HI Rahul,

This type of report building in BPC is not possible/quite complex. Everything is not possible in a tool, there are limitations, you have to explain this to client even though it is Excel!

Otherwise you would have to build a very complex macro for this....

Regards,

JP