Date Range Evaluation ?

[Tepzepi]

Hi All,

I know this answer is in this forum, but after reading multiple entries, I am non the wiser, due to lack of understanding of the exact formula set I would require.

My report (already up in Crystal Reports 4 Other Topics FAQ149-363008 which Naith is kindly providing input on.) requires formulas I did not realize would be required until design time. The report essentially has 3 main columns with 3 sub columns each, main column 2, and 3 being sub reports. The sub reports will be linked on the date parameters. I have now discovered I require 2 sets of date parameters (or at least this is how I assume I would have to write this report)in order to exclude specific data.

Date parameter 1 is based on invoice_date, this being the main date that the primary and sub reports need to be based on. Within this on the primary report I need to filter Ren_date which would typically follow the same dates as invoice_date, but not in all instances. Hence the parameter.

If the above and below comes across as unintelligible, please refer to the above-mentioned FAQ.

I have a formula in the details of the report to identify policies that are incomplete/uninvoiced and policies that have not been renewed as follows:

If
isnull({PLY_POLICY.PLY_INVOICENO})
then
"Incomplete/Uninvoiced"

else if
{PLY_POLICY.PLY_RENDATE}in {?From Unprocessed Date} to {?To Unprocessed Date}
then
"Renewal Overdue"

In the select expert I have the following formula:
{PLY_POLICY.PLY_INVOICEDATE} in {?From Date} to {?To Date} and
{PLY_POLICY.PLY_RENDATE} in {?From Unprocessed Date} to {?To Unprocessed Date} and
not ({PLY_POLICY.PLY_TRANSACTIONTYPE} in ["C", "L"]) and
{PLY_POLICY.PLY_RENEWABLE} = -1

The report obviously only generates the incomplete and Renewal Overdue data.

This part of the formula :
{PLY_POLICY.PLY_RENDATE} in {?From Unprocessed Date} to {?To Unprocessed Date} and
not ({PLY_POLICY.PLY_TRANSACTIONTYPE} in ["C", "L"]) and
{PLY_POLICY.PLY_RENEWABLE} = -1

is what I am using to filter the incompletes and renewals overdue, and I assume I need an evaluation formula on this in order for it not to override the Invoice_Date parameters. This is where I need help please, as I've not the know how to select the correct formula/s to achieve the desired result.

The Primary report will need to have the above Ren_date policies filtered, as will the first sub report, but the second sub report will be only the ren_date policies.

I apologise for the potentially confusing narrative of my actual requirement.

Thanks in advance and Regards

Warren I'd rather be stupid for 5 minutes than for the rest of my life.

 

[synapsevampire]

Perhaps demonstrating some data and expected output would help.

For this:

"is what I am using to filter the incompletes and renewals overdue, and I assume I need an evaluation formula on this in order for it not to override the Invoice_Date parameters. This is where I need help please, as I've not the know how to select the correct formula/s to achieve the desired result."

What does override mean here? If you want both, use an OR instead of an AND, and then within the report filter accordingly.

({PLY_POLICY.PLY_INVOICEDATE} in {?From Date} to {?To Date})
OR
({PLY_POLICY.PLY_RENDATE} in {?From Unprocessed Date} to {?To Unprocessed Date}
and
not ({PLY_POLICY.PLY_TRANSACTIONTYPE} in ["C", "L"])
and
{PLY_POLICY.PLY_RENEWABLE} = -1)

I've no idea if this is your intent, hopefully this will help to steer you in the right direction.

Also make sure that the SQL being generated is passed along to the database, how you construct the record selection criteria will decide this.

The record selection criteria should pull back ALL rows required, if you have subsets of data that you need to present, you can always suppress/display/sum/etc. those based on some criteria.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Tepzepi]

Hi synapsevampire,

Thanks for your response.

"What does override mean here?"
Indeed, not phrased accurately.
What I mean is, I wish to have ({PLY_POLICY.PLY_INVOICEDATE} in {?From Date} to {?To Date})as the main selection dates, and within this date range, I need to have the ability to either include or exclude policies which have the below criteria.

({PLY_POLICY.PLY_RENDATE} in {?From Unprocessed Date} to {?To Unprocessed Date}
and
not ({PLY_POLICY.PLY_TRANSACTIONTYPE} in ["C", "L"])
and
{PLY_POLICY.PLY_RENEWABLE} = -1)

Regardless,I have since discovered that I'm getting myself totally lost on this report, hence I'm looking for an consultant who could help me create this report from scratch via email if possible.

Thanks & Regards

Warren
I'd rather be stupid for 5 minutes than for the rest of my life.

 

[synapsevampire]

Your record selection criteria looks sound to me.

Send along an email, I'll work it out for you, and there are plenty of other CR professionals hereabouts.

kai@informeddatadecisions.com

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com