Will a matrix on SSRS split the data between two datasets which dont have common key but common dimensions ?

Question

Will a matrix on SSRS report split the data between two datasets which don't have common key but common dimensions or merge ?

Attached is a screenshot of two tables and expected and current o/p of matrix. Note id is not used to join here and we are merging two datasets which are not directly linked.

I want to know the expected behavior of the ssrs matrix when 2 datasets are joined which have no common keys but dimensions.

Answer 1

Hi @Anonymous ，
Depending on the query, the SSRS datasets are independent from each other.
In one matrix you could only have data from one dataset. But you could join the two table before using them in SSRS.
Try the following query：

select * from TableA  
  
select * from TableB  
  
;with cte as  
(  
select a.Product_code,a.unit1,b.unit2  
 from (select Product_code,sum(unit1) unit1 from tableA group by Product_code) a  
inner join (select Product_code,sum(unit2) unit2 from tableB group by Product_code) b  
on a.Product_code=b.Product_code  
)  
  
Select * into TableC FROM cte  

output：

Hope this helps.
Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks, I have one only one dataset on SSRS report and this dataset has sub queries ( Union of 2 datasets(sub queries))