Reporting on Hierarchical Recursive data using Reporting Services

Question

Post reply

Reporting on Hierarchical Recursive data using Reporting Services

Asif Sayed

SSChasing Mays

Points: 620
More actions
June 22, 2006 at 3:00 pm

#190972

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

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

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

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 1

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

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 2

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.

Asif Sayed SSChasing Mays Points: 620 More actions · Answer 3

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.

mullerl Valued Member Points: 73 More actions · Answer 4

mullerl

Valued Member

Points: 73

July 13, 2006 at 3:25 am

#648893

Definately going to try it.

Thxs

narayan-363426 SSC Journeyman Points: 81 More actions · Answer 5

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

Maxer SSCrazy Eights Points: 8964 More actions · Answer 6

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?

steitelbaum SSC Eights! Points: 864 More actions · Answer 7

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".

aktikt Ten Centuries Points: 1355 More actions · Answer 8

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.

alum SSC Rookie Points: 37 More actions · Answer 9

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... 🙂

Maxer SSCrazy Eights Points: 8964 More actions · Answer 10

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)

aktikt Ten Centuries Points: 1355 More actions · Answer 11

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.

aktikt Ten Centuries Points: 1355 More actions · Answer 12

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

Filaretos Postekoglou Grasshopper Points: 17 More actions · Answer 13

Hi,

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

Thanks

Alex S-483693 SSC Enthusiast Points: 121 More actions · Answer 14

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