Combine results from different datasets on one Tablix in Report Builder 3.0

Transact-SQL
1

Hi All,

I have 17 data sets connected to 17 datasources and displaying in 17 tablix in report builder 3.0 as shown in the pic below:

1
1.JPG1479×731 177 KB

Now since the tablix are all the same except that they are connected to different datasets, i want to have 1 tablix and point all the datasets to it and combine the resulting data as shown in the pic below:
2
2.JPG1479×752 103 KB

The reason i want to combine them into one is that those many tablix can not be Pivoted after they are exported to excel. The columns are not consistent.

Please assist.

2

Have you tried what was recommended to you here?

3

Yes, it brings me to this new question.

4

Try out the recommendation of creating only one dataset. Either create a shell db wherein you aggregate data from all databases using a stored procedure in this shell db or leverage an existing db and do the same

5

i do not have rights to create anything on those databases, all i do is retreive data

6

in that case create one dataset and create multiple joins from across all databases in the query string.

select name, action, maction, paction
from db01.dbo.table01
union
select name, action, maction, paction
from db02.dbo.table01
union
select name, action, maction, paction
from db03.dbo.table01