I'm using Essbase as a source with OBIEE 22.214.171.124.2 BP1 and Essbase has two dimensions as below:
Account dimension (four levels): Total Accounts -> Account Category -> Account Subcategory -> Account Number (Eg: Total Accounts -> Revenue -> Operating Revenue -> 1001, 1002)
Project dimension (three levels): Total Project -> Project Class -> Project Number (Eg: Total Project -> Capital -> 100010, 100011)
I have a report requirement as below:
Account Dimension Project Number Amount
Revenue Total Project 300
Operating Revenue Total Project 300
1001 100010 100
1001 Subtotal 200
1002 100010 75
1002 Subtotal 100
I have achieved the required format using hierarchy column for accounts dimension, however, I'm not sure how to achieve subtotals for account number.
Any ideas how to achieve subtotals when using hierarchy columns?