Getting the minimum date with groups?? 1

[Oona8]

Hi
I'm using CR 8.5
I have a pb with a report which I want to show only the data corresponding to the lowest date. ex : I have 2 groups of employees, who did an action on a contract at a certain date. I only want to see the first action done (ie at the minimum date)
It works well untill I create a group based on the employee group, which causes the report to display the minimum date for each group and certain data are displayed twice.
Ex :

Paul Contract 1 06/04
Mike Contract 1 07/04

But I only want to see Paul's line but as they're in two different groups the system takes the minimum date per group
Is there any way to get the thing working without getting rid of my group or using a sub report (as I'll need to add the distinct count of the contracts ID of each group) ?

Thanks for your help

Oona

 

[synapsevampire]

If the date is the outer group, just place your columns in the group header of the date group, this will give you the lowest date.

As for a distinct count, right click the field and select insert summary->Distinct Count

You can now drag that field to the group header.

-k

 

[Oona8]

Hi Synapsevampire
Thanks for replying but I did that already, and it makes my report display the lowest date but it does it for every group. In my report I only want to see the lowest date of the action, whoever did it.
Ex : Paul creates contract1 on 06/02, and then Mike enters some new data on the same contract on 06/02. As they're in two different group, the contract line is displayed twice, though I only want to see Paul's line as he's the one who first did something on the contract. I don't want to see every group's first action on a contract .
Hope it makes sense..Oona

 

[synapsevampire]

Why do you have an employee group in there?

-k

 

[lyanch]

create a subselect in the where portion of your SQL to only return the minimum date:

and OuterTable.date = (select minimum(it.date) from
innertable it where it.contract_id = outtertable.contract_id)

I am guessing that it is the minimum date for the contract that you want to look at.

Lisa

 

[synapsevampire]

Lisa: They're on CR 8.5

 

[lyanch]

I am too... and i do it all the time

Lisa

 

[lbass]

After inserting a summary (minimum) on the date field for Group 2 (Employee Group), go to format section->Group 2 header (or footer, if that's where you are displaying)->suppress->X+2 button and enter:

{table.date} <> minimum({table.date},{table.contractID})

This will leave only the group instance with the minimum date displayed, but the suppressed records will still contribute to counts, distinctcounts, etc. for other report calculations.

-LB