Expense Management Hiearchy

[#1]

Hi,

I am trying to create a dashboard for elemental level of expenses for products.

I have 3 excel tables: Cost center mapping table, account line mapping table and the actual data set that needs to be pivoted.

Both of the cost center mapping table have up to 10 hiearchys. The way I have it is level 1 is column A, level 2 is column B ... etc.

My data set is pulled at the lowest level for cost center and account lines. I need to make a dashboard that can map and sum these lowest level hiarchys to level 2 or 3 of the cost center and account lines.

[johnw]

When you say level 1 is column A, level 2 is column B, etc., I picture a data model something like this:

Level 1, Level 2, Level 3, Level 4, ..., Amount
XXX, YYY, ZZZ, 000, ... , $500

If so, and you want to sum to level 2 or 3, just put the levels you want to sum to in your chart, and use sum(Amount), say, as your expression.

I'm pretty sure I've misunderstood, though. If so, can you show briefly what your data model looks like and what you want your chart to look like?

[#3]

Ok So here's my thought process behind it, but maybe I'm building my tables wrong.

I'll have an 2 Hiearchy Mapping tables. One is for account level and the other is for CostCenter level. These 2 mapping tables are basically static, unless the hierarchy changes. Then, I have a data dump of 2014 expenses and 2015 expenses in it's lowest cost center and account line.

I'm trying to build a summary view in a dashboard of our level 2 expenses and be able to drill down farther in account hierarchy.

My account hierarchy mapping table looks like this:

Level 1,                        Level 2,               Level 3  ..... Level n+1

Non-Interest Expense, Total Comp,

Non-Interest Expense, Total Comp,        Salaries

Non-Interest Expense, Total Comp,        Salaries,     Full-Time

Non-Interest Expense, Total Comp,        Salaries,     Part-Time

Non-Interest Expense, Total Comp,         Benefits

Non-Interest Expense, Total Non-Comp, Sales & Mktg, Sales,

Non-Interest Expense, Total Non-Comp, Sales & Mktg, Marketing,

The cost-center mapping table is built the same way.

My Data dump is set up like this and the account and cost-center is in it's lowest level

Account Level, Cost-Center Level, 2014 Expenses, 2015 Expenses

So in my dashboard, I'd like to see something like this:

Filter:

Product

                                          2014 Expenses           2015 Expenses

Non-Interest Expense

Total Comp

  Salaries

  Benefits

Total Non-Comp

  Sales

   Marketing

But I want to be able to drill into the Salaries, if I need to.

[johnw · Accepted solution]

Attached is one approach. The inline loads at the start are assumed to be what your raw data source looks like, then I transform that data into a format I think would be more useful for you, and drop the original tables. Hopefully I've understood your data and your needs.

[#5]

Thank you John,

Question on your code below. Since their are different level of hierarchy, is the code below fixes that the lowest level might be on different levels?

,if(len(trim(CC2)),CC2,CC1) as CC2
,if(len(trim(CC3)),CC3,if(len(trim(CC2)),CC2,CC1)) as [Cost Center]

[johnw]

Yes, that's the idea - to put the lowest level always at the same level, so that it can be one field, so that your main data table will link automatically to that one field. I suspect there's a more clever way to write that piece, as it will be cumbersome for ten levels, but that was the idea. There may be a better way to handle the hierarchy itself. If so, I'd like to know about it, as this is how I've handled similar requirements in several applications. Actually, in my applications, the hierarchy has always been simple enough with few enough levels that I just coded it in an inline table, including the extended levels, and called it good enough.

And overnight I thought it would be nice to put 'Other ' in front of the Account or Cost Center when extending down to the lowest level, so that we'd see 'Other Total Comp', say, if the raw data used 'Total Comp' as the account. See attached if that's something that you think would be an improvement.

[johnw]

A different approach is attached. I don't think I've ever used the Hierarchy or HierarchyBelongsTo statements in a real application, but I thought I'd give them a go. I had to fiddle a little with the raw data. It would have been easier if I'd just changed the raw data to an adjacent node list, but it seemed like that wasn't what you had as data.

One downside is that instead of filling in the missing nodes in the expanded hieararchy, it just gives you null, but at least it still links up properly.

One upside is that we can set up an Account Total field. If you just make selections in the Account, it only shows you data directly associated with that account. But if you make selections in the Account Total field, it shows you all data at that point and below in the hierarchy.

Someone with more experience using Hierarchy and HierarchyBelongs might do a better job of this.