Crystal Reports selecting records by earliest date 1

[mikew013]

Hi there. i need to select records by earliest date in a report.
what i am exactly trying to do is select only customers who started a billing with us in a specific quarter only if they became a new customer during that quarter. Here's my select expert for selecting by quarter:
if ({?QuarterNum} = 1) then
{VIEW_SPR_AR_COMPANIES.ENTRY_DATE} in Calendar1stQtr
and year({VIEW_SPR_AR_COMPANIES.ENTRY_DATE})={?YearNum}
else if ({?QuarterNum} = 2) then
{VIEW_SPR_AR_COMPANIES.ENTRY_DATE} in Calendar2ndQtr
and year({VIEW_SPR_AR_COMPANIES.ENTRY_DATE})={?YearNum}
else if ({?QuarterNum} = 3) then
{VIEW_SPR_AR_COMPANIES.ENTRY_DATE} in Calendar3rdQtr
and year({VIEW_SPR_AR_COMPANIES.ENTRY_DATE})={?YearNum}
else if ({?QuarterNum} = 4) then
{VIEW_SPR_AR_COMPANIES.ENTRY_DATE} in Calendar4thQtr
and year({VIEW_SPR_AR_COMPANIES.ENTRY_DATE})={?YearNum}

and i found this grouping formula elsewhere at this site :
{INVC_HEADER.DATE_CREATED} = minimum({INVC_HEADER.DATE_CREATED})

 

[lbass]

You could simplify this to:

datepart("q",{VIEW_SPR_AR_COMPANIES.ENTRY_DATE}) = {?QuarterNum} and
year({VIEW_SPR_AR_COMPANIES.ENTRY_DATE}) = {?YearNum}

Then for your record selection formula, use:

{INVC_HEADER.DATE_CREATED} = minimum({INVC_HEADER.DATE_CREATED},{table.customer})

Replace {table.customer} with your customer field (assuming you have grouped on customer).

-LB

 

[mikew013]

IT ONLY KINDA WORKED. I KNOW SOME OF THE DATA NEEDED AND IT DID NOT MAKE THE REPORT. THAT DATA DISSAPPERED WHEN I GROUPED BY COMPANY NAME.THIS IS MY GROUPING FIELD NAME:{VIEW_SPR_AR_COMPANIES.ENTRY_DATE}

ANY MORE IDEAS WOULD BE VERY HELPFUL!!!!

 

[lbass]

Please don't use caps.

You must mean when you added the group selection formula. The grouping on company wouldn't have changed the record set. You should use the company as your Group #1, and date as your Group #2 (if you need to group on date for some reason). If you need the company name as Group #2, you will need a different approach.

Please describe more about your report structure.

-LB

 

[mikew013]

Only on grouping level. I need it to pull only the records from a companies first invoice, not the first invoice in a particular quarter, but I need to produce the report for any specific quarter hence the selecting by quarter.
thanx for your help. It is very much appreciated

 

[lbass]

Define your group structure please. What fields are you grouping on in what order. What should the resulting report look like? Please show a little mockup of the hoped-for result.

-LB

 

[mikew013]

group structure is pretty simple...grouping on only company name
the rprot should look like this...


Quarter #
Period: (date range)

Company Name Post Date Invoice # Total Revenue

 

[lbass]

THIS IS MY GROUPING FIELD NAME:{VIEW_SPR_AR_COMPANIES.ENTRY_DATE}

You appeared to be grouping on a date field.

I think you should lay the entry_date field and the INVC_HEADER.DATE_CREATED in the detail section and then add a copy of your selection formula to the detail section. Then observe how the fields relate to each other.

What happens to customers who had a create date in one quarter and the first entry date in the next? There may be some logic like that that you would have to address in your formula.

-LB

 

[mikew013]

The selection formula returns true. If I switch to grouping by date the report breaks @ the group selection formula. even changing the group name fiels fails to produce a working report. Thanks for your help again!

 

[mikew013]

Sorry! Put the wrong field name when describing my grouping name before!

 

[mikew013]

I've got it working now...Thanx again for all your help