Herein I will discuss about a Dynamics Report using Multiple Valued Parameter in SSRS. As recently, I was creating a SSRS Report wherein I was suppose to show the Performance of Engineers to my boss on Monthly Basis. So, I created an Year and Engineer Name as Query Parameters and a Specific value Parameter with Month Names.
Specific Values Parameter:-
Now my simple report was working perfectly on Monthly basis, on the selection of any single Month from parameter.The Report used to show the total no of Development Task and Support Task done in any current month and also the productivity/efficiency of each engineer which was calculated by :- Total no of task done(Dev. + Supp) / 22 days
I took out reports for the Month of Jan, Feb and March respectively which are as follows:-
But as any general requirement, everyone of us wanted the report to be flexible so that It can show the Report for any N no. of selected months and calculate the Efficiency accordingly on count of those Months.
i.e. new formula would be :-
Total no of task done in N Months(Dev. + Supp) / (22 days * N Months)
Now to achieve this behavior we have a functionality within SSRS Report Builder, where we can change the parameter values from Single Select to Multiple Select.
1> After selecting the check box for “Allow multiple Value”, we can see the reflected change in the report too. In the below figure we can select multiple values for Month at a time.
2> Now we would also need to make some changes in the Query accordingly like: Use IN for Multiple Values
Where (DATENAME(mm, CreatedOn) IN (@Month))
3> Also the Expression under Productivity needs to be changed with :-
=Fields!productivity.Value / (22 * Parameters!Month.Count)
With all the above changes, Our report is finally ready to show results for N no. of Months and with correct Productivity/Efficiency value. If you compare the above individual reports for the 1st Quarter and this single report combined, then the result will be same.
Hope this will be helpful.