EPM report - suppress one dimension based on a single member of a nested dimension?

[alexander_b_white]

Hello experts,

In EPM 10, is it possible to build a report where the outer dimension in the row axis is suppressed based on the presence of data within a single member of the inner dimension?

To give a simplified example, let's say our report contains two dimensions in the row axis: Profit Center and Account.  The full, non-suppressed version of the report looks like this:

I would like to suppress the Profit Center dimension based on the A.402000 member of the Account dimension.  Whenever A.402000 contains data, all three Account members would be displayed within that Profit Center; but when A.402000 is empty, the Profit Center would not be shown at all.

The desired result would look like this:

Using the original example above, you can see that A.402000 contains no data within PC.100000 and PC.400000, so those Profit Centers would be entirely suppressed.  However, all three Accounts would be shown for PC.200000 and PC.300000 since they contain data within A.402000.

As I'm sure you all know, if I use standard row suppression, the result looks like this:

This is not desired because PC.100000 and PC.400000 are still shown, due to the other included Account members containing data.

I've tried tinkering with the Filtering tab in the report editor, and it seems like the Keep Blocks option might be part of the answer, but I don't see any way to perform filtering based on a single member.

Do you have any ideas?

Thank you!

Alex

[former_member186338 · Accepted solution]

"I've tried tinkering with the Filtering tab in the report editor, and it seems like the Keep Blocks option might be part of the answer, but I don't see any way to perform filtering based on a single member." - correct, no way to suppress based on some defined member!

You may try the following:

1. Create first report with required list of Profit Center and only A.402000.

2. Suppress it to have the correct list of Profit Center members with non-empty A.402000

3. Create a local member as a column that will concatenate Profit Center member ID's in a comma separated string.

4. Use this comma separated string in EPMDimensionOverride for Profit Center for the second report.

5. Use all required Account members in the second report.

Vadim

[alexander_b_white · Accepted solution]

Vadim, thanks for the unique idea!  I have built a report with this structure and it works well.

The only complication I can anticipate is if a user wants to edit the column layout of the main report, the suppression might be incorrect since the first report would still be basing its suppression off of the original column layout.  This could be solved as follows:

1. On the main report, insert a similar local member as the one you described, but for the column axis.
2. On the first report, reference this comma-separated string to override the Time dimension.

Users would just need to be trained to click Refresh Workbook after altering the column layout.

Another option I thought of this afternoon is to only include A.402000 in the Account selection, and add A.401000 and A.402000 as EPMRetrieveData formulas.  This way, the standard row suppression would only look at A.402000 to determine which Profit Centers to display.  I would not recommend this on a larger report for performance reasons, but it could work for small one-offs.  The only downside is that users might need to click Refresh twice before the EPMRetrieveData cells bring back data.

[former_member186338 · Accepted solution]

Sample:

Use B4 in EPMDimensionOverride for second report.

Vadim

[alexander_b_white · Accepted solution]

Thanks, Vadim.  This looks very similar to the report I built, except that I did not use the IF formula to account for the blank row at the bottom.  Instead, I embedded a LEFT statement in my EPMDimensionOverride formula to get rid of the last comma.  I think I like your approach better. 

Cheers,

Alex