Help with Selection and Formula for a close date report

[Taij]

I am trying to do a close date report, based on the Job Date and Invoice Date.

A job may have multiple invoices.

I will be running the report every month. If a job has been invoiced in the month I am running the report for, then I want it calculate the close days by subtracting the Invoice date from the Job Date.


If the job has multiple invoices and an invoice is in the previous month (or some time before), I am assuming that the job has already been closed and I do not want this shown on the report. Where do I start, and how do I go about making the selection for this report?

 

[JohnBlaze]

What type of data are you reporting off of? Are you linking to an Access DB or an ODBC linked table(s)?

 

[synapsevampire]

Are you running it for a currentmonth, or for a previous month? Please post example data, expected output, Crystal version and database type/version.

I would create 2 parameters, startdate and enddate.

These would have a default value to take the current or previous months values, and then these could be used in the record selection formula to limit the rows.

That's the basics, if you want specifics, please supply them.

-k

 

[Taij]

I am using ODBC linked tables. I am using Crystal 8.5 with Oracle.

eg of data:

Jobdate Job Number Inv # Invoice Date

01/01/2003 c001 60001 01/12/2003
60002 03/3/2003

03/4/2003 c002 60005 04/12/2003
60006 04/28/2003

If I am running the report for the month of March I do not want to see job C001, it should show up only in the month of Jan.

If I am running the report for April, I should see job C002

 

[synapsevampire]

So you want to run the report for past periods?

Create 2 parameters:

startdate and enddate of type date

Set the default to 1/1/1970

This assumes that you are running the report for past periods as well as current.

Now create 2 formulas

@startdate
if {?startdate} = cdate(1970,1,1) then
cdate(year(currentdate),month(currentdate),1)
else
{?startdate}

@enddate
if {?enddate} = cdate(1970,1,1) then
dateadd("m",1,cdate(year(currentdate),month(currentdate),1))-1
else
{?enddate}

In the record selection formula place:

{table.invoicedate} >= @startdate
and
{table.invoicedate} <= @enddate

This will allow it to default to the current month if no parameters are passed, or you can override the dates using the parameters.

-k

 

[Taij]

Thanks for your help, but this does not exclude the jobs which have invoice billed in the months prior to the current date.
Any thoughts on this?

 

[lbass]

I think you could define the larger date period in your record select statement by using a discrete date parameter that defines the end of the period under consideration, e.g., 8/31/2003 for August 2003. The period should be long enough to include any previous invoices that would lead you to exclude a job. Let's say it would be an invoice within the last six months. Then for the record select, use:

{invoice.date} in dateadd(&quot;m&quot;, -6, {?date}+1) to {?date}

Then, because you are really interested only in the particular month of the parameter date, go to report->edit selection formula->GROUP and enter:

minimum({invoice.date},{table.jobno}) in dateadd(&quot;m&quot;, -1, {?date}+1) to {?date}

This should return only those job numbers with initial invoices in the month under consideration.

-LB