about variable(s) in dataset query in SSRS

Question

Hello There,

I recently gone through SSRS parameters documentation. In the associated documents, I came across about using variable in dataset as follows:

1. Filter paginated report data at the data source by writing dataset queries that contain variables.
2. When you define a dataset query that contains a query variable, the query command is parsed. For each query variable, a corresponding dataset parameter and report parameter are created.

I tried to investigate about variables, and using variables in query designer and parameters, but, couldn't find any helpful elaborate information.

would anyone please help understand OR find example using variables in dataset query to filter reports?

Thank you for giving your valuable time!

Answer 1

Hi @ArunRaaman ，
To begin, we need to understand the definition of query parameters. A query parameter is passed to the data query engine to be incorporated into the SQL script's WHERE clause or some other part of the SQL that can accept parameters.

Query parameters are processed on the data source server and used to limit data retrieved from the data source. When we define dataset query that includes variables, Reporting Services creates corresponding query parameters.

when we add the following Transact-SQL WHERE clause as the last line in the query:

WHERE StoreID = (@StoreID)    

The WHERE clause limits the retrieved data to the store identifier that is specified by the query parameter @StoreID.
For more information. please refer to: Tutorial: Add a Parameter to Your Report (Report Builder).
Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @ArunRaaman ，
In dataset query, we usually use TSQL statement to query.
For variables in TSQL, we need to declare variables, set values for variables, and use SELECT statements to return variable values. That is, the commonly used "Declare, Set and Select".

WHERE StoreID = (@StoreID) can be thought of as the process of creating a variable. At the same time, the corresponding query parameters are created. I think the parameter and variable mentioned here is a dependency, this query parameter is essentially a variable.

But there is a difference between variables and parameters in scope. In reports, query parameters are used to limit the data retrieved from a data source, usually on columns or fields. The report variable can actually run through the entire report.
In SSRS report properties, you could create report variables. The variable can be referenced multiple times in the report through an expression.

Hope this helps.
Best Regards,
Joy