We’ve build a Finance OLAP Cube in SSAS which is mostly used/browsed by Accountants. Due to a “self-service” policy that is followed, users are in the meantime required to make use of Excel (with a data connection to the cube) as their reporting and/or analysis tool.
One of the dimensions, the Accounts dimension, contains the full list of accounts including a Chart of Accounts hierarchy. After the initial launch of the cube, users started to request alternative/custom Account hierarchies to cater for their reporting.
Most of these requested hierarchies (apart from the Chart of Accounts hierarchy) contain only a portion of the full list accounts e.g. Management Expense Hierarchy.
The initial approach was to create e.g. a Management Expense Hierarchy within the Accounts dimension with a “not applicable” attribute name for the non-management expense accounts. Users then needed to deselect the “not applicable” name from the “Management Expense Hierarchy” Pivot Field List in Excel whenever they dragged this hierarchy into the “rows” field. Not all users were satisfied with this option and requested that they only wanted to see the relevant accounts without deselecting items which lead us to utilize the attribute hierarchy select/deselect members of the Dimension Data section from Roles – which was not a successful.
The next approach was to utilize the Dimension Hierarchy Level Property – HideMemberIf “No Name” in order to show only the relevant Expense Management hierarchy levels/members and hide the non-management expense accounts. This was achieved by creating a dimension called “Management Expenses” and adding an additional foreign key column in the fact table with a relationship to the Management Expense dimension. The non-management expense accounts were populated with a -1 key and a NULL attribute name.
The desired results were achieved in Excel with the HideMemberIf “No Name” BUT the “Grand Total in Excel” still shows the sum of ALL accounts
Any suggestions in order to resolve the “Grand Total” issue – i.e. to exclude the -1 entries also … will be much appreciated.
