I have a hierarchy question I can't seem to resolve. I have a reporting hierarchy table that defines how transactions roll up from Branch to Market to Region to Division (there are actually 9 levels in all but I'm simplifying it 4 for this example). Individual transactions are identified with a Branch # and a transaction date. Using the hierarchy function I have built the hierarchy structure and fields that allow these transactions to roll up and be summarized correctly based on the user selections. So far, so good. But what happens when the hierarchy is modified throughout the year? I need to generate a hierarchy that reflects the cumulative change throughout the reporting period. Help!
Here's an example. In January 2012 the reporting structure looks like this:
Division
Region
Market
Branch
D1
R1
M1
B1
D2
R2
M2
B2
D2
R2
M3
B3
In February 2012 the reporting structure is changed to this:
Division
Region
Market
Branch
D1
R1
M1
B1
D2
R2
M2
B2
D1
R1
M3
B3
Market M3 and Branch B3 were reassigned and are now in Division 1/Region 1. If I'm doing current year or quarter summaries is it possible to enhance my hierarchy to recognize that Branch 3 transactions report up through D2/R2/M3/B3 in January but D1/R1/M3/B3 in February.
I need to be able to handle and correctly reflect hierarchy changes at any of the 9 levels.
