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]

I know, but I did it 3 times. Not sure what is going on here. When I open it locally it is marked but when opened through the link it isn't. Never saw this happen before.

 

[ramblinrose]

This is another attempt to send file with data.

 

[lbass]

I'm confused. If I comment out your current detail suppression formula and just use...

whileprintingrecords;
//Suppresses records that are less than the max date of 3 or more consecutive records because they are no longer on a series
shared numbervar maxdttm;
{Command.drawnStamp} <= maxdttm

Both 127 and 188 show--and they both should. 127 does not have 3 good results.

-LB

 

[ramblinrose]

Overnight more test results were added to the database one of them being for group 175 which is right before the group 188. Not sure why that record made a difference to me being able to see 188. When I ran the report this morning you are correct 188 is now visible.

I removed the commented part (see below) of the detail suppression and it is suppressing 175 as expected.

shared numbervar maxdttm;
{Command.drawnStamp} <= maxdttm
or

I am now having a problem with the group 4b and 4c suppression. When I apply the group suppression I lose GroupHeader 4b and 4c for 188.

I feel like I am trying to get a report writing tool to function like am application. We thought about writing a SQL code to do all of this filtering prior to designing the report, but again it is so complex we don't think that could be done either.

 

[lbass]

What is the suppression formula you are using for GH4b and c? Is it already in the report you sent?

-LB

 

[ramblinrose]

Yes it is. I just have it commented out so I can see what is happening to the details.

 

[lbass]

Please delete the following formulas from the main report:

{@cnt}
{@maxdt}
{@maxdttime}

They otherwise can change calculations.

Then in the subreport, change the {@cnt} formula to include a check on whether the driver name is numeric or not:

whileprintingrecords;
numbervar cnt;
if {@Check if flagged for group} = 0 and
not isnumeric ({@Driver or Phlebotomist})then
cnt := cnt + 1;
if {@Check if flagged for group} = 1 then
cnt := 0;
cnt

It is the cnt formula snd the shared maxdttime formula in the subreport that are critical to the accuracy of the suppression in the main report.
Then rerun the report and resend. I can't test this because the changes require the rerun.

-LB

 

[ramblinrose]

I have made the revision as you stated. It initially looks great! I am attaching the report for you to look at. I need to request a current listing of dairies on series from our Environmental Health dept for validation.

I really do appreciate all of your efforts in helping me get this. If this is correct, it will certainly be time for celebration.

 

[lbass]

You will be the best judge of whether it’s working or not.

-LB

 

[ramblinrose]

I went through the validation and this report looks like it works. The difference I had with their report from another application were explained to them and it looks like the issues are on their end. I was showing this report to one of my peers and we are having a difficult time figuring out how the subreport is passing the correct information.

Would you please take the time to explain it. We hope to learn from your expertise.

Thank you.

 

[ramblinrose]

I heard back from our Environmental Health dept. They stated the report was exact with 1 exception. There is a dairy that had been in the series and then qualified to be out due to 3 good consecutive records and then back on again due to a bad test that was picked up by a creamery driver. I have the detail suppression formula on the main report suppressing any records received about the first bad record that are picked up by a creamery driver, thus suppressing this particular bad record. However, since it starts a new series, it needs to be on the report as the first bad record. I have tried working with the formulas already within the report and the variables, but I get errors due to evaluation timing.

 

[lbass]

I realized this later and was trying to get up the energy to immerse myself again in this very long thread. Could you please see if this fixes this issue before I do more on this?

See thread 11-16 20:24.

Change the {@cnt} formula in the subreport just after the variable declarations to:

If {@check if flagged for group}= 0 and
(
(
{table.groupfield}<>previous({table.groupfield}) and
Isnumeric({@Driver or Phlebotomist})
) or
Not isnumeric({@Driver or Phlebotomist})
) then
cnt := cnt + 1;
If {@check if flagged for group}=1 then
cnt:=0;
cnt

-LB

 

[ramblinrose]

Linda - I completely understand how you feel.

I edited the {@cnt} and it does not work the way I need it to. That addition is counting a good record from a driver which is not what I want. See below. You can see the history of this particular dairy and see where they were on a series and than future testing took them off and then they are to be back on. The report does not account for if they are removed from a series and then back on. The record for 11/5 should be the only record showing for this dairy. Test following will also be on the report until a minimum of 3 good consecutive records.

Thank you
Rose Mary

 

[lbass]

The report you sent me is unlike the previous reports and does not include any of my formulas.

-LB

 

[ramblinrose]

Sorry, that report is what I use to see the history.

The report is attached.

 

[lbass]

Using this report, please explain what is incorrect about the results. Also, I see that you have done something to change the suppression of the group header so that a number is repeating that didn't before.Please explain.

-LB

 

[ramblinrose]

I had to change the suppression on the group header because what was happening was I would get results of a dairy under the previous dairy. This would happen if the group count was > 1.

As far as this report goes. If you look at page 11 (District 3) Dairy 206, it only shows the group header and not test results. If you look at the graphic sent earlier (19:19) you will see where they are on series because of the record from 11/5. That is the first record of a new series. And then of course the following record 11/26 would not be on the series list because it is from a creamery driver and not tested for the same lab test.

 

[lbass]

Can't get to page 11 without running the report--this was after I made the changes as follows which you need to build into the report exactly as shown:

In the subreport:

{@cnt:
whileprintingrecords;
numbervar cnt;
if {@Check if flagged for group} = 0 and
(
(
isnumeric({@Driver or Phlebotomist}) and
{@Lab Test Description}<>previous({@Lab Test Description})
) or
not isnumeric({@Driver or Phlebotomist})
) then
cnt := cnt + 1;
if {@Check if flagged for group} = 1 then
cnt := 0;
cnt

In the main report, change the suppression formulas to:

//GH4b section suppression:
whileprintingrecords;
numbervar cntgrp4;
shared numbervar maxdttm;
cntgrp4 > 1 or
maxdttm = maximum({Command.drawnStamp},{@Lab Test Description})

//detail section suppression (do not add the additional lines you show):
whileprintingrecords;
shared numbervar maxdttm;
{Command.drawnStamp} <= maxdttm or
(
isnumeric({@Driver or Phlebotomist}) and
{@Lab Test Description}=previous({@Lab Test Description})
)

Once you have incorporated these, please resend the report saved with data if it is not working as expected and identify a specific instance or two where it is not working.

-LB

 

[ramblinrose]

First thing that I can see is with the removal of ({Command.drawnStamp} <= Minimum ({@Bad Record DrawnStamp}, {@Lab Test Description}) and {@Check if flagged for group} = 0) part of the suppression formula for the Details section I am getting records that are good and are before the first bad record.

 

[lbass]

Send report and identify instance this occurs please.

-LB