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!

 

[vidru]

I've got a solution that uses a SQL Expression field.

If you're using a SQL database like SQL Server or Oracle, you can create a SQL Expression that acts as a correlated subquery to check if the loan has a "Complete" record.

I've found that in order to use a correlated subquery, the table name in the subquery must be aliased for it to work properly (if you're using a table that's already used in the report).

Say your main report table is named Loans. Create a SQL Expression called 'Complete', with the following text (including the parentheses):
[tt]
(SELECT COUNT(LoanNumber) FROM Loans L1 where LoanNumber = Loans."LoanNumber" AND Status = 'Complete')
[/tt]
The record selection formula would look like:
[tt]
({Loans.Status} = "In")
and
({%Complete} = 0)
[/tt]
This would return one row for each loan number that has an "In" status, and there isn't a row for the same loan number with a "Complete" status.

Hope that helps you somewhat.

-dave

 

[kimchavis]

Im not sure if I understand what you mean. This is being run on an Oracle database, but I dont really understand what I need to do a correlated subquery. Is there any other way?

 

[vidru]

I was afraid you'd say that...

How about some information about your environment (version of Crystal, table(s) used, sample data, expected output, and the contents of your record selection formula).

With a little more info, I might be able to help you understand the proposed solution better.

-dave

 

[kimchavis]

Im using v8.5,

My tables are:

dbor.loan
dbor.pscat.document_relations
(left out from loan to document relations)

The loan table houses all the loans and loan level detail. The document relations houses the statuses. It lists each loan every time a new status is inputted. Therefore you can have multiple listings of the loan with 50 statuses. I need to look within those statuses to see if it has a status of "in" but not a status of "complete". If the loan doesnt have a status of "in", I dont want it to show on the report. If it has a status of "in" and a status of "complete" then I dont want it to show on the report. But if it has a status of "in" and it can have any other status with it(except complete), I want the loan to show on the report with all statuses associated with it.

There is nothing in my "record selection" criteria, however, right now I have a formula that is doing this based on the "group selection" criteria: Sum ({@if }, {dbor.LOAN.LOAN_ID}) > 0. The problem with this is, if it does run, it takes 30-45 minutes, but usually it times out and you have to shut down crystal. I hope this helps.

I really appreciate your help with this.

Thank you

 

[vidru]

I use SQL Server, but I believe all of the syntax I'm using will work in either one (although I'm a bit iffy about those periods in the table names - linked servers?).

Create two SQL Expression formulas, one for finding if the loan has a "Complete" status, the other to check for "In" status:

In the Field Explorer (Insert menu > Field Object), right click on SQL Expression Fields, and choose 'New'. Name the expression "Complete". In the SQL Expression Editor, enter the following (you didn't give me the status field's name, so you might need to replace 'status' below with the correct field name):
[tt]
(select count(loan_id) from dbor.pscat.document_relations dr where loan_id = dbor.loan."LOAN_ID" and status = 'Complete')
[/tt]
Create another SQL Expression named "InStatus":
[tt]
(select count(loan_id) from dbor.pscat.document_relations dr where loan_id = dbor.loan."LOAN_ID" and status = 'In')
[/tt]
Go the Report menu > Edit Selection Formula > Group, and either comment out what you've got in there, or delete it. Then go to Report > Edit Selection Formula > Record, and enter this formula (again, since I'm not sure of the name of your Status field, you may have to change this a bit):
[tt]
({%Complete} = 0)
and
({%InStatus} > 0)
[/tt]
The idea here is to get all of the processing to happen on the database side. You should see a pretty noticeable increase in speed (if it works at all!!!).

-dave

 

[vidru]

Initially, you posted...

My tables are:

dbor.loan
dbor.pscat.document_relations

Yet in your post in the other thread you referenced a different table:
[tt]
(select count(loan_id) from dbor.pscat.document_relations dr where loan_id = dbor.loan."LOAN_ID" and PSCAT_LOANS_DOCUMENTS_RELATION."STATUS" = 'Complete')
[/tt]
Makes it a little difficult to help without accurate information.

Replace the green text below with the actual name of the "Loan" table on the main report, and replace the blue text with the actual name of the "Document_Relation" table.

For the 'Complete' SQL Expression:
[tt](select count(loan_id) from DocumentRelations dr where loan_id = Loan."LOAN_ID" and status = 'Complete')[/tt]

For the 'InStatus' Expression:
[tt](select count(loan_id) from DocumentRelations dr where loan_id = Loan."LOAN_ID" and status = 'In')[/tt]

I'm also working under the assumption that a field named 'Loan_ID' exists in both of your tables (and that's how you're linking them), and that in your 'DocumentRelations' table, there is a field named 'Status' that has values like 'In' and 'Complete'.

Hope we cleared some of that up...

-dave

 

[kimchavis]

Hi Dave,

IM sorry, your right, I didnt give you the full table names. I did as you said and changed my sql expression to: (select count(loan_id) from PSCAT_LOANS_DOCUMENTS_RELATION dr where loan_id = V_UNION_ALL_LOANS."LOAN_ID" and status = 'complete')

To answer your question about my linking, Both tables do have loan id. IM doing a left outer from the loan table to the pscat document relations table.

When I put this query in now, revising it as you said,, this is the error I get:

"ODBC error: [microsoft][ODBC driver for Oracle] [Oracle] ORA-00942: table or view does not exist"

I click ok on that one and get:

"Error in compiling SQL Expression: Syntax error found here"
After pressing okay on this one it places the cursor in front of the c in count.

It doesnt make sense about the tables not existing, they are both in the dropdown.

Any thoughts on this one???

 

[vidru]

Wish I had Oracle to test on. It all looks OK, and works for me using SQL Server. I've verified that the SQL syntax I used is valid for Oracle as well.

The only other thing I can suggest is trying the Crystal Oracle ODBC driver instead of the MS one.

-dave