Crystal Insured Report

[110950]

Our Insured Report is supposed to give us all Insured loans dated from January 1, 2002 forward, except the loans coded in item tracking as "CRCD" (meaning that the Certificate has been received).

We need all loans equal to "T" except the loans that have the borrower code of "NA" and if any exception items have a received date, we don't want it. Meaning that we do not want the entire loan, period.

The objective is to get all insured loans within the time frame noted above and that is all we want. We don't care about any open exceptions and we don't want any loans that have the "CRCD" code. What is happening with our code is that it appears that we get the loans as specified in the formula, however, if there is an open exception, then we are getting the loan even if there is a "CRCD".

Here's the way we have it written:

{ITEMP.ITEM_TYPE} = "T" and
not ({ITEMP.BORR_CODE} in ['NA']) and
IsNull({ITEMP.RECEIVED_DT}) and
{ITEMP.ITEM_NUM} <> &quot;CRCD&quot; and
({XREF2.INSURED_DT}) > Date (2002, 01, 1)

Hopefully, you can tell us what we are doing wrong. We are currently using Crystal Reports 8.0 with LoanQuest and we are using Windows98 Platform.

 

[FVTrainer]

Is each loan a single record? Or are there multiple records per loan? If there are multiple records per loan, are exceptions their own record?

 

[Madawc]

What is 'an open exception'? It's not clear. Also with dates, maybe you should add
not isnull ({XREF2.INSURED_DT}) and

Madawc Williams
East Anglia, Great Britain

 

[lbass]

It's a little hard to tell what's going on here without sample data, but I'm guessing that you have multiple records per loan, so that some records per loan will not have &quot;CRCD&quot; while one or more other records for the same loan will. When you use the record select above, you are just filtering out the records that have &quot;CRCD&quot;, not the loan itself. To filter out the loan, remove the &quot;{ITEMP.ITEM_NUM} <> &quot;CRCD&quot;&quot; from your record select formula, and then create a formula field (@CRCD} for the detail section:

if {ITEMP.ITEM_NUM} = &quot;CRCD&quot; then 1 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@CRCD}, {table.loannumber}) = 0

This assumes that you have a group on {table.loannumber}. This will return only those loans with no CRCD records.

-LB