Writing a query that will handle three different labels

Question

I have 3 different drop down options. If I select one option at a time it works, but on selecting more than one it will throw an error. The three labels refer to three different versions. Everything works but when I select more than one option in the drop down it crashes. Need help understanding it why

The three parameters are Select all: Report1 Report2 Report3

Here my query:

SELECT
  ServerInfo.Version,
  ServerInfo.Type,
  ProjInfo.ProjName,
  ServerInfo.ServName
FROM
  ProjInfo, ServerInfo
WHERE ServerInfo.Version LIKE('%'+@ServerReport+'%')

Incorrect syntax near ','.

Query execution failed for dataset 'Report'. (rsErrorExecutingCommand)

An error has occurred during report processing. (rsProcessingAborted)

Is the issues has to do with casting? Or should I write down individual cases statements for each of the label options ? — LooperBoy
Please provide more info, what are the three labels values? What variable does it populate, ServerReport? What is the value of that variable when you select 1 or more option? — Michael Muryn

StevenWhite StevenWhite · Accepted Answer · 2019-03-21T17:47:09

The problem here is that the query is expecting a single string value, but the report is sending it an array of values when there are multiples selected.

In the parameter tab of your dataset properties, update the expression like this:

="," & Join(Parameters!ServerReport.Value, ",") & ","

This will combine the selected values into a single, comma-separated string.
Update your WHERE clause like this:

@ServerReport like '%,' + ServerInfo.Version + ',%'

This allows it to scan for the version string in the parameter string. The commas on the outside of both the joined parameter and the like statement prevent it from inadvertently matching partial strings.

Clarification: This is all assuming that you actually need the like statement in the first place. Normally you would just say:

ServerInfo.Version IN (@ServerReport)

This would accept multiple values without any other changes. But the strings would have to be an exact match.

Writing a query that will handle three different labels

Incorrect syntax near ','.

Query execution failed for dataset 'Report'. (rsErrorExecutingCommand)

1 Answers