CODEWITHSUNDEEP
sqlrecursionreporting-servicesparent-childdistinct
High Plains Grifter
High Plains Grifter

Reputation: 1571

SSRS Recursive Parent gives distinct children only, when children have multiple parents

I have made an SSRS report using the recursive parent functionality to show a hierarchical tree of values. The problem I have is that some children have more than one parent, but because (in order to use the recursive parent nicely) I need to group the results by Id, I only see distinct entries. This means that I only see each child once, even if it "should" appear in multiple locations in the report (under each of its parents).

Here is an example dataset that shows what I mean:

DECLARE @Bear Table
( ParentId  INT NOT NULL
 ,Id        INT NOT NULL
 ,Name      VARCHAR(255))

INSERT INTO @Bear
SELECT * FROM 
(SELECT 0 AS ParentId,  1 AS Id,    'Daddy Bear' AS Name    UNION
 SELECT 0 AS ParentId,  2 AS Id,    'Mummy Bear' AS Name    UNION
 SELECT 1 AS ParentId,  3 AS Id,    'Baby Bear'  AS Name    UNION
 SELECT 2 AS ParentId,  3 AS Id,    'Baby Bear'  AS Name)           AS FamilyMember
ORDER BY FamilyMember.Id

SELECT * FROM @Bear

My Actual data contains lots of "Baby Bears" where for instance a function is used by more than one procedure, or a procedure is used by more than one report.

When I make the report, I group on Bear.Id, with a recursive parent of Bear.ParentId, which gives me something like this (in the report table):

Level 1     Level 2
Daddy Bear
            Baby Bear
Mummy Bear

As you can see, "Baby Bear" only appears once (normally, Id would be unique and this would make perfect sense). What I would like is for SSRS to display is something more like this:

Level 1     Level 2
Daddy Bear
            Baby Bear
Mummy Bear
            Baby Bear

This would give the users a much better idea of the actual structure they are looking at.

So far, I have tried changing the report group to group by "cstr(Fields!Id.Value) & cstr(Fields!ParentId.Value)", in order to re-establish a unique grouping, so that no records are aggregated into invisibility, but this loses the ordering where children appear immediately after their parent, so I get something like this:

Level 1     Level 2
Daddy Bear
            Baby Bear
            Baby Bear
Mummy Bear

I have also tried adding ROW_NUMBER() OVER (ORDER BY Id, ParentId) as a new column in the query, to group on that, unquely, but SSRS seems to have a problem with this. The final workaround I am now using is to list only the distinct values as in the first example, but use an Action in each table row to run the report again for each node, on click. This is far from ideal, however.

I have also Googled without result.

I am stuck as to what to do.

Any help would be greatly appreciated - what should I do?

Thanks for your time,

Mark

Upvotes: 4

Views: 2284

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

Why can't you add the ROW_NUMBER() exactly?

SELECT ROW_NUMBER() over (order by parentid) as rn, * FROM 
(SELECT 0 AS ParentId,  1 AS Id,    'Daddy Bear' AS Name    UNION
 SELECT 0 AS ParentId,  2 AS Id,    'Mummy Bear' AS Name    UNION
 SELECT 1 AS ParentId,  3 AS Id,    'Baby Bear'  AS Name    UNION
 SELECT 2 AS ParentId,  3 AS Id,    'Baby Bear'  AS Name)           AS FamilyMember

Produces a "unique" id per row for grouping on.

UPDATE

So based on my understanding of your problem, you want a recursive CTE. There are quite a few questions here on SO about them, so between that and that link I encourage you to figure out how to produce a dataset that fits your needs.

Upvotes: 0

Related Questions