cancel
Showing results for 
Search instead for 
Did you mean: 

Exlcude Member from Expansion

Former Member
0 Kudos

Hi All,

I'm very new to BPC, and I'm having trouble desinging a report. I would like to run a report with all Base member in the ACCOUNT Dimension EXCEPT those that belong to the member A.TOT_OFFENDING_ACCTS. I've been playing around with the expansion range of the EVDRE, but I can't see how this is possible. I see how to exclude accounts based on a property with the Build Filter feature. Unfortunately there is no property common to only these accounts, and I don't have control over the properties. I also see how to include only certain members through a list, but if I use this option I may miss members that are added later.

I haven't found any other means of filtering, and suppression seems to only relate to items with null measurements. Is it possible to exlcude certain members? This seems like such a basic request. I feel like I'm missing a big piece of information.

Thanks.

Erin

Accepted Solutions (0)

Answers (1)

Answers (1)

esjewett
Active Contributor
0 Kudos

Hi Erin,

I'm doing this from memory and not testing in a system right now, but I believe that the ID of a dimension can be treated as a property for the purpose of filtering. So you would want to try "ID<>TOT_OFFENDING_ACCTS AND CALC=N" as your filter in the Account memberset to get all base level accounts that are not TOT_OFFENDING_ACCTS.

Hopefully that works for you.

Cheers,

Ethan

Former Member
0 Kudos

Thanks Ethan, but I'm getting an error in the EVDRE cell when I try that, #ERR: Invalid member or flag in <member set>, item: IDTOT_OFFENDING_ACCTS AND CALC=N, col# 2. After asking around a bit more, I'm being told that it isn't possible to exclude certain members. That instead I need to list those to include. I'm not happy about that as the list will require constant maintenance as new members are added. If anyone has information to the contrary, I'm all ears.

Thanks.

Erin

Former Member
0 Kudos

Hi Erin,

You could say BASMEMBERS AND ID<>TOT_OFFEND_ACCTS in memberset for the Account dimension.

This worked for me.

Hope this helps.

Thanks

Anjali

Former Member
0 Kudos

Sorry, It looks like the "Not equal to" symbol is not coming up in the message. You should say BAS and ID is not equal to the member. and the member should be in quotations for this to work. You should use the not equal to symbol (the symbol which has both less than and greater than symbols together).

Hope this helps.

thanks

Anjali

esjewett
Active Contributor
0 Kudos

Hi Erin,

As Anjali pointed out, the "not equals" sign is getting deleted from our messages. Did you try it like the following?

ID(less-than sign)(greater-than sign)TOT_OFFENDING_ACCTS AND CALC=N

Sorry about the missing sign in my first recommendation

Cheers,

Ethan

Former Member
0 Kudos

Thanks everyone,

I had high hopes with the does not equal sign, but I just can't get it to work. I've tried variations of everything you told me. (If they don't appear trust that there is a does not equal sign after each instance of ID shown below.)

BAS AND ID<>"A.TOT_OFFENDING_ACCTS"

BAS AND ID<>A.TOT_OFFENDING_ACCTS

BAS AND ID<>"A.TOT_OFFENDING_ACCTS" AND CALC=N

BAS AND ID<>A.TOT_OFFENDING_ACCTS AND CALC=N

BASMEMBERS AND ID<>"A.TOT_OFFENDING_ACCTS"

BASMEMBERS AND ID<>A.TOT_OFFENDING_ACCTS

BASMEMBERS AND ID<>"A.TOT_OFFENDING_ACCTS" AND CALC=N

BASMEMBERS AND ID<>A.TOT_OFFENDING_ACCTS AND CALC=N

ID<>"A.TOT_OFFENDING_ACCTS"

ID<>A.TOT_OFFENDING_ACCTS

ID<>"A.TOT_OFFENDING_ACCTS" AND CALC=N

ID<>A.TOT_OFFENDING_ACCTS AND CALC=N

Then I thought the problem might be because A.TOT_OFFENDING_ACCTS isn't a BASE member so I used one that was, A.79999, where I had previously used A.TOT_OFFENDING_ACCTS. That didn't help either.

In all of these instances I'm no longer getting an error message, but the accounts aren't excluded. A.79999 is still appearing as are the other offending accounts. When I use the CALC=N I get no results.

I appreciate everyone's help. Thanks.

Erin

Former Member
0 Kudos

Couple of things.

1) In the Member Expansion rule you don't have to qualify with the dimension like A.MEMBER_ID because you are already under a specific dimension ROW/COL in Expansion settings.

2) If A.TOT_OFFENDING_ACCTS is not a BAS member then i would assume you are trying to exclude a specific subtree and not just a single member.

As others pointed out , define a specific reporting property in the Account DIM, say, INC_IN_RPT and mark all members that you need as Y. Depending on your need you may code it for exclusion also.

Then in your report Expansion rules under Account dimension ROW/COL give Prop Name as INC_IN_RPT and Prop Val as Y

Hope that helps.

Former Member
0 Kudos

Thanks, but I don't have control over the properties. If our administrator defined a new dimension property every time a user needed to exclude a few members from a report, defining properties would be a full time job. I'll go with the "list every member I do want to include" option. This will create a lot of report maintenance for me as members are added. I fear I will end up missing pieces over time, but it seems to be my only option.

Thanks everyone for your suggestions.

Erin

esjewett
Active Contributor
0 Kudos

TOT_OFFENDING_ACCTS isn't a base member? That changes everything

So want to show all base accounts except accounts that are children of TOT_OFFENDING_ACCTS? You can't use filters to do this. You'll need to structure your hierarchy so that you have a node like TOT_NONOFFENDING_ACCTS and then use BAS(TOT_NONOFFENDING_ACCTS) to get the proper list of accounts, or you could use a hierarchy as already mentioned. You haven't told us much about your hierarchy structure, but I assume it is not currently set up this way or it sounds like you would have already done this.

Good luck with maintaining the list of accounts manually if you decide to go that route. It sounds like someone is already maintaining the list of offending vs. non-offending accounts in the hierarchy, so I would think that slightly restructuring the hierarchy or maintaining a property instead might be an option to look into in the future.

Cheers,

Ethan