Create a conditional supression formula within the details section of a crystal report. Joe Brown used Ask the Experts™
on
I would like to suppress a details line in a crystal report based upon a certain condition of itself. Looking for some help in writing this conditional suppression formula.

When the Test ID is 175, 176 or 212 then I need only one of those to show based upon this hierarchy.
1. If 176, 175 and 212 all exist, then 175 and 212 should suppress (only show 176)
2. If 175 and 212 exist, and 176 does not, 212 should suppress (only show 175)
3. If neither 176 or175 exists then only 212 should show

Can someone help me write this properly?
Example-of-Report.pdf
FN_CofA_Form_Working.rpt
Comment
Watch Question

Do more with EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I think that you can do this by using formulas to get a count for each Test ID.  A summary on those formulas will give you a count for each code.  Create formulas like the following:

// Count_175
if {Test ID field} = 175 then
1
else
0

// Count_176
if {Test ID field} = 176 then
1
else
0

You don't need a formula for 212, since nothing is suppressed because of that ID (212 is only shown if the others aren't there).

There is a group on the report.  I assume that you're looking at the Test ID's within each group, so you want to check group summaries (as opposed to summaries for the entire report).  The suppression formula would be:

({Test ID field} = 175 and Sum ({@Count_176}, {group field}) > 0) or
({Test ID field} = 212 and Sum ({@Count_175}, {group field}) + Sum ({@Count_176}, {group field}) > 0)

Of course you should replace those field names with your actual field names.

176 is never suppressed, so there's no need to check anything for that ID.

If it's 175, suppress it if there is a 176 (it doesn't matter if there is a 212).

If it's 212, suppress it if there is a 175 or 176.  Rather than check the individual counts for 175 and 176, I just added them together.  If the combined count is > 0, then there is at least one 175 and/or 176, and 212 is suppressed.

This is all untested, but I think it will work.

FWIW, I think the report that you posted had saved data, but that was discarded because I got an error on at least one of your formulas (I'm guessing that it was the one that uses a QR code function).  So I had no data to test with.

James
IT Manager

Commented:
The group doens't matter in this case since it is just a group for the record number itself.

I created a formula named Count 175
if {Out_FNData.Out_FNData_TestID} = 175 then 1 else 0

I created a formula named Count 176
if {Out_FNData.Out_FNData_TestID} = 176 then 1 else 0

I created the suppression like so:
{Out_FNData.Out_FNData_TestID}=175 and Sum({@Count 176})>0
or {Out_FNData.Out_FNData_TestID}=212 and Sum ({@Count 175}) + Sum ({@Count 176}) >0

When all 3 of the Test ID's have a value, 176 is reported (as expected).
If I test it with only a value in 175 and 212 (no value in 176), it reports Test 176 with no value in the Actual column
If I test it with only a value in Test 212 (nothing in 175 or 176), it reports Test 176 with no value in the Actual column

Did I miss part of your instructions?

Commented:
The group doens't matter in this case since it is just a group for the record number itself.
I'm not sure what you mean by "record number", but the report that you posted is grouped by Out_FNData_LabID (which doesn't sound like a record number, in the normal CR sense).  IAC, if the report includes more than one set of records (each of which could have 175, 176 and 212 records), then you presumably want to check the counts for the set (ie. group).  If the whole report is just one set of records, then you can just check the grand total.

As for the suppression, are you using that suppression formula on the Test ID field (just that field), or the detail section?  You said in your first post that you wanted to suppress the detail line (ie. section), but it sounds like you're suppressing the field.

Also, when you say "no value in 176" and "nothing in 175 or 176", it sounds like you're saying that there are records for 175 and 176, but some field in the record is 0/blank/null ("no value").  Do the "missing" Test ID's not have any record at all; or is there a record, but one or more fields haven't been filled in yet?  My suggestion was based on the assumption that there was no record at all for those Test ID's.

James
IT Manager

Commented:
We are only passing, via a parameter, one set of records at a time. So the group is actually probably not even needed, but we keep it.

The suppression formula is being used on the details section, not the field. Sorry for any confusion.

Sorry, this is a bit confusing to explain. I will try to add more details that may help.  Each of the tests, including 175, 176 and 212 are present in the table that we are pulling from for each LabID. There will always be a value in Test ID 212. There could (or could not be) a value in the table for tests 175 and/or 176, but the TestID's are always present in the LabID. I have added an excel spreadsheet that shows 4 different sets of data to use as examples for this LabID.

Example 1 shows you how the data looks when there is only values for Test 212 (whether or not it reports the Vendor or the IFT value is determined in a different formula). In this case, the report should suppress the detail line for 175 and 176 because they both have NULL values. It should report the value for 212.

