How to get multiple disparate row groupings in a tablix to function?

Question

Here's the use case I'm working on: I have a report which contains a number of sub reports. Three of these sub reports consist of a tablix which needs multiple, disparate row groupings.

The structure of the query populating these looks a bit like this:

SELECT     c.Foo
,          c.Bar
,          c.Zap
,          d.Foo1
,          d.Bar1
,          e.Foo2
,          e.Zap2
FROM       c
LEFT JOIN (SELECT      d.Foo1
           ,           d.Foo2
           -- other fields and joins which justify the subquery structure)
           FROM        d) AS d
ON         c.PrimaryKey = d.ForeignKey
LEFT JOIN (SELECT      e.Foo2
           ,           e.Zap2
           -- Again, etc
           FROM        e) AS e
ON         c.PrimaryKey = e.ForeignKey

There are six of these subqueries in play for each of these three sub reports. Each one needs to group by each of the subqueries.

The grouping structure of the tablix is:

(RowGroup) -- c.PrimaryKey
- (Foo1) -- d subtable (d's PK)
- (Foo2) -- e subtable (e's PK)

(Yes, the children are siblings of one another.)

For the d grouping, I get repeated rows for each value as I expect. However, for the e grouping, I only get a single row -- the first row, specifically -- of data.

Since I'm not extremely well-versed on SSRS, and have been fighting this puzzle for quite a while, what is the correct approach for getting this kind of structure to work?

The desired output looks something like this:

c.Field Name        Value
c.Field Name        Value
d.Field Name        Value -- repeat for all ds
c.Field Name        Value
e.Field Name        Value -- repeat for all es
... etc

It is important that this tablix actually be oriented vertically by field instead of horizontally. The entire tablix structure repeats for each c. The business wants the report oriented that way.

It's also important that the groupings for d, e, etc., allow them to be intermingled within c's data. That is, there are a few fields from c, then the d group, then a few fields from c, then the e group, etc.

How do format a report in that fashion in SSRS?

I could not locate a duplicate, but as always, if this is a dupe, please close it and I'll get my solution from the other question. I hope. :) — John Rudy

Mike Honey Mike Honey · Accepted Answer · 2014-02-27T02:17:22

I'm a bit confused by all the c's in your example, but I would try to split out the derived tables from your SQL into datasets within the sub-reports (e.g. just looking at the c1 table).

Then the parameters you pass to the sub-reports would take the place of your current joins and filter each instance of the sub-report at runtime.

How to get multiple disparate row groupings in a tablix to function?

1 Answers