Reporting on Hierarchical Recursive data using Reporting Services

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Asif Sayed » Reporting on Hierarchical Recursive data...

21 posts, Page 2 of 3

««1 2 3 »»

Reporting on Hierarchical Recursive data using Reporting Services

Rate Topic

Display Mode

Topic Options

Author

Message

Maxer

Posted Thursday, January 31, 2008 6:37 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, September 21, 2012 9:22 AM
Points: 250, Visits: 815

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)

Post #449906

aktikt

Posted Friday, February 01, 2008 7:58 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 03, 2012 4:56 PM
Points: 194, Visits: 410

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.

Post #450842

aktikt

Posted Monday, February 04, 2008 10:07 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 03, 2012 4:56 PM
Points: 194, Visits: 410

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

Post #451237

Filaretos Postekoglou

Posted Tuesday, March 04, 2008 9:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 11, 2008 2:33 AM
Points: 1, Visits: 26

Hi,

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

Thanks

Post #463879

Alex S-483693

Posted Friday, June 13, 2008 12:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 12:48 AM
Points: 3, Visits: 93

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

Post #516449

Alexander Kleinwächter

Posted Wednesday, July 30, 2008 6:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 16, 2012 6:59 AM
Points: 2, Visits: 40

Hi,

is there a possibility to use hierarchy grouping in a matrix? The aggregates like SUM with the recursive parameter only recognize the row group (hierarchy) but not the column group (e.g like months). So every column has the same values. Is there a workaround for that issue?

Best regards
Alex

Post #543402

gita_rani_guru

Posted Thursday, April 16, 2009 12:22 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 08, 2009 3:39 PM
Points: 3, Visits: 27

Thanks Alum. This worked for me like a magic. Thanks again

Post #698750

suwyah

Posted Tuesday, May 05, 2009 6:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, May 16, 2009 3:03 PM
Points: 1, Visits: 3

Hello everyone,

I have been trying to implement the exact example provided with ASP.NET without luck. I'm getting the following error on the reportviewer control:
* An error occurred during local report processing.
o The report definition for report 'd:\My Documents\Visual Studio 2008\Projects\RecursiveReport\RecursiveReport\Report1.rdlc' has not been specified

Any advise would be appreciated in this regard.

Thanks.

Post #710113

ashley-1085959

Posted Thursday, May 07, 2009 7:56 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 05, 2009 1:36 AM
Points: 1, Visits: 2

Alexander Kleinwächter (7/30/2008)

Hi Alex - Did you find a solution to this, we are looking at the same problem.

Post #712562

Travis Truax-483944

Posted Monday, June 07, 2010 11:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 09, 2010 7:49 AM
Points: 1, Visits: 12

Has anyone figured out why the built-in recursive functionality can only accommodate unique relationships?
To stick with the ultra basic example used - if an employee has 3 managers, they will be listed under one of the three, and the other two relationships will be omitted. Is there some way to change this behavior? I sure hope so. If not, this functionality is nearly worthless. Why would they assume that all recursive table relationships are unique?

Thanks-

Post #933623

« Prev Topic | Next Topic »

21 posts, Page 2 of 3

««1 2 3 »»

Permissions