0

I am building a report with Microsoft SSRS (2012) having a multi-value parameter @parCode for the user to filter for certain codes. This works perfectly fine. Generally, my query looks like this:

SELECT ...
FROM ...
WHERE
 TblCode.Code IN (@Code)
ORDER BY...

The codes are of following type (just an excerpt):

C73.0
C73.1
...
C79.0
C79.1
C79.2

Now, in additon to filtering for multiple of these codes I would like to als be able to filter for sub-strings of the codes. Meaning, when the user enters (Example 1)

C79

for @parCodes The output should be

C79.0
C79.1
C79.2

So eventually the user should be able to enter (Example 2)

C73.0
C79

for @parCodes and the output would be

C73.0
C79.0
C79.1
C79.2

I managed to implement both functionalities seperately, so either filtering for multiple "complete" codes or filterting for sub-string of code, but not both simultaneously.

I tried to do something like

...
WHERE
 TblCode.Code IN (@parCode +'%')
ORDER BY...

but this screws up the Example 2. On the other hand, if I try to work with LIKE or = instead of IN statement, then I won't be able to make the parameter multi-valued.

Does anyone have an idea how to realize such functionality or whether IN statement pared with multi-valued parameters simply doesn't allow for it?

Thank you very much!

Wellenprinz
  • 1

1 Answers1

1

Assuming you are using SQL server

 WHERE     (
    TblCode.Code IN (@parCode) 
     OR
      CASE
          WHEN CHARINDEX('.',  Code)>0 THEN LEFT(TblCode.Code, CHARINDEX('.', TblCode.Code)-1)
          ELSE  TblCode.Code
      END IN (@parCode)
      )

The first clause makes exact match so for your example matches C73.0 The second clause matches characters before the dot character so it would get values C79.0, C79.1, C79.2 etc

Warning: Filtering using expressions would invalidate the use of an index on TblCode.Code

niktrs
  • 9,858
  • 1
  • 30
  • 30
  • Thanks for the code snippet. I implemented it and it works just fine for my purposes. However, this implementation only works so long as the sub codes are seperated by a dot. E.g. if I enter 'C7', the codes starting with 'C7' won't show up. Is there an easy way of filtering for whatever portion of a code I enter? – Wellenprinz Apr 08 '21 at 10:52