BAS() in combination with EPMDimensionOverride()

[Former Member]

Hi All,

I do something wrong probably, but i don't know what Hopefully, you experts can help me further.

I have this formula running in my BPC report, and works for the members directly below the node in B4.

• =EPMDimensionOverride("000";"E_BUSINESS";"PARENTH1<>"&B4&"")

So now it's excluding the members below the node in B4, but it should exclude all base members below that node, so I want to try BAS() to achieve that i created this:

• =EPMDimensionOverride("000";"E_BUSINESS";"PARENTH1<>BAS("&B4&")")

But this doesn't work... so my questions, does anyone know, how to exclude basemembers correctly?

Many thanks!

[Former Member · Accepted solution]

Hi,

How many levels do you have below that hierarchy node? Single or multiple? Your solution works only for single level....Can you show the desired report with an excel layout and the hierarchy for better understanding?

Regards,

JP

[Former Member · Accepted solution]

Hi Former Member

This is what we want to achieve:

[former_member195154 · Accepted solution]

Hi There,

Can you determine which one of the duplicate post you want to keep as I have seen answers to both of them?

Regards

Folu

[Former Member · Accepted solution]

please keep this one.. their were some problems with the Forum this morning.. sorry for this..

[Former Member · Accepted solution]

Hi,

Try this formula =EPMDimensionOverride("000","E_BUSINESS","DEP("&B4&")")

This will give you only Sub business unit1.1 and 1.2. This is what you require, right?

Regards,

JP

[Former Member · Accepted solution]

Hi,

How you are giving selection for B4 cell? 

Are  you using EPMcontext function for selection or any other way? If you are using EPMcontext then why you are using override no need to use override function. you can achieve through standard BPC functionality.

simply in selection use relationship as children of context member.

please check below screenshot.

Still if you want override then JP has given one approach another workaround is you can you use parenth property.

=EPMDimensionOverride("000","E_BUSINESS","PARENTH1="&B4)

Hope this will solve your problem now

[Former Member · Accepted solution]

HI JP,

Thanks, this indeed selects the members below 1.1 and 1.2... but now still the question is open, how to exclude them?

The DEP() function retrieves the nodes (1.1 and 1.2) as i can see. But i want to exclude the base members below 1.1 and 1.2.

So my question is how to point via EPMDimensionOverride() to Business Unit 1.0 and then EXCLUDE the members below (in 1.1 and 1.2)

Many thanks

[Former Member · Accepted solution]

Many thanks BAS() but i don't want to include those members, i want to exclude them... any idea how to deal with this request?

[Former Member · Accepted solution]

Hi,

Please confirm are you using context member then only that will work else you need to use override only.

As per your screen shot you want parent level members and you don't want show any base level members of selected node.

If you see my screen shot it is showing only parent node not the base level of top node.

With which approach you want go Override or standard?

If you want go standard way then, whatever member is coming in context it will show children of that member. In context if you select Business Unit 1 the it will give you only Sub business unit1.1 and 1.2 and if you select sub business unit1.1 the it will show its childrens of sub business unit1.1.

If this the requirement then I will suggest go with standard approach if not then go with override approach.

Please apply any solution and check the results and still you are facing issue please share more details on the  report, your requirement ,selection what members you want to show in report what will be the selection.

[Former Member · Accepted solution]

PLease see the example in the screenshot. The logic must be, I have the information of 'Business unit 2' then all data which not belongs to Business Unit 2 should be INCLUDED. The data within the selected business unit 2 on all levels must not be included.

Thanks

[Former Member · Accepted solution]

Hi,

With the DEP function you are getting only 2 members 1.1 and 1.2. From where are you getting the base members? I am unable to understand that! Is it that you only want business unit 1. Please explain neatly what is it that you want to exclude based on cell selection. An excel layout would be helpfull....

Regards,

JP

[Former Member · Accepted solution]

The input is retrieved from user input, and they must be able to say which data to exclude. So in this example a business user enters 'Business Unit 2' so he will see Business unit 1  (1.1 and 1.2) and Business unit 3 (3.1 and 3.2) and more if apllicable.

Please see this newExcel screenshot hwat the EPMDimensionOverride should accomplish

The users selects Business Unit 2, then he will see other data OTHER than the selected BU

[Former Member · Accepted solution]

Hi,

Ok then in that case as per my understanding directly not possible.

Workaround is maintain one property and  in that property you just mention business unit like 01,02,03. something like as shown in below table and then you can use this property in override with some excel logic so it will always exclude the desire members.

In below case it will exclude BU=01 and all other members will come in report.

You will require to maintain one property without that i guess it is not possible as per my understanding. Try it and let us know the results.

Hope this will help you.

[Former Member · Accepted solution]

Thanks for thinking with me, and I think you get exactly what I want... but your solution will not work as it will not selct the base members in a situation with sub business units.. it only will work when a Business Unit has no SUB Business units..

So that's why I think we need to include BAS() in some way n order to retrieve the base level members (companies) to exclude

any thoughts on this on?

[Former Member · Accepted solution]

Hi,

Just worked on it....in your case we can use Exclude Members functionality. Go to Edit report-->Exclude Members --> Select dimension ---> Select context member with base level relation ship..

And for your Edit report definition keep "BASE level" for the Business Unit dimension.

This is the result......one thing I  have given profitcenter for selection using EPM context member with only hierarchy nodes. you can do that in your case for Business unit. Shown in below report.

Let me know...if it helps...

Regards,

JP

[Former Member · Accepted solution]

Many thanks, this is the way to exclude more level items!

I owe you a beer

[Former Member · Accepted solution]

Hahaha..... Sure! Glad it helped you....

[Former Member]

Hi,

If I understood your requirement correct the please check below screenshot and for more information I am mentioning the formulaforoverride.

EPMDimensionOverride("000","DATA_SOURCE","CALC=N;PARENTH1<>"&A4)

It will exclude base members of that parent present in that cell remaining all bas members will come in report.

Hope this solve your problem.Please don't create duplicate posts.