Parameter based joins on SSRS reporting

Often in reporting, a user might want to include a set of tables added in the report dynamically. One way to do it, is by determining, all the related tables that could be needed during design time and then add them all after turning each of them into a select * query with a parameter and doing a left join.

So, something like

Select * from table1

would be turned into

Select *, @reportParam1 as flag1 from table1

and then the left join will be applied.

So, something like the following could be produced

 


select * from main_table mt left join (select * from (select *, @reportParam1 as flag1 from table1) raw where @reportParam1=1) as child_table1 on mt.fk = child_table1.pk

that’s it :)

Leave a Reply

Your email address will not be published. Required fields are marked *


*