We have a pivot table with custom groups for both the rows and columns, based on the required filter criteria and using column hierarchies. We have multiple measures going across as columns that needs to be based on the filter criteria, but independent of each other. The problem is the pivot table wants to keep the same filters from the row-based group I created, even though those filters need to change for the other measure columns. This would be easy in Excel as each cell/intersection can have its own formula, but we are limited by the groups we already defined on the left.
I thought I could add another hierarchy column and just display them seperately, but OBIEE only lets you add one heirarchy per pivot table. Is there a way to accomplish this in one table or I must have seperate tables?
Activity Groups | Account Group 1 | Account Group 2 | Account Group 3
Group 1 x,xxx,xxx x,xxx,xxx x,xxx,xxx
Group 2 x,xxx,xxx x,xxx,xxx x,xxx,xxx
Group 3 x,xxx,xxx x,xxx,xxx x,xxx,xxx
To clarify, Group 1 on the left would be filtered on certain activities that combined with Account Group 1 (filtered on certain accounts) produce the correct sum/measure. The problem is that for Account Group 2, or Account Group 3, I need Group 1 to have different filter criteria than it was for Account Group 1, rather than the same across all 3 account groups.