SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reporting on Hierarchical Recursive data using Reporting Services


Reporting on Hierarchical Recursive data using Reporting Services

Author
Message
Maxer
Maxer
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3435 Visits: 1667
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
aktikt
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 413
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
aktikt
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 413
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
Filaretos Postekoglou
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 26
Hi,

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

Thanks
Alex S-483693
Alex S-483693
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 117
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
Alexander Kleinwächter
Alexander Kleinwächter
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 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
gita_rani_guru
gita_rani_guru
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 27
Thanks Alum. This worked for me like a magic. Thanks again
suwyah
suwyah
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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.
ashley-1085959
ashley-1085959
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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.
Travis Truax-483944
Travis Truax-483944
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 15
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-
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search