How to UNION different datasets in SSRS?

Question

I have several datasets/tablixes on my report (e.g. seven datasets). All datasets have the same fields: First field is the primary key, the other fields are integer values. E.g:

and five other tables...

Now what I want is to generate a new dataset, which UNION all datasets, then groups them by the ID and sums up all the fruits. Thus the new dataset looks like this:

I Know that I could write a new SQL query which unions all the seven existent queries, but in my case this would be a very, very long query. So I wonder if this is possible by using pure SSRS functionality (e.g. expressions on calculated fields)?

Did you solve this? My issue is not a long query but the data sources are different — KeithL

Aldrin Aldrin · Accepted Answer · 2016-05-31T07:16:06

I think that would not be a very long query if you only have 7 tables. Use this query in just 1 dataset then put the data in one tablix.

SELECT ID, 
      SUM(APPLES) AS APPLES,
      SUM(BANANAS) AS BANANAS,
      SUM(TOMATOES) AS TOMATOES)
FROM
(
SELECT * FROM TABLE1
UNION
SELECT * FROM TABLE2
UNION
SELECT * FROM TABLE3
UNION
SELECT * FROM TABLE4
UNION
SELECT * FROM TABLE5
UNION
SELECT * FROM TABLE6
UNION
SELECT * FROM TABLE7
)
GROUP BY ID

How to UNION different datasets in SSRS?

1 Answers