Multi value parameter passed to stored procedure is not working in SSRS
Today, I was facing very unusual problem in SSRS-2012 and that was very frustrating.
I had created a SSRS report with multi valued parameters and just for testing; I put the SQL query in text format inside its data-set and used the parameter with IN clause (like ColumnName IN (@ParameterName)) and as we know SSRS has the capability to handle multi value parameter; need not to handle manually like we need to do in Stored Procedures.
After some days, when My development got over I decided to change my text query to stored procedure for better performance and created the stored procedure with required parameters and handled multi value passing and filter data accordingly and was trying to run the report but It was not working!!!
I checked the parameter by displaying the parameter’s value but that was showing me #Error….. It was very frustrating that usual stuff was not working. I searched over the net but didn’t find anything.
Then I decided to apply very famous method of developers called “Hit & Try”!!!
Then finally came to know that some how SSRS remembers the nature of the parameter for what it had created. In my case; It had created for IN clause not for passing parameter to a stored procedure ( here I want to highlight that it is not related to .data file; I deleted it and tried that one as well).
Then, I deleted all the parameters from the .rdl file and refresh the data-set. It created all the parameter used in stored procedure. I linked them back with their respective data-sets and run the report and it worked!!!!!
So this time I senses that it is going to pass for a stored procedure and it worked. It is strange but it worked for me.
If anyone facing the same problem then delete all parameters then refresh the databases. I will work and don’t forget to take backup of .rdl file before it to prevent any consequences.