sql server reporting services: Unrecognized Parameter -- sql server reporting services

Unrecognized Parameter

Ben Holcombe
11/2/2007 11:25:00 AM

sql server reporting services:

I have a report where the dataset is text and not pulling from a stored
procedure but probably should be. The query includes populating a temporary
table. All has been fine passing parameters from the report into the select
statement that populates the temporary table until I added an additional
parameter that is multiselect requiring an IN operator. If I change the
operator to = then the report is ok. The error occurs during report
processing only. The query executes correctly in SSMS and in the Data tab of
the report. It also displays the report and you are able to populate the
parameters but when you press view to render the report you get an error
message indicating that you must declare the scalar variable. I was wondering
if someone else ran into a similar issue and knew of a workaround.

Re: Unrecognized Parameter

EMartinez
11/3/2007 3:11:50 AM

On Nov 2, 1:25 pm, Ben Holcombe
[quoted text, click to view]

The scalar reference is most likely due to the '=' being used instead
of the 'in' statement. Firstly, you will want to use a stored
procedure instead of just a standard query. Also, you will want to
pass the multi-select parameter value to the stored procedure and
parse it out (based on the commas) into a temporary table where you
can legitimately use "...where xxxxx in (select xxxxx from
#temptable)" Hope this helps.

Regards,

Enrique Martinez
Sr. Software Consultant