Example 2 shows there are values in 175 and 212, So, in this case, it should suppress test 176 and 212.

Example 3 shows there are values in 176, 175 and 212. 176 is always in charge, so test 175 and 212 should be suppressed.

Example 4 shows there are values in 176 and 212. Same result as Example 3 is expected. Suppress 175 and 212.

If a value exists for 176, always use 176. If no value exists for 176, use 175
4-Examples.xlsx
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You will need to modify the formulas
Name - Has175
if {Out_FNData.Out_FNData_TestID} = 175  AND Not IsNull({Out_FNData.Vendor}) then 1 else 0

Modify the 176 and 212 the same way

Your suppression on the record would be based on the value of the TestID and the sum of the appropriate fields.

//  Suppress them if the data is NULL
({Out_FNData.Out_FNData_TestID} = 176 AND Sum({@Has176}) = 0)
OR
({Out_FNData.Out_FNData_TestID} = 212 AND Sum({@Has212}) = 0)
OR
({Out_FNData.Out_FNData_TestID} = 175 AND Sum({@Has175}) > 0)
OR
//Suppress 175 and 212 when there is 176 data
({Out_FNData.Out_FNData_TestID} = 175 AND Sum({@Has176}) > 0)
OR
({Out_FNData.Out_FNData_TestID} = 212 AND Sum({@Has176}) > 0)
OR
//Suppress 212 when there is 175 data
({Out_FNData.Out_FNData_TestID} = 212 AND Sum({@Has175}) > 0)

mlmcc

Commented:
Pretty much what mlmcc said.

If you always have a record for each TestID and the real controlling factor is whether or not certain fields are null, then you need to check for those nulls.  Which fields do you need to check?  Looking at your Excel file, Vendor and IFT are usually both null at the same time, but there are some cases where Vendor is null, but IFT is not.  Do you need to show those records because IFT is not null, or is Vendor (or some other field) the deciding factor?  I am assuming that if Vendor is null, you don't want to see the record (and it doesn't matter if IFT is null or not).

Change Count175 and Count176 to also check for nulls, as mlmcc described:

if {Out_FNData.Out_FNData_TestID} = 175 and not IsNull ({Vendor field}) then 1 else 0

if {Out_FNData.Out_FNData_TestID} = 176 and not IsNull ({Vendor field}) then 1 else 0

For the suppression, you'll need to add a check for 176.  I didn't include one, because I thought that if there was a 176 record, you always wanted to see it.

({Out_FNData.Out_FNData_TestID} = 176 and IsNull ({Vendor field}))
or ({Out_FNData.Out_FNData_TestID} = 175 and Sum({@Count 176}) > 0)
or ({Out_FNData.Out_FNData_TestID} = 212 and Sum ({@Count 175}) + Sum ({@Count 176}) > 0)

So, if it's 176 and Vendor is not null, show the record.
If it's 175 and there aren't any non-null 176 records, show the record.
If it's 212 and there aren't any non-null 175 or 176 records, show the record.

mlmcc added a check for a count on 212 (Sum ({@Has212}), but you shouldn't need that, since you said that 212 always has a value.

FWIW, I noticed that you didn't include () around the And tests (eg. (a and b) or (c and d) ).  While you don't have to use () there, since And has precedence, I prefer to include them, to keep the order of evaluation clear.

James
IT Manager

Commented:
We have another formula where we determine if we will report the Vendor Result or the IFT result. There is a field on the report called @Actual Results and this determines if we are going to report the Vendor or the IFT value for each TestID. So either the vendor or the IFT result will come up under the Actual Results column based upon that formula.

Now we are trying to suppress two of the 3 tests for "Iodine Value" which are tests 175, 176 and 212. So we just need to work on a suppression formula that will have this hierarchy so only one of the 3 tests will be present.
If test 176 has a value in {@Actual}, then this test should be reported always and 175 and 212 must be suppressed and 176 remains
If test 176 has no value in {@Actual} but there is a value in 175, then 176 and 212 are suppressed and 175 remains
If test 176 and 175 both do not have a value in {@Actual} then 176 and 175 are suppressed and 212 remains

So, with this information, do you feel we could use either of your formulas and trade out the {@Actual} field rather than the Vendor and IFT fields to obtain the same results?
Commented:
I haven't tried to analyze those formulas, but it seems like checking @Actual to see if it's null would work, as long as the result from @Actual will be null when there's no value, as opposed to just blank (spaces or an empty string).  If @Actual could be blank or null, then you could check for both.

James

Do more with Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.