crystal reports-selecting most recent date but not grouped by date 1

[mully1]

hi, this is my 1st post so please excuse any mistakes i make. I have a crystal 10 report querying oracle 10g db. the report counts & details number of claims made for school meals, grouped by benefit type the guardian receives, then grouped by benefit office for each benefit type. There may be multiple benefit types against each guardian so i only want to count the most recent benefit type end date (which is normally a future date but may be a valid past date). Each guardian may claim for more than 1 child but each child should only appear on the report once.At the minute my report is showing a child's claim details under each benefit type the guardian has, not just under the most recent. Can anyone help please? many thanks.

 

[kskid]

1. Just insert another group by Child
2. Insert a sort on date descending
3. Move the child objects into the child group header
4. Suppress the detail section that contained the child objects

-lw

 

[lbass]

This is actually a little complex. You need to create a SQL expression (field explorer->SQL expression->new) something like this (we'll call it {%maxdt}:

(
select max("benefitenddate")
from "table" A
where A."guardian" = "table"."guardian"
)

Substitute the actual field name for benefitenddate and guardian, and the table name for "table". Leave the "A" as is. Then in the main report, go to report->selection formula->record and enter:

{table.benefitenddate} = {%maxdt}

Then you should see only one benefit type per guardian. You can still use your current groups.

-LB

 

[mully1]

Hi, just wanted to thank both lbass & kskid for taking the time out to help me. Lbass your solution worked a treat, many thanks.