x

Creating A Multi Value LIKE Expression in SSRS

Hi there,

I've got a report that I'd like to include a multi-value parameter that will filter the dataset using multiple LIKE values. I've created the parameter called 'Year' and ticked the box 'Allow Multiple Values' and added a default list of years of '2016', '2015', and '2014'. The source table should be queried with these values on the column YEARS_STATUS which can contain values such as:

 BOUGHT 2014
 SOLD 2014 FOR CASH
 SOLD 2014 FOR CREDIT
 FINANCED 2014
 BOUGHT 2015
 SOLD 2015 FOR CASH
 SOLD 2015 FOR CREDIT
 FINANCED 2015
 BOUGHT 2016
 SOLD 2016 FOR CASH
 SOLD 2016 FOR CREDIT
 FINANCED 2016

Therefore if the report user selects 2014 and 2015 I want the report to return all possible values similar to SQL using the LIKE operator,

e.g.

 WHERE YEARS_STATUS LIKE '%2014%' OR YEARS_STATUS LIKE '%2015%'

I need this coded into an expression within the report level, not the SQL. So far I've gone into teh Dataset Properties pane and gone to Filters to try to Add one. I selected Expression of '[YEARS_STATUS]' with the Operator 'In' and the Value of '[@Year.IsMultiValue]', however I am getting the error "Cannot compare data of types System.string and System.boolean".

Can anyone advise how to correctly create a multi value LIKE expression?

TIA

more ▼

asked Apr 19, 2016 at 03:11 PM in Default

avatar image

David 2 1
1.1k 56 61 67

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x690
x93
x23

asked: Apr 19, 2016 at 03:11 PM

Seen: 373 times

Last Updated: Apr 20, 2016 at 09:26 AM

Copyright 2017 Redgate Software. Privacy Policy