Suppression or filter based on criteria 1

[ramblinrose]

I have a report that captures Dairies in a Series of Bad Test Results. When a dairy gets their milk tested and it is bad, it goes into a series and requires special testing. They can get off the list by having 3 consecutive good results. I need to be able to use a suppression or a filter to remove any records included prior to the 3 or more consecutive good results.

In this example record 1 puts them on a Series.
Record 6 takes them off the series and they would stay off until the next bad sample (record 8)
I would only want record 8 and the next records until I get 3 more good consecutive records.

We are using Crystal Reports 2018 with a Microsoft SQL database.

Any help is greatly appreciated.

Thank you in advance,
Rose Mary

 

[ramblinrose]

The reason I did not want the subreport is because it slowed down the processing from seconds to minutes.

 

[lbass]

Yes, it will be slower, but I can’t think of a way around it.

-LB

 

[ramblinrose]

 

[ramblinrose]

 

[ramblinrose]

Hello lbass,

I am back from vacation and got back to this report. I think I have just about everything the way it should be except for the groups that have no relevant data. I tried the suppression formula and it did not work. I am attaching the report. There is no confidential data as I have removed the Dairy names. Would you please take a look at it?


Thank you.

RM

 

[lbass]

I can't verify that fixes the report, since the report needs to run once changes are made, so please implement these changes and then let me know.

In the section expert, add the following suppression formula to the Group Header #4b->suppression->x+2 formula area:

whileprintingrecords;
shared numbervar maxdttm;
maxdttm=maximum({Command.drawnStamp},{@Lab Test Description}) //note NO colon before the equal sign here

Then also add the a suppression formula to the Group Header #3->suppression->x+2 formula area:

whileprintingrecords;
shared numbervar maxdttm;
maxdttm=maximum({Command.drawnStamp}, {Command.Producer_Number}}) //note NO colon here

I see that you ended up using a number variable for a datetime stamp instead of a date variable, which is fine and necessary in your case, but it bothers me to see the former formula for the shared datevar maxdt in both the sub and the main report. If you were in fact using this shared variable, the formula's presence in the main report would affect your results. If you had used it, it should only have been defined (set to a value) in the subreport and only referenced in the main report.

The detail suppression formula seems to be working properly, although I'm unsure of the impact of your addition to correct for bad timestamps.

-LB

 

[ramblinrose]

The suppression formula for 4b worked, but not the one for #3.

The reason I ended up using the number variable is to make sure the order of test results were correct if in the same day. I needed to suppress any good records from the report before the first bad records. The number variable unfortunately is how the date is stored in the database using UNIX all the way to milliseconds. I have to do the following formula to convert to a proper date in our time zone.

stringvar drwnstmp := mid(totext({Command.drawnStamp},0,''),1,10);
datetimevar dsGMTdt := dateadd("s",tonumber(drwnstmp),date(1970,01,01));
Date(ShiftDateTime (dsGMTdt,"GMT,0", ""))

 

[lbass]

Please trying using the GH4 suppressions formula for the GH3 header and see if that works. If it doesn't, can you please resend the report saved with data after you added my suppression formulas? Thanks.

-LB

 

[ramblinrose]

That did not work. The report is attached.

Thank you

 

[lbass]

Ok, I see the problem. I can't fully check this without running the report, but think this should work.

First, Suppress the Group #3 Header. Then insert another group header #4 section. Make sure the sub is in GH4a, and then put the Group Header #3 group name in GH4_b. The GH4_c section still holds the Group #4 name. Now you will see the GH#3 repeating with each GH#4 instance. Create a formula in the formula editor:

//{@grp4cnt} to be placed in GH4_b:
whileprintingrecords;
numbervar cntgrp4 := cntgrp4 + 1;

Add a reset formula to the Group Footer #3:

whileprintingrecords;
numbervar cntgrp4 := 0;

Then add a section suppression formula for GH4_b:
whileprintingrecords;
numbervar cntgrp4;
shared numbervar maxdttm;
cntgrp4 > 1 or
maxdttm=maximum({Command.drawnStamp},{@Lab Test Description})

-LB

 

[ramblinrose]

Perfect!

Thank you so much. I could not of done this without you.

 

[ramblinrose]

Hello Linda,

I am back. This report works great except for one more scenario.

In the example below the record for 7/9 has a driver 19154. Once a dairy is put in the series the milk to be sampled while in the series can only be from an Inspector which is shown by name. The numbers are Dairy Creamery drivers. The first record that puts them on a series can be a driver but all other records during series testing can only be an inspector.

Can you assist me with this?

Thank you,
Rose Mary

 

[lbass]

What do you want to happen for a scenario like this? Please be specific.

-LB

 

[ramblinrose]

The only time they want to see a record that a creamery driver (numeric value for Driver/Inspector)picked up is if it is the FIRST record in the series. In the previous example I show the 3rd record should not be displayed because it was picked up by a creamery driver even though it tested bad. The correct results would look like this:

I foresee another problem. That is if you remember if there are 3 consecutive records that are good, than they are no longer on the series and all records are not displayed until they are on the series again. So in the example here, this dairy will have 2 good consecutive records even though there is an invalid record suppressed between the two good records.

 

[lbass]

Try changing the second part of the cnt formula (22 Aug 18 16:31) to

If {table.results}=“Bad” and
Not isnumeric({table.driver/inspector}) then
Cnt := 0;

You can also suppress that detail record with a formula:

{table.results}=“Bad” and
Isnumeric{{table.driver/inspector}) and
{table.groupfield}=previous({table.groupfield})//use whatever is the group inside which you are counting the good records.

-LB

 

[ramblinrose]

Linda,

I have been fighting this report and trying to get 1 issue resolved without bothering you, but I have had no luck. I am attaching the report for what I have so far.

On page 7 Dairy 127 should not show because there are 3 consecutive good test results where the milk sample was picked up by an inspector that follow the bad test. So they are no longer in the series.

On the same page Dairy 188 should stay on the report because they only have 2 good results where the milk sample was picked up by an inspector. There is a good record between the 9/20 and 10/30 date that is suppressed and not being counted because it was picked up by a creamery driver and is not valid for testing when the dairy is in a series. The count (@cnt) works correctly and does not increase for this record. This dairy should remain in the series until the 3rd good test is received from an inspector.

Now the problem is when I use the detail suppression formula shown below both dairy 127 and 188 are suppressed. Where 188 should not be suppressed.

​

whileprintingrecords;

​

shared numbervar maxdttm;

​

{Command.drawnStamp} <= maxdttm

If I can get this part figured out, I think the report will be complete.

Again, I wish I did not have to come back to you, but I gave in since after spending hours on hours trying to figure this out and having my colleagues look at it with no solution in sight.

Thank you.

 

[lbass]

You didn’t attach the report. I’ll take a look at this tomorrow once I see the report.

-LB

 

[ramblinrose]

Here it is.

Thanks

 

[ramblinrose]

Linda,

I sent the file more than once with data, but when I checked the link it appears that the report is not saving with data. Please let me know if you are not able to see the data.

Thank you.

 

[lbass]

It looks like you haven't checked "save with data" in the file menu. No data.

-LB