Sql Server Reporting Services Must Declare the scalar variable @param

Question

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

Which version are you using? Have you checked in the "Data Sets" section of BIDS for Parameters? Is @PropID in there? — swasheck

praveen praveen · Accepted Answer · 2012-06-27T16:24:34

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)

Sql Server Reporting Services Must Declare the scalar variable @param

2 Answers