EPM report - suppress one dimension based on a single member of a nested dimension?
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?
Vadim Kalinin replied
"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.