In SSRS 2008, how are parameters passed down to the DatSet Query... or can they?

Xarzu · February 7, 2019, 8:37pm

I have a simple question. I think this is pretty straight-forward. If necessary, I can include screen shots of what I mean.

Here is the basic format of the report. I have a SSRS report made on Visual Studio 2017. it has two DataSets. It has one Tablix and one Bar Chart that is loaded with from the DataSets. I have one Parameter defined for the report. The parameter a Date/Time data type set to allow nulls and I name it rundate. (I will use is in a data driven subscription, but for now that is beyond the scope of my question here)

For each dataset, I have gone into the Datset Properties and added a parameter with a Parameter Name of rundate and Parameter Value [@rundate]. I think it would be great if I make this somehow a variable in the Query for the Dataset. I assume that this is exactly what this is supposed to be used for. How do I declare it in the SQL query.

I said that this was a pretty straight forward question. it should be easily answered and it will save time in digging around the internet for the answer. So far, all I have found is how to set up filters. This is not exactly the same sort of thing. But, if necessary, I guess I could resort to using filters but it will take some changes in the query. And it would be a mess.

yosiasz · February 7, 2019, 8:58pm

https://docs.microsoft.com/en-us/sql/reporting-services/tutorial-add-a-parameter-to-your-report-report-builder?view=sql-server-2017

jeffw8713 · February 8, 2019, 4:24pm

If you are embedding your queries in the report - then you can just reference the parameter as you would in SSMS. With that said - I always recommend using stored procedures instead of embedded queries.

If your queries are embedded and you need to change the logic but not the columns returned - you have to edit the query in the report definition and redeploy. With a stored procedure you just change the logic in the procedure.

Using a stored procedure for your datasets - you also get the advantage of SSRS building the report parameters for you based on the stored procedure parameters. And - you can optimize the stored procedure much easier than optimizing the code embedded in the report.

mannesravya · February 8, 2019, 5:13pm

In where clause of query just mentioned where column in @parameter.