SSRS query designer “must declare scalar variable” for max of multiple columns

Question

My goal is to get the max value of three different columns in my report's dataset. I'm using this query in the SSRS Query Designer for a new dataset:

SELECT  SurveyID,
    (SELECT Max(valueColumn) 
    FROM (VALUES (field1), (field2), (field3)) AS TableOfValues(valueColumn)) as MaxVal
FROM            ENVIRONMENTAL_EVW
WHERE        SurveyID = @surveyid

However, instead of popping up the dialog window as it normally does and giving me a chance to declare the variable, I'm just getting "must declare scalar variable" as an error.

If I remove the WHERE clause, it gives me an "unable to parse query text" error but still completes the operation correctly and displays the table of results. If I remove the subquery to find the max value, it correctly pops up the window and asks for the input to the variable.

Is this kind of subquery just not supported in an SSRS Dataset? How could I find the max value in an SSRS SELECT statement, specifically? Any of the values could be null.

I am using Visual Studio 2015, with the target server set to "SQL Server 2008 R2, 2012 or 2014" in case that matters. It's SQL-Server 2014.

Ella Ella · Accepted Answer · 2017-05-31T20:45:28

I used what I found in SQL Max of Multiple Columns to answer the question. I think this question is slightly different since it pertains to SSRS, and involves null values. I still don't know why the original version wasn't working, but this did:

SELECT SurveyID,
EnvironmentalID,

CASE
    WHEN field1>= COALESCE(field2,'') AND field1 >= COALESCE(field3,'') THEN field1
    WHEN field2 >= COALESCE(field1,'') AND field2 >= COALESCE(field3,'') THEN field2
    WHEN field3 >= COALESCE(field1, '') AND field3 >= COALESCE(field2,'') THEN field3
    ELSE field1
END AS MaxVal

WHERE SurveyID = @surveyid

This works in reverse for the min value, too, but instead of ' ' in the COALESCE, you need to put some value much higher than all the possible values.

SSRS query designer “must declare scalar variable” for max of multiple columns

1 Answers