0

I have an SSRS report with dataset that has query looking like this (simplified as the query is a lot more complex that this):

select * 
from myTable 
where country in (@Country)

@Country is a parameter and can have multiple values

It works fine, but we were asked to roll up all the values from one country to another one (the country is not in the list of parameters to be selected).

So for example we need to roll up all the records that belong to Canada to US if US was selected as one of the Countries essentially just replacing 'Canada' with 'US' in the dataset returned which is easy to achieve using REPLACE or even CASE statement. However, it gets a bit tricky with WHERE clause in the query. Does anyone know how to replace a string with another string so it's understood by IN operator?

If I simply do something like this:

select * 
from myTable 
where country in (replace(@Country, 'US', 'US,Canada'))

the query doesn't return anything and I understand the reasons behind it.

If I test this hardcoding the values like this:

select * 
from myTable 
where country in ('US', 'Canada')

it returns the correct rows of data. I think I'm looking for a way to join multiple values so it's understood properly by IN operator.

TIA, -TS.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tony
  • 149
  • 2
  • 4
  • 14

2 Answers2

1

You can use Dynamic SQL to achieve this. All you need to do is pass country name in declaration at first line.

Look at the code below:

DECLARE @SQL NVARCHAR(2000), @Country VARCHAR(30) = 'Canada'

SELECT @SQL = CONCAT('
    select 
        * 
    from myTable 
    where country in (', 
    CASE WHEN @Country = 'US' THEN '''US'', ''Canada''' else '@Country' end , ')'
)

EXEC sp_executesql @SQL, N'@Country VARCHAR(30)', @Country = @Country

Here's a fiddle : http://sqlfiddle.com/#!18/b7f49/4

Udit Solanki
  • 531
  • 5
  • 12
  • Hi Udlt, it works in SQL management studio. I'll try to use this in SSRS report. – Tony May 07 '18 at 16:36
  • For some reason, if you can't run this query in SSRS, then create a stored procedure of it and call it in SSRS. please mark it as an answer if it answered your question. Thanks! – Udit Solanki May 07 '18 at 17:11
  • Trying to do this on the whole query (I previously only tried a small fragment) and I'm having issues with syntax (I believe with quotes) even in the management studio. The query is over 200 lines long so it will take a while to debug it. – Tony May 07 '18 at 19:01
0

Possibly the same answer as SQL in (@Variable) query

You may need to add REPLACE to that function based on your requirements.

sfabrey
  • 26
  • 3
  • Unfortunately, I cannot create functions in there. Matter of fact, I cannot even touch this DB, so everything needs to be done within the query. – Tony May 07 '18 at 16:03