Group Selection Problem

[kimchavis]

I was hoping someone could help me with this problem Im having:

Currently, I have a report that lists the loan number multiple times based on status, ex:

loan # 123456 loan amt 100,000 status in
loan # 123456 loan amt 100,000 status suspended
loan # 123456 loan amt 100,000 status completed

The report wants to look at all loans in the database that has an in status but not a complete status. The way I currently do this is to group by loan number and do a formula at the detail level, if status = in then 1 else if status = complete then -1000. I do a sum based on this and put a group selection criteria on the report to show if the sum of the formula is >0.

This causes the report to go through all records in the database and usually times out.

Is there a more efficient way to do this?


I would appreciate any assistance with this.

Thank you!

 

[IanWaterman]

Kim

Wy can't you just use record selection and filter on status = in

Or am I missing the point

Ian Waterman

 

[kimchavis]

Because that will only pull back records with an in status. I need it to pull back all records that have an in status but not a complete status. There are other statuses that need to be displayed.

 

[vidru]

Kim:

Did you try what I'd suggested in your other post?

-dave

 

[kimchavis]

Yes, I tried it, it gave me an error, when I tried to do the first formula. "Error in Compiling SQL expression". My formula was:

(select count(loan_id) from dbor.pscat.document_relations dr where loan_id = dbor.loan."LOAN_ID" and PSCAT_LOANS_DOCUMENTS_RELATION."STATUS" = 'Complete')

 

[vidru]

OK, looks you supplied me with bogus table names in the other thread.

I'll post more in the other thread.

-dave