Impact of manipulating SSRS parameter inside dataset

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.

Advertisements

About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in SSRS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s