Greater than 30 days dating parameter 2

[jercisneros]

Hi all, I need to create a parameter that will give me invoices that have greater than 30 days dating, but would like to have a paramater that would also let the user select 60, 90, 120, and 150 days dating for aging invoices.
I have a field {AR_INVOICE_DET.INV_DATE} and would like to use it for the parameter.

Any help would be greatly appreciated.

Jerry

 

[synapsevampire]

Create the parameter as a numeric and preload it with your choices.

Now in the record selection formula (report->Edit selection formula->Record) use something like:

currentdate-{AR_INVOICE_DET.INV_DATE} >= {?MyParameter}

This should pass the SQL to the database too, which means optimal performance.

-k

 

[rhinok]

Synapsevampire's formulas is clean and simple.

If you want to break out data within aging groups rather than simply returning all records > # then you might benefit from using a Switch statemnt. Using a Switch statement also ensures that the record selection criteria will be passed to the database.

Do you want your intial aging to be from 30 - 59 days or 31 - 60 days (more common)? If you require 31 - 60 days, then you can take advantage of some Crystal functions (at least in part):

//Note: The example uses String parameter values
{AR_INVOICE_DET.INV_DATE} In
Switch
(
{?Invoice_Age} = '30', Aged31To60Days,
{?Invoice_Age} = '60', Aged61To90Days,
{?Invoice_Age} = '90', (CurrentDate - 91) to (CurrentDate - 120),
{?Invoice_Age} = '120', (CurrentDate - 121) to (CurrentDate - 150),
{?Invoice_Age} = '150', (CurrentDate[/color] - 151) to (CurrentDate - 9999),
)

If you really want aging from 30 - 59, then use the following formula:

//Note: The example uses String parameter values
{AR_INVOICE_DET.INV_DATE} In
Switch
(
{?Invoice_Age} = '30', (CurrentDate - 30) to (CurrentDate - 59),
{?Invoice_Age} = '60', (CurrentDate - 60) to (CurrentDate - 89),
{?Invoice_Age} = '90', (CurrentDate - 90) to (CurrentDate - 119),
{?Invoice_Age} = '120', (CurrentDate - 120) to (CurrentDate - 149),
{?Invoice_Age} = '150', (CurrentDate[/color] - 150) to (CurrentDate - 9999),
)

 

[rhinok]

Oops- don't forget to remove the last comma in the Switch statement. I've reposted the examples below:

//Note: The example uses String parameter values
{AR_INVOICE_DET.INV_DATE} In
Switch
(
{?Invoice_Age} = '30', Aged31To60Days,
{?Invoice_Age} = '60', Aged61To90Days,
{?Invoice_Age} = '90', (CurrentDate - 91) to (CurrentDate - 120),
{?Invoice_Age} = '120', (CurrentDate - 121) to (CurrentDate - 150),
{?Invoice_Age} = '150', (CurrentDate[/color] - 151) to (CurrentDate - 9999)
)

or

//Note: The example uses String parameter values
{AR_INVOICE_DET.INV_DATE} In
Switch
(
{?Invoice_Age} = '30', (CurrentDate - 30) to (CurrentDate - 59),
{?Invoice_Age} = '60', (CurrentDate - 60) to (CurrentDate - 89),
{?Invoice_Age} = '90', (CurrentDate - 90) to (CurrentDate - 119),
{?Invoice_Age} = '120', (CurrentDate - 120) to (CurrentDate - 149),
{?Invoice_Age} = '150', (CurrentDate[/color] - 150) to (CurrentDate - 9999)
)

 

[jercisneros]

Thank you both for your input. That solved my problem. You guys are great.

Thanks Again,

Jerry