The way I like to do it in SSRS is start first with the tsql that will create your report dataset. So in this case, what is the tsql that will create the results you are looking for?

Then use that tsql to create a stored procedure. Create a new dataset in your report and specify your stored procedure as the source for the dataset.