Styjun

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)

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.

1. 
   

   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.

   
   


2. 
   

   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.