Thursday, January 24, 2013

Ordering Sub Reports dynamically in SSRS by using parameter values...

There are some requirements that the order of the tables or sub reports needs to be changed according to the provided parameter values. I tried to find a way and searched the internet. But the found solution was add sub reports multiple times and show or hide those by considering the parameter value.
But this is not a good way because if there are 10 reports (tables) to be sorted, then we have to add all those 10 reports for each position and it will load 100 reports due to that.

I was able to get done this in a different way and it will provide better performance than this.
What I did was I kept all those tables in a single report instead of separate sub reports and show or hide those table by considering the passed parameter value. In that add a parameter as "Report" to keep the which table is need to be showed. This report is used as the sub report and in the main report what I did was create a data set considering the provided ordering parameter values.
Let say we have two tables and want to sort those table according to the given position.
For the first table, position is provided by the P1 parameter and the position of the second table is provided by the P2 parameter.
Then a dataset is created using those parameter values and the query is as below.


   1: SELECT        @P1 AS Position, 'Report1' AS Report
   2: UNION
   3: SELECT        @P2 AS Position, 'Report2' AS Report



In the data set just pass those parameters as below



Then add a table and set the DataSetName as the created data set and add sub report to the detail cell as shown below.



In the table, set the Sorting property of the Details group by the Position value in the created data set.
Also in the Sub Report pass the Report value in the data set to the report as shown below.



In the sub report which contains those two tables, set the visibility of those tables by considering the "Report" parameter value.
Also to avoid loading the unwanted dataset data, we can only execute the query by checking the "Report" parameter value and if only it matches with the relevant table.
This will improve the performance since only the required data is retrieved and does not load all the data sets.