Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Reporting on Hierarchical Recursive data using Reporting Services Expand / Collapse
Author
Message
Posted Thursday, January 31, 2008 6:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:06 AM
Points: 280, Visits: 1,115
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
Posted Friday, February 1, 2008 7:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 8:54 AM
Points: 194, Visits: 412
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
Posted Monday, February 4, 2008 10:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 8:54 AM
Points: 194, Visits: 412
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
Posted Tuesday, March 4, 2008 9:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, June 13, 2008 12:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 17, 2014 11:32 PM
Points: 3, Visits: 108
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
Posted Wednesday, July 30, 2008 6:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, April 16, 2009 12:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 8, 2009 3:39 PM
Points: 3, Visits: 27
Thanks Alum. This worked for me like a magic. Thanks again
Post #698750
Posted Tuesday, May 5, 2009 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, May 7, 2009 7:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 5, 2009 1:36 AM
Points: 1, Visits: 2
Alexander Kleinwächter (7/30/2008)
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


Hi Alex - Did you find a solution to this, we are looking at the same problem.
Post #712562
Posted Monday, June 7, 2010 11:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:51 PM
Points: 1, Visits: 14
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 »

Add to briefcase ««123»»

Permissions Expand / Collapse