We are gathering General Ledger data from Great Plains both in Summary and Detail format and storing it in a data warehouse of sorts. It is be gathered from around 800 databases, and is identified by lcoation with the field UnitID. Reporting on the data has been simple for the most part, as we are familiar with all of the fields, and use Crystal Reports XI. However, we are now trying to report on the data in a hierarchical manner. Basically, we need to be able to report on the data from bottom to top, while adding summary levels at the supervising units on the way up. What we are looking for is very similar to how FRx can use trees to create a hierarchy if anyone is familiar with FRx.
A simplified example of our data is like this:
UnitID AccountNumber Debit Credit
0000 1001 50.00 25.00
9020 1001 500.00 1000.00
5360 1001 75.00 20.00
6360 1001 100.00 30.00
5140 1001 12.00 10.00
As an fyi, we know the proper hierarchy and can modify it if necessary to provide more detail. However, right now, we simply have the information by UnitId and SuperUnitID(supervising unit). In the example above, 0000 is the top level and all units roll into it. 6360 rolls into 5360 and both of them into 9020. 5140 is on its own, but would roll up into 0000.
Our problem is creating summaries at the right levels. In this example, we would need summaries as follows:
0000 and all sub Units
0000
9020 and all sub Units
9020
5360
6360
5140
Crystal doesn't like this type of arrangement on its own so it can only return summaries by the individual levels and then the Overall totals. I have been trying to do this in SQL by creating a view that uses a Union to pass through the data multiple times and gather totals at the summary levels, but I have been unsuccessful, as I am not sure how to handle the data within our hierarchy tree.
Has anyone had to deal with a similar issue, or have any further ideas?
Thanks in advance for your help.
A simplified example of our data is like this:
UnitID AccountNumber Debit Credit
0000 1001 50.00 25.00
9020 1001 500.00 1000.00
5360 1001 75.00 20.00
6360 1001 100.00 30.00
5140 1001 12.00 10.00
As an fyi, we know the proper hierarchy and can modify it if necessary to provide more detail. However, right now, we simply have the information by UnitId and SuperUnitID(supervising unit). In the example above, 0000 is the top level and all units roll into it. 6360 rolls into 5360 and both of them into 9020. 5140 is on its own, but would roll up into 0000.
Our problem is creating summaries at the right levels. In this example, we would need summaries as follows:
0000 and all sub Units
0000
9020 and all sub Units
9020
5360
6360
5140
Crystal doesn't like this type of arrangement on its own so it can only return summaries by the individual levels and then the Overall totals. I have been trying to do this in SQL by creating a view that uses a Union to pass through the data multiple times and gather totals at the summary levels, but I have been unsuccessful, as I am not sure how to handle the data within our hierarchy tree.
Has anyone had to deal with a similar issue, or have any further ideas?
Thanks in advance for your help.
