I was planning to use an alternate hierarchy to summarize accounts in groupings that are different than the primary parent-child relationships.
I created and populated a custom property named “Account Group”, only to find out from the Prophix Online Help:
> The following dimension types cannot have alternate hierarchies: Account, Version, Time Perspective, Currency, and Allocation.
As a partial workaround, I’ve used name sets for each account group so that I can dynamically include each account in rows organized by account group. I can also use template calculations to sum each group.
What I’m not able to do is report the sum of each account group directly without the detail rows.
A few clunky options are:
-
Hide the detail rows in the report and just make the template calculation summary rows visible.
-
Use a SUMIFS function summary based on the detail rows.
-
Export the data to Excel.
Is there a better workaround that would allow me to use the custom member properties in the reporting? As far as I can tell, I can’t even display the custom member properties in the report unless they are in an alternate hierarchy dimension.
TIA
Hey Jerry,
The Account dimension cannot have an alternate hierarchy due to the various aggregation methods and formula logic the Account dimension type is built upon.
There are a few options other than the ones you described, and each would be a good fit based on your specific need.
I have worked with a client where we actually built a separate financial reporting cube for a second set of reporting needs (a different account grouping then their regular reporting). However, a second cube may be overkill for you depending on the need. Do you have many reports that you need to create with this alternate account grouping or just 1 or 2? How frequent would these reports be used? For my client, there were 5+ reports used on a monthly basis, involving charts etc, so we deemed a second cube would be the best & cleanest fit.
Another option could be creating statistical accounts (one for each account group) and using an account formula to sum the accounts. However, this would only really be a good fit if you only have a small number of account groupings and accounts that needed to be summed. For example, with the client I described above, they only had 4 account groupings, but needed to fit their entire PNL accounts into these 4 groupings. That’s a lot of accounts to sum! If you go this route, you’ll also need to consider whether to go with a standard formula or a procedural calc for performance implications. The Prophix Online Help you are familiar with describes what the best use case is for each calculation type, but if you’re confused, this would be a good question for your CSP consultant.
If this second reporting hierarchy is just for 1 or 2 reports, I would likely go with the first option you mentioned - using sum formulas and hiding the detailed accounts. You shouldn’t have to hide too many detailed accounts if you are using the named sets (i.e. you should only have to hide the named sets).
As for displaying custom member properties, yes! You can do this. Pls see steps below:
- Open your report in Design Mode in Template Studio
- In the left-panel, open your data view and right-click the Account dimension.
- Choose Properties
- Display members using > CUSTOM > click on the 3 dots
- Remove what’s in the Display Format box (right side) and choose the appropriate property from the Available Properties (left side). Click OK and Preview the report to take a look.
- If you want to display the Account in 1 column and the Account Grouping in a second column, you can use the clone feature…
- In the left panel, right-click on Rows > Clone > insert it in a blank column.
- In my example below, I’ve cloned my Account dimension so that I can display the account key in 1 column, and the account name in the 2nd column
-
Advanced reporting tip: rename the 2 row titles (e.g. Account Name, Account Key)
Hope this makes sense!
Lilian
5 Likes
Hey Lilian,
Thanks for the awesome reply!
We only need this hierarchy for one report per month, and there are about 20 groups.
Based on your explanation of the pros and cons of each option, I’ve used a SUMIFS formula to make a summary table at the top of the template sheet, based on the detailed account data in hidden rows at the bottom of the sheet.
That seemed cleaner since I can hide or unhide all the detail rows in one step instead of having to hide 20 alternating rows if the formulas were interspersed in the detail.
That SUMIFS approach wouldn’t have worked without your explanation of how to display the custom member property through the Properties.
Thanks again! 
5 Likes
What if you need multiple account hierarchies for the entire P&L? I could do this using statistical accounts, but it’s just a bit messy. If I use a new cube, would I have to create a new cube for each set of alternate account hierarches? Is there a way around this? Or are statistical accounts the best way to go?
Hi @kim.worthington,
If you need multiple account hierarchies, the methods you discussed is the correct approach. If you go through the stat accounts, be sure to use procedural calculations to set that up. A new cube can be created as well however this approach can increase maintenance and you will need to create dataviews and templates for the new cube.
I hope this helps!
