SSRS Parameter Not Passing Into Query

Question

I have two parameters in my report, @CustomerId and @SalesType, however the @CustomerId parameter is not passing correctly into my report and results in the error Must declare the scalar variable "@CustomerId".

Below is the beginning of my query where this is happening.

Declare @customer nvarchar(max) , @sales varchar(10)
Set @customer = @CustomerId;
Set @sales = @SalesType;

The parameter is the correct case and is a multi-select parameter. Why could this be happening? If i define it it work, @SalesType works so im stuck why @CustomerId isnt.

Thank you.

well, you are taking a multivalue report parameter and trying to assign it to a simple parameter. What's the point on doing that, by the way?, why don't you use the report parameters directly? — Lamak
The report was extremely slow, and was recommended to define the parameters to stop parameter sniffing. Is there a specific way to define a multivalue parameter? — user3015316
So, is it using a stored procedure?, if not, then no parameter sniffing. If you are in fact using an sp, then you should implement this logic inside of it instead — Lamak
Can you post a screenshot of the parameters assignment tab in your SSRS dataset? — Tab Alleman
Post some screenshots of the parameters and the dataset parameter view, also write how exactly the procedure has the definition in the beginning — Rednaxel

TPhe TPhe · Accepted Answer · 2015-03-12T18:56:32

A multi-select parameter can contain multiple values, so it should be used more like this:

where table.customerId in (@CustomerID)

Perhaps you can elaborate on why you need to assign it to a variable if that won't work for you.

SSRS Parameter Not Passing Into Query

1 Answers