I am trying to create a new filtering formula in crystal reports 2011 with something like, " {table.field} is like "*C*" but not like "*K*" " but I can not. Can you please help with the correct syntax or procedure for what I am trying to accomplish?

[Former Member]

I am trying to create a new filtering formula in crystal reports 2011 with something like, " {table.field} is like "*C*" but not like "*K*" " but I can not. Can you please help with the correct syntax or procedure for what I am trying to accomplish?

[Former Member · Accepted solution]

Hey Levi,

You can try the below logic .

{Command.USER} like '*C*' and not({Command.USER} like '*K*')

I hope this helps,

TDP

[JWiseman]

hi Levi,

i have attached a 'like, not like' sample report. please extract the contents and change the .txt extension to .rpt.

note that this technique uses 2 formulae that roll up the like & not like values from the multi value parameter. they add the wildcards needed to pass the filter to the database server. this is reflected in the Database menu > Show SQL display.

these formulae are then referenced in the Report > Selection Formulas > Record formula where they are split out.

i hope this helps,

jamie

Multivalue Parameter LIKE and Not LIKE in RS (Server Side).txt.zip

[DellSC]

Borja's response is great if you just want to hide the data on the report.  However, if you want to filter the data it may cause your report to run slowly because using formulas like this in the Select Expert can cause Crystal to pull the data into memory and filter there instead of pushing the filter to the database.

Are you linking tables together in your report or are you using a command or a stored procedure.  If you're just linking tables or you're using a command, I have a solution for you but I need to know what type of database you're connecting to.  If you're using a stored proc, you'll have to put Borja's formula in the Select Expert.

-Dell

[Former Member]

I am linking the tables through SQL Database, Dell. I have to total the dollar amount for what I am trying to filter onto the report, and somehow these running totals and summaries don't really work well with suppression formulas, at least in my experience with them but I will give it a go. Thank you both I'll keep you posted.

[Former Member]

The formula given by Borja did not work for this report, but it will be applicable on my other reports so thank you, Borja. Can we discuss what you had in mind, Dell?

[DellSC]

If you're linking tables, try this to filter out the records that you don't need instead of suppressing them:

1.  Create a SQL Expression to check for the letter C.  I'll call it {%HasC} - it would look like this:

CharIndex('C', "ORCRD"."CardName")

2. Create another SQL Expression that checks for the letter K.  I'll call this {%HasK}

3.  In the Select Expert, put the following in the Selection Formula:

{%HasC} > 0 and {%HasK} = 0

Because these are SQL Expressions, they will be pushed to the database for evaluation and only the records that have the 'C' but not the 'K' will be returned.

-Dell

[Former Member]

Hi levi,

Try this (supress formula):

InStr ({OCRD.CardName},'C' )<>0

AND

InStr ({OCRD.CardName},'K' )=0