How can I display a date from a specific row in a list of data?

[QABrian]

I am trying to get my report to print an inspection date in the Group Footer 1 row. My problem is I need a date from a specific row number within a grouping.

The below example is a piece of my report. I created a formula in the section expert for GH2 to suppress RunningTotal greater than 5 because I am only interested in looking at the 5 latest inspections (which is why I ordered the Date Inspected in decending order) regardless of how many inspections there may be. As you can see in the GF1 row there are 7 lots inspected the last two are suppressed.

RTotal | Date Insp. | Inspection Status | Part Number
GH2 1 | 20100324 | REJECT | XXXX
| | |
GH2 2 | 20100301 | REJECT | XXXX
| | |
GH2 3 | 20090918 | ACCEPT | XXXX
| | |
GH2 4 | 20090903 | REJECT | XXXX
| | |
GH2 5 | 20090820 | REJECT | XXXX
| | |
GF1 | 20090514 | 7 LOTS INSPECTED |

I created a running total in an attempt to give each inspection a line number because I only want the Date Insp. in line number 5 to display in the GF1 row. I don't know how to tell Crystal Reports to look at the date in line 5 and display it in group footer 1.

I tried using the "Insert Summary" and chose "Min" for the Date Inspected field but it displayed 20090514 which is the min value of all seven rows including the suppressed data I am not interested in. I only want it to look at the "min" of what is displayed or tell Crystal directly what line number I want.

I hope I was clear enough.

Thanks

 

[IanWaterman]

Create a formula

@LOt5

If #RT = 5 then datefield else date(1900,01,01)

In GF1 add a maximum summary on this formula.

Ian

 

[lbass]

You can use one of two different methods. The simplest is:

{table.date} = NthLargest(5,{table.date},{table.group#2})

A second way would be to create a formula like this:

whileprintingrecords;
datevar mindate;
if {#rt} = 5 then
{table.date} := mindate;

In the group footer use:

whileprintingrecords;
datevar mindate;

In the group header, add a reset in case there are groups without five records;

whileprintingrecords;
datevar mindate;
if not inrepeatedgroupheader then
mindate := date(0,0,0);

-LB

 

[QABrian]

Thanks guys for the help. Maybe I'm not doing something correctly?

IanWaterman, I tried your suggestion I put the formula in GH2 and the other in GF1. I get the error "This Field Cannot Be Summarized". Crystal doesn't seem to want to summarize running totals.

lbass, I tried your first suggestion I used NthLargest (N, fld, condFld) in my formula. I entered NthLargest (5,{IH_INSPECTION.IH_DATE_INSPECTED} ,GroupName ({IH_INSPECTION.IH_LOT_NUMBER}) ) I get the error "This Field Cannot Be Used As A Group Condition Field", refering to GroupName ({IH_INSPECTION.IH_LOT_NUMBER}). I haven't tried your second suggestion yet as it's a little more involved.

 

[lbass]

Don't use the groupname in the formula, use the field itself for the group condition: {IH_INSPECTION.IH_LOT_NUMBER}.

-LB

 

[QABrian]

I put the formula in GF1 the result I get is "TRUE" instead of the date on line 5.

 

[lbass]

What formula did you put in GF1?

-LB

 

[QABrian]

Your recomendation was:
{table.date} = NthLargest(5,{table.date},{table.group#2})

I origionally entered:
NthLargest (5,{IH_INSPECTION.IH_DATE_INSPECTED} ,GroupName ({IH_INSPECTION.IH_LOT_NUMBER}) )

I made the correction of removing the groupname. This is what I am using:

{IH_INSPECTION.IH_DATE_INSPECTED} = NthLargest (5,{IH_INSPECTION.IH_DATE_INSPECTED} ,{IH_INSPECTION.IH_LOT_NUMBER} )

 

[lbass]

Sorry, the formula should just be:

NthLargest (5,{IH_INSPECTION.IH_DATE_INSPECTED},{IH_INSPECTION.IH_LOT_NUMBER})

-LB

 

[QABrian]

Thanks much, you really know your stuff. :-D