Multi-value filters. Tricky problem with the use of wildcard. SSRS 2005

• 10e5x

  Default port

  Points: 1445

  More actions

  December 16, 2012 at 3:01 am

  #272047

  Hi pros out here,

  In my report i would like to create an optional multivalue filter for @accessVar. However my biggest problem is that available values of @accessVar makes use of wildcards. e.g 'BT%'. It will be ok if i does not allow multiple selection. But i do need multiple select. And to my knowledge i cant use IN operator with %wildcard.The following is my query in dataset.

  SELECT PASS_M, ENTRY_DT, EXIT_DT, WORKED_HRS, ACCESS_LOCATION_X, IC_N, COMPANY_M, CONSECUTIVE_D

  FROM TEMP_TARGET

  WHERE (CONSECUTIVE_D >= @consecDays) AND (ENTRY_DT BETWEEN @startDate AND @endDate) AND

  (ACCESS_LOCATION_X LIKE @accessVar) AND

  (IC_N LIKE @icVAr)

  Urgent help needed. Any suggestion?

• 10e5x

  Default port

  Points: 1445

  More actions

  December 16, 2012 at 8:51 pm

  #1568997

  anyone?

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  December 17, 2012 at 6:58 am

  #1569132

  I forget exactly how SSRS 2005 passes in Multivalued parameters to the query, so this might not work, but one method could be

  Use a delimiter function e.g. Jeff's one here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

  then change your query to something like:

  SELECT PASS_M, ENTRY_DT, EXIT_DT, WORKED_HRS, ACCESS_LOCATION_X, IC_N, COMPANY_M, CONSECUTIVE_D

  FROM TEMP_TARGET tt

  CROSS APPLY (SELECT Item FROM FROM dbo.DelimitedSplit8K(@accessVar, ',')) av

  WHERE (CONSECUTIVE_D >= @consecDays) AND (ENTRY_DT BETWEEN @startDate AND @endDate) AND

  (ACCESS_LOCATION_X LIKE av.Item + '%') AND

  (IC_N LIKE @icVAr)

  This is completely air code but hopefully will get you started.

  Cheers

  Gaz