Reporting on Hierarchical Recursive data using Reporting Services

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aSayed/reportingonhierarchicalrecursivedatausingreporting.asp

  • Did anybody else try this?  I'm getting flat output without indentation or hierarchy - everybody on level 1. 

  • I'm pretty sure that it has something to do with my dataset and the way I named the columns.  I inadvertantly named my 3rd DataTable column Reports_To.  And now I'm not getting any data for the field in my report.  Interestingly, it appears that the data set (dsReport - I think?) has the data, but the rds doesn't.  But since I was supposed to know what I was doing in C# & Reporting Services before attempting this exercise, I guess you needn't worry too much about me.  If I see a C# guy walking by I'll hijack his debugging skills.
     
  • Hi,

    You can give anything name to a column, it wont make any diffrence to reporting service if you call it "Reports_To" instead of "ReportsTo".  I would suggest to take a close look again on Report Design section of the article and make sure you are properly defining the detail grouping.

  • Definately going to try it.

    Thxs

  • Has anyone tried this with a parent-child dimension in SSAS?  Can you post how you accomplished it?  Thanks.

  • I'm trying to do this with SSRS 2005 but instead of showing indentation I want to use the drill down + option so you will see the CEO at the top with a single + and then be able to expand that out for each level.

    However, I can't think of any slick way to do that... has anyone else pulled that off?

  • yes, I got it to work with drill down.

    For some reason though, if i dont set initial visibility to "visible", the initial expression stays in effect. I wanted the initial visibility to be just one or two levels deep but that expression stayed in effect. For now I'm living with intial visibilty set to "visible".

  • Is there anyway to group on a recursive hierarchy? When I try to add a group that would be underneath the recursive hierarchy it doesn't display right.

  • aktikt, im not sure exactly what you like to accomplish but maybe this helps.

    (This applies to Table layouts, havent tested it with Matrix layout)

    To make a recursive hierarchy with drilldown in reporting services from an SSAS OLAP cube (with parent-child hierarchy) you:

    1. Include the hierarchy in your dataset by drag-n-dropping the parent-child hierarchy in the query designer. Let's call this hieararchy "Employees".

    2. Create a table with one column (more if you like to put in values); The first textbox shall contain =Fields!Employees.Value

    3. Select the table -> Properties -> Groups tab -> Details Grouping.

    4. In the details grouping view under "Group on:" -> "Expression", add =Fields!Employees.UniqueName

    4. In the details grouping view under "Parent group:", add =Fields!Employees.ParentUniqueName

    5. If you like a document map with the hierarchy, under "Document map label:", add =Fields!Employees.Value

    6. Still in the details grouping view, go to the visibility tab.

    7. Set "Initial visibility:" to "Hidden".

    8. Check "Visibility can be toggled by another report item.

    9. Under "Report item:" choose the textbox in the column which you created in step 2.

    10. Hopefully, this works. There might be other ways to do this... 🙂

  • Now for that to work you are relying on a data cube though right?

    Or would that same approach work with a simple table of data that had:

    EmployeeID

    EmployeeSupervisorID (references the employeeID of their boss)

  • Alum,

    Thanks for the reply. But, I am using reporting services 2005. I wish to set up three groups a top level profile group, a middle group which is recursive and a bottom group. It needs to look like this:

    Profile1

    -----Function1

    ----------Function1A

    ----------Function1B

    ----------Function1C

    -----Function2

    ----------Function2A

    User1

    User2

    User3

    User4

    Profile2 ...

    However, reporting services insists on displaying the data like this:

    Profile1

    -----Function1

    -----User1

    -----User2

    -----User3

    -----User4

    ----------Function1A

    ----------User1

    ----------User2

    ----------User3

    ----------User4

    ----------Function1B

    ----------User1

    ----------User2

    ----------User3

    ----------User4

    ----------Function1C

    ----------User1

    ----------User2

    ----------User3

    ----------User4

    -----Function2

    -----User1

    -----User2

    -----User3

    -----User4

    ----------Function2A

    ----------User1

    ----------User2

    ----------User3

    ----------User4

    Profile2 ...

    Any help you can give for doing this would be appreciated.

    Thanks.

  • FYI,

    I figured out the solution to the problem I mentioned above.

    Here's my solution: In my grouping scheme the users were displaying at every node, but I needed a way to only display the users at the final node. However, my every attempt at numbering the records through aggregate functions and such didn't work. However, it dawned on me that I was displaying the recursive hierarchy in alphabetical order. So, I realized that if I marked the last (alphabetically) function record with a DisplayUser field indicating that I wanted it displayed I could hide the rest. So, to my data set I joined the results of a SQL query that grouped by profile and Max(functionName). I used a case function to indicate a 1 if it was one of these records I wanted to display and a 0 if not. In the Report at the third group level, I placed a conditional visibility on this DisplayUser field. This was the crux of the issue. I still had some display issues revolving around toggling conditionally visible fields. But, I managed to get around this by toggling at the function group level instead of by row.

    aktikt

  • Hi,

    Have you implemented this with MDX and SSAS 2005 cube as datasource?

    Thanks

  • To apply the "Visibility" drill-down, I used the Expression "=level() > 2" then set the Toggle Visibility setting to use the employee name field (for me it was textbox5). This was to have the top 2 levels of the hierarchy visible (level 0 and 1) with the remainder hidden but toggled by clicking on the appropriate manager name.

    All worked perfectly, except when exporting to Excel where the indents and drill-downs weren't applied. This is a minor annoyance but the report is great in the normal front-end.

    A

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply