Wednesday, May 02, 2012 8:36 PM
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.
Wednesday, May 02, 2012 9:19 PMModerator
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.
Syed Qazafi Anjum
Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
- Proposed As Answer by Syed Qazafi AnjumMicrosoft Community Contributor, Moderator Wednesday, May 02, 2012 9:19 PM
- Unproposed As Answer by HomeCookN Thursday, May 03, 2012 12:13 PM
Wednesday, May 02, 2012 9:35 PM
I think HomeCookn is dealing more with a parent-child/recursion issue than a group/visibility issue.
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.
- Marked As Answer by HomeCookN Thursday, May 03, 2012 12:35 PM
Thursday, May 03, 2012 12:17 PMThanks 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.
Thursday, May 03, 2012 12:56 PM
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.