Recently I had to convert a report’s dataset, which was using Stored Procedure, into inline T-SQL commands. The dataset had 4 parameters , which it was passing to a stored procedure. I had to add two more parameters and since this was an emergency fix, I took the code from the stored procedure, converted it as inline T-SQL and applied my new filters. When I hit the run button on dataset designer, the “Define Query Parameters” window popped up but I could see only 5 parameters instead of 6 parameters.

I checked the parameter section of dataset and it had 6 parameters. I opened the RDL as code and checked whether all the parameters were available. Everything looked fine but it was not accepting the value for one parameter.

To give a graphical view of the issue, refer the below screenshot (simplified the dataset to two parameters)

The first one is the image of the dataset and a simple T-SQL commands which finds the DATEDIFF between two dates which are taken as parameter. The second image shows that the dataset has two parameters, @dtFrom & @dtTo

The below screenshot is from the dataset query run command. If you notice it accepts only one parameter which is strange as we are passing two parameters to the dataset. The @dtTo parameter is missing.

After spending hours digging into this issue, I figured out that this happens whenever the parameter is manipulated inside the dataset. In the above case, the @dtTo parameter is modified inside the dataset. Once I removed the first line (the Set statement), it appeared in the Define Query Parameters window!!!

I was able to reproduce this in both SSRS 2005 & 2008. The fix is obvious. Assign the parameter to a local variable and then manipulate.