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:
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.
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
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