I have a report that runs a stored proc:
EXEC ra_spProjectCalendar @Month, @Year, @ProjectID
ProjectID is A multi-select dropdown. When a single project is selected, it works fine. If I select multiple projects, I get the error:
"Must declare scalar variable "@ProjectID"
It works fine when I run it from the Data tab, however, when I put it into Preview mode I have problems.
I got something similar to work by constructing the query as an expression - in your example
Some code in the target SP splits the multi-select string into a temporary table which is used in joins to create output.
I can't remember why we ended up doing it this way - it was right at the start of our move to SSRS and things have moved on a bit since - but it may have been the problem you describe.