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

 

[lbass]

First create a SQL expression {%maxbdt}. You will have to adapt the punctuation to your database, but it would look something like this:

(
select max("date_collected") //you might need to use A."date_collected" within the parens
from "table" A
where A."Results"='Bad' and
A."Dairy"="table"."Dairy"
)

Then create these formulas:

//{@bdate}:
if {table.results}="Bad" then
{table.date_collected}

//{@gdate}:
if {table.results}="Good" and
{table.date_collected}>{%maxbdt} then
1

Then in the section expert, select the detail section->suppress->x+2 and enter:

{table.date_collected}>maximum({@bdate},{table.dairy}) or //assumes a group on dairy
sum(@gdate},{table.dairy})>=3

-LB

 

[lbass]

PS. You would probably also want to suppress the group section for "Dairy" using:

sum({@gdate},{table.dairy})>=3

-LB

 

[ramblinrose]

Thank you for your quick response.
I don't know that this will work if the dairy has a bad record before they have 3 consecutive records which keeps them on the series list until they get 3 consecutive records.

 

[lbass]

I thought you just needed to show the “Bad” record if it was the most recent and then any “Good” records after that which are building towards the 3 necessary to eliminate from the list. So you are saying that you want to show all records after 3 good ones even if they go back and forth from bad to good until you get another three good ones?

-LB

 

[ramblinrose]

This gets confusing. Once a dairy goes off series (Series 1) by having a minimum of 3 consecutive good records they stay off the series until their next bad record (Series 2) which puts them on the Series list again. We need to show all the records from the beginning of that series (Series 2, Records 8-11) until they get a minimum of 3 consecutive good records again.

In the sample above record 6 would take the Dairy off the list and they would show up again with record 8 and stay on the list displaying the series history (8-11) until their 3rd good consecutive record. The reason for the history needing to be displayed for the current series, is because a Dairy will have a couple of levels of warnings before getting their milk degraded.

Hope this helps.

 

[lbass]

Can you present the report in descending order by date?

-LB

 

[ramblinrose]

They would prefer that it is not since they have to analyze the status.

 

[lbass]

Not sure why the order would matter.

-LB

 

[ramblinrose]

I guess it is just a matter of reading top down, versus bottom up. With that being said, if it is the only way we can present the data, then it will have to do. They are currently using paper and manually logging results, so any solution I can present will be better.

 

[lbass]

Okay, the following works, with records sorted in ascending order as you already have them.

First save a copy of the report and rename it with "-sub" as the extension. Then reopen the original report and insert a second group header section (dairy). Move the groupname into GH_b, and then insert your new subreport into the GH_a section. Link the sub to the main report on {table.dairy} (edit->subreport links). Then click on the subreport tab to open the sub and create the following formulas:

//{@cnt} to be placed in the detail section:
whileprintingrecords;
numbervar cnt;
if {table.results}="Good" then
cnt := cnt + 1;
if {table.results}="Bad" then
cnt := 0;
cnt

//{@maxdt} to be placed in the detail section:
whileprintingrecords;
numbervar cnt;
shared datevar maxdt; //be sure to make this a shared variable
if cnt >= 3 and
{table.results}="Good" then
maxdt := {table.date_collected};
maxdt;

//{@reset} to be placed in the group footer:
whileprintingrecords;
numbervar cnt := 0;

Next suppress all sections within the subreport (but not the subreport object itself). In the main report, go to format->format subreport->check "suppress blank subreport". The subreport should now disappear. Also in the main report, format both GH_a and "GH_b to "suppress blank section."

To make the group header disappear when there are no relevant records, in the section expert, select GH_b->suppress->x+2 and enter:

whileprintingrecords;
shared datevar maxdt;
maxdt = maximum({table.date_collected},{table.dairy}) //notice there is no colon

Then select the detail section->suppress->x+2 and enter:

whileprintingrecords;
shared datevar maxdt;
{table.date_collected} <= maxdt

Format the detail section also to "suppress blank section".

In the formula editor, create a {@reset} formula and place it in the group footer section and suppress:

whileprintingrecords;
shared datevar maxdt := date(0,0,0);

-LB

 

[ramblinrose]

Thank you for your time. I will try this a little later and post my results.

You are appreciated.

 

[ramblinrose]

Okay, I am trying the steps given, but I am not sure about the reset and where it goes. I do not have any groups in the subreport. The issue I am finding is that the maxdt presents the max date for the records following the good sequence. In the example below the 8/7 and tests following should not show the date 5/4 if we are going to be suppressing based on that date field.

Thank you

 

[lbass]

Add a group on dairy to the sub, just to keep things consistent with the formulas i presented. I assume you have linked the sub on {table.dairy} and that there is also a group on dairy in the main report. There is a reset within the sub for the cnt variable, and a reset in the main report for the shared maxdt variable. Both are in the dairy group footer.

The maxdt variable in the subreport is supposed to retain the value for all following records until another set of three “good”s. In the main report, you are only using the maxdt variable for section suppression. I can’t tell from your description how you actually implemented this, but I did test it here, and it works according to my understanding of what your desired results are.

If you are not getting the desired results, please describe in detail how you implemented the above solution.

-LB

 

[ramblinrose]

What I have is the following

GH1 Dairy
GH2 Lab Test (each dairy has multiple lab test and the series is based on the set of lab test
Detail --- {Date Collected} {Test Results}

I put the reset in GF2
The issue I am having now is the max date carries over from the previous group until the 3rd good record.

 

[lbass]

If the series are evaluated within each kind of test, then the subreport should be GH2_b and the resets should be in GF2. The sub should be linked on dairy AND on lab test. Also wherever I referenced {table.dairy} in formulas, use {table.labtest} instead.

If you want me to troubleshoot this directly, attach your Crystal report—you can remove dairy names, if you like.

-LB

 

[ramblinrose]

I think I got it to work, but I am not sure that I need a subreport. I am going to try using your process in the main report.

 

[lbass]

Yes you need a subreport—-it’s the only way to trap the date you need and make it available to suppress records that precede and include the record that reaches the threshold. The issue is that the relevant date is dependent upon a sequence of results (3 goods) that might repeat. Why don’t you want a sub? You shouldn’t be able to see it.

-LB

 

[ramblinrose]

Thank you, I just figured that out.

I just want to say that you are probably one of the best Crystal Report Writers ever and what a benefit to the rest of us in this forum.

 

[lbass]

Thank you.

-LB