Crystal 9 Select Expert forumula 2

[VickieT]

Hi Everyone

I'm using Crystal 9 against a SQL database with Eden Financial Systems and reporting on Benefits and Deductions.

We have employees who retain their benefits until the end of the month of their termination (this is their benefit_end_date and should be included in the report I'm doing for billing purposes. I'm grouping the report on the benefits I want to report on and have a parameter set up to choose which organization we are reporting on. I want to include the terminated people in my data because they have benefits until the end of the month and used the following formula:

{ESYEMBDD.BEN_START_DATE}<={?Effective Date} and
isnull({ESYEMBDD.BEN_END_DATE}) or
({ESYEMBDD.BEN_END_DATE} > {?Effective Date})

This does include the right people but puts them in all the groups whether they are members of that organization or not. Examples of Groups are Accident Insurance, Basic Life Insurance, Cancer Insurance

Examples of organization would be clerk of court, airport authority, tax collector

Say Kathy Doe of the Tax Collector Organization has cancer beneifts and basic life insurance. Her termination date is October 13, 2004 but she has benefits until October 31, 2004. She will show up also in all the other organizations as well as her own because something in my formula is incorrect. I'm sorry if this is long but I'd rather give you too much info than not enough.

If you need more please let me know and I appreciate your help so much.

Vickie

 

[synapsevampire]

A great way to convey requirements is to use the following:

Crystal version
Database/connectivity type
Example data (this means as it's in the tables, not report putput)
Expected Output (this should be based on the example data)

-k

 

[lbass]

Where is the reference to your organization parameter? Your formula should look something like:

{table.organization} = {?org} and
{ESYEMBDD.BEN_START_DATE}<={?Effective Date} and
(
isnull({ESYEMBDD.BEN_END_DATE}) or
{ESYEMBDD.BEN_END_DATE} > {?Effective Date}
)

Also notice the change to the parentheses.

-LB

 

[VickieT]

LB thank you sooooo soooo much for your help. This worked perfectly. Can you tell this Crystal newbie why the parentheses made the difference? Thanks again, as always, this forum is a lifesaver!

 

[mbarron]

With the parans:
Record returned are ones where the table org is the same as the org parameter and those records either either have a null date or a date greater than effective date

Without the parens:
Records returned are those where the table org is the same as the org parameter and have a null date or records that have a date greater that the effective date - but possibly not the table org equal to the org parameter.


Mike

 

[mbarron]

With the parans:
Record returned are ones where the table org is the same as the org parameter and those records either either have a null date or a date greater than effective date

Without the parens:
Records returned are those where the table org is the same as the org parameter and have a null date or records that have a date greater that the effective date - but possible not the table org equal to the org parameter.


Mike

 

[lbass]

Otherwise the select statement could be misinterpreted as:

(
{ESYEMBDD.BEN_START_DATE}<={?Effective Date} and
isnull({ESYEMBDD.BEN_END_DATE})
) or
{ESYEMBDD.BEN_END_DATE} > {?Effective Date}

-LB

 

[VickieT]

Thank you both so much. I have about 20 reports that this was causing an issue with and now they are all perfect. Thanks again.