Quick Formula question. 1

[techninut]

I am trying to suppress certain keywords from my report, and having a wee bit of trouble, hopefully someone could help. Just a few questions?

1) Does crystal syntax have any wildcard characters that will suppress anything with a certain word in it ?

ex. {alarm_entries.alarm_keyword} = "listen:"

will this ignore this - listen: <29> port available

in the suppress section of the Section Expert, how would I ignore anything with the word &quot;listen&quot; in it.

2)Is there anyway to list all the keywords at once rather than having to rewrite each statement like:

{alarm_entries.alarm_keyword} = &quot;listen:&quot; or {alarm_entries.alarm_keyword} = &quot;comm:&quot; or {alarm_entries.alarm_keyword} = &quot;.Daily:&quot; or
{alarm_entries.alarm_keyword} = &quot;[FE]:&quot;

this does not seem to work every time either.

I'm sure I just don't know the proper syntax, because I am able to write it in SQL and it works.

If anyone has any suggestions, please let me know.

- David

 

[synapsevampire]

You can use a like predicate:

{alarm_entries.alarm_keyword} like &quot;*listen*&quot;

Or use an Instr function:
instr({alarm_entries.alarm_keyword},&quot;listen&quot > 0

I don't think that you can do multiples at once.

You state that you want to suppress keywords, but I think what you want is to suppress any records which have a keyword within a field.

To do so, use Report->Edit Selection Formula->Record and place something like:

{alarm_entries.alarm_keyword} like &quot;*listen*&quot;
or
{alarm_entries.alarm_keyword} like &quot;*comm:*&quot;
or
etc...

This will pass the SQL to the database for processing.

-k

 

[techninut]

Thank you for your quick response.

I used this in the Suppress section and it works.

{alarm_entries.alarm_keyword} like &quot;*listen*&quot;
or
{alarm_entries.alarm_keyword} like &quot;*comm*&quot;

As well the selection expert

{alarm_entries.alarm_keyword} like &quot;*listen*&quot;
or
{alarm_entries.alarm_keyword} like &quot;*comm:*&quot;
or

works for what your wrote but that filters everything except &quot;listen&quot; and &quot;comm&quot;. Is there something I could write in the section expert that would filter these out?

The first way does work, I am just curious.

David

 

[lbass]

Something like this might work either for a conditional field or section suppression:

{alarm_entries.alarm_keyword} in [&quot;listen:&quot;,&quot;comm:&quot;,&quot;.Daily:&quot;,&quot;[FE]:&quot;] and
instr({yourtextstring}, {alarm_entries.alarm_keyword}) <> 0

-LB

 

[synapsevampire]

Sorry, for the record selection use:

not({alarm_entries.alarm_keyword} like &quot;*listen*&quot
and
not({alarm_entries.alarm_keyword} like &quot;*comm*&quot
etc...

This will eliminate them from the report, so you don't have to suppress.

Be careful using the IN as demonstrated by LB in the record selection, because it won't pass it to the database.

Once you have it in the record selection criteria, you don't need to worry over the suppression.

-k

 

[techninut]

Thank you both for your help. I am going to use the Select Expert route because this won't affect my total at the end. However, being that I am the type of person who likes to know how to solve problems, I propose this problem. When I was suppressing the keywords in the Format Section expert, it would hide these records, it still included these in the Total at the end. Any ideas on how to recalculate the values after suppression takes place? Like I said it works great in the Select Expert, I was just wondering.

David

 

[lbass]

You have to use running totals and apply the same conditional criteria to the running totals. So if you had used my formula above in a running total (using the running total editor), you would choose a field and a summary and then in the evaluate section, you would choose formula and add the formula:

Not({alarm_entries.alarm_keyword} in [&quot;listen:&quot;,&quot;comm:&quot;,&quot;.Daily:&quot;,&quot;[FE]:&quot;] and
instr({yourtextstring}, {alarm_entries.alarm_keyword}) <> 0)

This would exclude the suppressed rows from the running total.

-LB

 

[synapsevampire]

Think of it this way, by using the record selection method, the additional records are not in the report at all.

If you need those that the criteria omits for totaling, don't use this method, use the suppress method.

-k

 

[techninut]

I appreciate everyone's help. I get to keep my job now : )

David