Hierarchical ID Recursion for Chart of Accounts

Question

This must be a pretty standard report format but so far it looks as though I’m not smart enough to figure out how to get ‘er done. I’m using 2008 R2 to work with a hierarchy of about 30k GL accounts. The hierarchy is narrow at the top—only 5 subtrees (Assets, Liabilities, etc.)—and it is only four levels deep but the bottom levels are several thousand accounts wide.

In addition to the original Account_Number and Parent_Number columns, a straight-forward T-SQL recursive CTE has produced 3 new columns which I suppose should be useful. I’ve tried to work through the SSRS help examples on How To Create a Recursive Hierarchy Group but it’s not getting me where I want to go.

Level     Hierarchy             Node                     Account_Type

1              /1/                          0x58                       Assets

1              /2/                          0x68                       Liabilities

1              /3/                          0x78                       Equity

1              /4/                          0x84                       Income

1              /5/                          0x8C                      Expenses

2              /1/1/                     0x5AC0                 Assets -> Cash

2              /1/2/                     0x5B40                  Assets -> Acct Receivable

2              /2/1/                     0x6AC0                 Liabilities -> Acct Payable

I would like to a report which opens showing only the top 5 accounts but which will allow User to drill down through each of the remaining 3 levels. Using an expression to indicate level by simple indentation is not an option.

Please point me to an example or some more instructions.

---

HomeCookN

Answer 1

Hi There

As far as I understand you  have charts of account which have three levels and by default you would like to open first level and from there user will drill down to next level.

You could do it easily by creating three row groups and visibility of the second and third level will be hidden initially and use display can be toggle by report item as shown in the figure.

However if you don’t like to go on this track and give your user the option to select which level he would like to see after the first level then please create a parameter and give user the ability to go straight to that level.

I am putting some screenshot for your help.

If you still have a questions please let me know.

Many thanks

Syed Qazafi Anjum

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 2

Syed,

I think HomeCookn is dealing more with a parent-child/recursion issue than a group/visibility issue. 

HomeCookn,

A straightforward approach would be to flatten your hiearchy.  This, or course, assumes that your number of levels is not going to change.  Then, once flattened, you could group by the resulting columns (Level1, level2, etc).  Only after you flatten it, can you use Syed's instructions for creating a drillable report.   To flatten a parent/child hierarchy, you can do this a few different ways.  This article should get you started. 

Gene

Answer 3

Thanks a lot for your response, Syed, but as the following repsonse explains, displaying the different groups is not my issue. Sorry I did not express it more clearly becuase I can see that you spend some time on trying to make a helpful response.

---

HomeCookN

Answer 4

Thanks Gene; you obviously do see the issue and after reviewing the flattening hierarchies link, I think you are correct in putting forward that approach as a reasonable solution.

However, as it turns out, while thinking about this last night I believe I hit upon a simplier way to do what I want to do. I don't know if it will work but today I'm going to try it and I'll post my results.

Basically I'm going to parse the string expressions in my Hierarchy column into four distinct levels based on the values between each pair of "/" and then create four nested Row grouping levels. Here's hoping.

---

HomeCookN