CODEWITHSUNDEEP
sql-serverreporting-servicesparameters
CodeKingPlusPlus
CodeKingPlusPlus

Reputation: 16081

Sql Server Reporting Services Must Declare the scalar variable @param

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

Upvotes: 6

Views: 29691

Answers (2)

Ramkumar Sambandam
Ramkumar Sambandam

Reputation: 557

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

Upvotes: 2

praveen
praveen

Reputation: 12271

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)

Upvotes: 5

Related Questions