Suppression and Inclusion Criteria in Crystal Report XI

You use record selection rules to get the records you want. (Click the Select Expert button or use Report >Select Expert or Report>Selection).

Suppression expressions hide data in the report but do not stop it being selected from the database.

If you think of a conditional mail merge in Microsoft Word where you can have for example 10 letters.  The data or the criteria you define in that conditional mail merge indicates which letter to be used and populated with data.  Those letters that are not being used used do not come up at all.  Only the letters where there is criteria that matches the conditions come up.

I am trying to do a similar thing in Crystal Reports.  I have 10 different detail sections, each representing a different letter.  I was using Suppression code but this does not seem to work as I have explained as it is exclusionary.

Is this possible to have a conditional mail merge like this in Crystal Reports with 10 different letters in the one report?  I really do not want to have 10 different Crystal Reports.

If you are talking about including or excluding sections then you can only do that using conditional suppression.
If the rules are based on data values I don't see any reason why you should not be able to suppress the sections you don't want.

Agree it is not clear how you need to do this.

You can use selection criteria to ensure you get the records you want but it may also exclude some records.

Conditional suppression would be something like


x <> "1" OR y <> "2"

mlmcc

I am trying to suppress data with something like the following...

{Table1.Field1}<>"1" AND
{Table2.Field1}<>2 AND
((NOT ("HELLO" IN {Table3.Field1})))

We are trying to suppress data from any record that has a "1" in Field 1 of Table 1 AND a "2" if Field 1 of Table 2, and does not contain the word "HELLO" in Field 1 of Table 3.

For some reason when I AND these together, I am not getting the results I should be.  When I run them individually they work just fine.

Are any of the fields potentially NULL?

Nulls must be tested for first.  If Crystal encounters a NULL field it stops doing the comparisons.

Try something like
Not(IsNull({Table1.Field1})  OR  IsNull({Table2.Field2})  OR IsNull({Table3.Field1}))
AND
({Table1.Field1}<>"1" AND
{Table2.Field1}<>2 AND
(InStr({Table3.Field1},"HELLO") = 0))

mlmcc

Every field in all three tables has a value.  I have tried what you suggested and I am still getting the wrong results.  If I run the InStr part by itself, I get 6 records.  This should mean that I should never get more than this once I factor in the fields of the two other tables.  I am getting far too many records returned still.

How are the tables linked?

mlmcc

I think we need to be quite clear about what you are ateempting.
Are you specifying a conditional suppression rule on detail sections?
Or are you specifying a record selection formula?

I have 20 tables all together but only using three of them for this suppression.  They all have left outer joins now.

If you select from the right side of a left join you destroy the left join and it reverts to an inner join.

I have multiple letters setup in one Crystal Report.  In the Section Expert, I have multiple details, where each detail is marked to Suppress and has SQL code tied to this suppression.  I am doing this to populate the data specific for each letter.  Again this is very similar to a conditional mail merge in MS Word.  I have multiple criteria for each letter that determines which letter is to be used.

Does it show it as an inner join again then?

This is good but I need the string NOT to include HELLO...

How do I add this in?

Try something like
Not(IsNull({Table1.Field1})  OR  IsNull({Table2.Field2})  OR IsNull({Table3.Field1}))
AND
({Table1.Field1}<>"1" AND
{Table2.Field1}<>2 AND
(InStr({Table3.Field1},"HELLO") = 0))

It is pulling all values that do not have HELLO within the string.

No but that is how the join operates.
Which os one reason why we must be clear about what you are doing.

So are you setting up a record selection rule or are you setting up a conditional suppression expression. They will be different.

conditional suppression.

OK .....Based on this .....
'We are trying to suppress data from any record that has a "1" in Field 1 of Table 1 AND a "2" if Field 1 of Table 2, and does not contain the word "HELLO" in Field 1 of Table 3.'

Table1.Field1}="1" AND
{Table2.Field1}=2 AND
((NOT ("HELLO" IN {Table3.Field1})))

I would just question the "1" and the 2.
If the field is text then you need the " " but id it is number then you don't have them.

This still does not provide me with those records with HELLO in Table3.Field1?

How do you use a record selection?

Sorry it should have been > 0 rather than = 0 for selection.  I thought you were using conditional suppression.

Not(IsNull({Table1.Field1})  OR  IsNull({Table2.Field2})  OR IsNull({Table3.Field1}))
AND
({Table1.Field1}<>"1" AND
{Table2.Field1}<>2 AND
(InStr({Table3.Field1},"HELLO") > 0))

Are you doing this through the selection filter or suppression?

mlmcc

We are using conditional suppression with multiple letters and not record selection.  I do not think record selection will work.

It doesn't seem to make any sense when I run each of the three conditions separately that they work fine.  When we AND them with each other - IT DOESN'T WORK.  This biggest issue seems to be the condition searching for HELLO within the string.

Had you tried peter's solution above.  It is the same as mine without the ( ).
https://www.experts-exchange.com/questions/22624576/Suppression-and-Inclusion-Criteria-in-Crystal-Report-XI.html?anchorAnswerId=19254391#a19254391


mlmcc