Q:

Concatenating multivalue parameters in SSRS

I'm having trouble getting this query to run correctly. It works like I want it for a single value, but if I select multiple values on my report it only runs the query for one of them. I want the query to return a handful of values for each server name that is in my @Servers multivalue parameter.
I modified my parameter value to have the full name of the tags and changed the query to just say IN(@Servers) without concatenating, and that works like I'd expect it to so the issue seems to be with concatenating strings in the IN statement - just not sure how I need to change this to make it work, listing out each tag from each server in my parameter values would be overboard for the user to select them all.
SELECT tag, time, value
FROM piarchive..piplot
WHERE tag IN(
    @Servers+'_LogicalDisk(C:)_% Free Space',
    @Servers+'_LogicalDisk(E:)_% Free Space',
    @Servers+'_Memory_Available Mbytes',
    @Servers+'_Memory_Pages/sec',
    @Servers+'_Paging File(_Total)_% Usage',
    @Servers+'_Processor(_Total)_% Processor Time',
    @Servers+'_Ping'
)
AND intervalcount = 100
AND time BETWEEN FORMAT(CAST(@StartTime AS datetime), 'dd-MMM-yy HH:mm:ss') AND FORMAT(CAST(@EndTime AS datetime), 'dd-MMM-yy HH:mm:ss')
ORDER BY time

EDIT to add working solution I found, in addition to the one below:
SELECT tag, time, value
FROM piarchive..piplot
WHERE LEFT(tag,12) IN(@Servers) 
AND (tag like '%LogicalDisk(C:)_% Free Space'
    OR tag like '%LogicalDisk(E:)_% Free Space'
    OR tag like '%Memory_Available Mbytes'
    OR tag like '%Memory_Pages/sec'
    OR tag like '%Paging File(_Total)_% Usage'
    OR tag like '%Processor(_Total)_% Processor Time'
    OR tag like '%Ping'
)
AND intervalcount = 100
AND time BETWEEN FORMAT(CAST(@StartTime AS datetime), 'dd-MMM-yy HH:mm:ss') AND FORMAT(CAST(@EndTime AS datetime), 'dd-MMM-yy HH:mm:ss')
ORDER BY time

A:

If you don;t have a column with the server name isolated you might find it easier to calculate the server name then test that column.
Here's and example. This will work with multiple values from SSRS too, you just won;t be able to test it outside of SSRS.
DECLARE @t TABLE (tag varchar(100))

INSERT INTO @t 
SELECT 'SrvA_Processor(_Total)_% Processor Time'
UNION SELECT 'SrvA_LogicalDisk(C:)_% Free Space'
UNION SELECT 'SrvA_LogicalDisk(E:)_% Free Space'
UNION SELECT 'SrvA_Memory_Available Mbytes'
UNION SELECT 'SrvA_Memory_Pages/sec'
UNION SELECT 'SrvA_Paging File(_Total)_% Usage'
UNION SELECT 'SrvA_Processor(_Total)_% Processor Time'
UNION SELECT 'SrvA_Ping'
UNION SELECT 'SrvB_Processor(_Total)_% Processor Time'
UNION SELECT 'SrvB_LogicalDisk(C:)_% Free Space'
UNION SELECT 'SrvB_LogicalDisk(E:)_% Free Space'
UNION SELECT 'SrvB_Memory_Available Mbytes'
UNION SELECT 'SrvB_Memory_Pages/sec'
UNION SELECT 'SrvB_Paging File(_Total)_% Usage'
UNION SELECT 'SrvB_Processor(_Total)_% Processor Time'
UNION SELECT 'SrvB_Ping'
UNION SELECT 'SrvC_Processor(_Total)_% Processor Time'
UNION SELECT 'SrvC_LogicalDisk(C:)_% Free Space'
UNION SELECT 'SrvC_LogicalDisk(E:)_% Free Space'
UNION SELECT 'SrvC_Memory_Available Mbytes'
UNION SELECT 'SrvC_Memory_Pages/sec'
UNION SELECT 'SrvC_Paging File(_Total)_% Usage'
UNION SELECT 'SrvC_Processor(_Total)_% Processor Time'
UNION SELECT 'SrvC_Ping'

DECLARE @Servers varchar(10) = 'SrvA'
SELECT * 
    FROM @t
    WHERE LEFT(tag,CHARINDEX('_',tag)-1) IN (@Servers)

Edit this to suit your table names and columns and this should work as-is in a report as long as your parameter is multi valued

