Sql Server Reporting Services Must Declare the scalar variable @param
Solution 1
The query which you have written needs to be corrected .Since you have selected multiple values you need to use in
clause .
Select col1,col2....
from TableName
where prop_id in (@PropID)
Solution 2
in stored procedure you can directly pass the parameter and split the values using a function inside stored procedure.
if parameter is directly passed to a sql query instead of stored procedure, then concatenate the parameter values using a join and pass to datasetenter image description here
CodeKingPlusPlus
Updated on July 09, 2022Comments
-
CodeKingPlusPlus almost 2 years
I have a Sql Server reporting services project. I have a dataset query called Total where I select certain data based on a parameter:
select ... from ... group by ... having prop_id = @PropID
Now to populate a list of multiple values for this parameter, I have a dataset query called AllProps that selects all possible prop_id's:
select prop_id from proposal order by prop_id
Now in the Report Data Pane I select the parameter properties from @PropID and fill out the forms as follows:
Under General I have, Name: PropID Data type: Text (I select "Allow multiple values") Under Available values I have, Get values from a query Dataset: AllProps Value Fields: prop_id label field: prop_id Under Default Values I have, Get values from a query Dataset: AllProps Valuefield: prop_id
When I click the preview tab to see my report I get the following error:
An error occurred during local report processing. An error has occurred during report processing. Query execution failed for dataset 'Total'.
MUST DECLARE THE SCALAR VARIABLE '@PropID'.
Where did I go wrong? What is scalar variable in SSRS and how is it properly used?
Thanks