Group with No Values, Crystal IX

[Former Member]

I have

tried to achieve something on both regular reports and on cross-tab

reports, and have the same problem on both reports. I have field for loan ID, loan amount, and loan officer. I want to show the

count of the loan ID and the sum of the loan amounts for each loan officer during

a specific date range. The

problem is, if the loan officer doesn't have any applications during the

chosen date range, the loan officer doesn't appear on the report at all.

I want all the loan officers to be on the report, and if they don't have

any loans during that date range, I want it to show 0 for the count and

0 for the sum.

I am on Crystal IX and the data is sequal.

Can anyone help? Thanks.

[Former Member · Accepted solution]

Let me clarify my linking. I changed it to link the code from the company file to the LO code in the Tracking File but this might not work because the code in the company file applies to all sorts of codes. Then the loan ID in the tracking file is linked to the loan ID in the Extended Tracking Table. When I cleared all the links and started over, I was able to choose Left Outer Join for the Join Type and choose = for the Link type. Then on the ordering links window I marked "link ordering is enforced". I'm still not getting the results I need on the report. Perhaps I need to have IT build a separate loan officer table since my company table has so many other codes in it also?

Thanks.

[Former Member · Accepted solution]

Yes, if you create a table with only the loan officers in it and use it as the first table with the left outer join that should work. Another option is to filter how many records are returned within your report. Can you use the "Report->Selection Formulas->Record" option and put a statement in there like "companytable.code = (some unique characteristic of only loan officer records)"? If your company table has a field which marks the record as being a loan officer record only you may be able to filter on that field.

[Former Member · Accepted solution]

I'm still doing something wrong. Here is the code in the parameter selection:

{company_file.Code} = "LO" and {tracking_file_heart2.FU40#Date_Registered} = {?Reg Date Range}

"LO" is the code in the company file that identifies the specific loan officer ID as a loan officer.

Do I need to include the specific ID for each loan officer?

Also, when I go back to look at the linking, everything is linked ok, but the Link Options window is greyed out again. The only way I can get it to open up is to clear all the links and then link them all over again. Since I can't open the window to verify the left outer join in still chosen, perhaps it isn't holding the value?

Maybe my table linking isn't correct. In the company file, the "Code" = LO to identify the the record as a loan officer and the "ID" is the specific ID for each loan officer. I am linking the "ID" from the Company file to the "Loan Officer ID" in the Tracking File.

Thank you for your assistance.

[Former Member · Accepted solution]

The {company_file.Code} = "LO" may be correct. You don't have to look for each loan officer's id. Your table linking looks correct also. Things I can think of for the problem:

1. Try TRIM(UPPERCASE({company_file.Code})) = "LO" (some Code fields possibly not uppercase or contain extra spaces after the LO?).

2. You can see the SQL if you click on "Database->Show SQL query". Does the SQL show the left outer join?

3. Since you are linking 3 tables make the join between tracking file and the 3rd file be a left outer join also. Because if the tracking file returns a null (i.e. no loans found for the loan officer) then you won't get any records from the 3rd file if you don't have a left outer join.

[Former Member · Accepted solution]

1. I didn't try the TRIM option because there is a drop-down box when the loan officer ID is set up. I have to click on LO, so I know they are all upper case. If all else fails, I'll come back to this item.

2. I think the left outer join is the problem. Here is what the SQL shows. I can't get the Link Options to open up. When I clear out the linking and start over, it will show up, but only for the linking of the tracking table to the extended tracking table. I can't get it to open up for the company file to the tracking table. Thank you.

SELECT "TRACKING_FILE"."f451#loan_officer_name", "TRACKING_FILE"."f448#branch_code", "tracking_file_heart2"."FU40#Date_Registered", "TRACKING_FILE"."Loan_ID", "TRACKING_FILE"."f7443#loan_amount", "company_file"."Code"

FROM ("MVData"."dbo"."company_file" "company_file" INNER JOIN "MVData"."dbo"."TRACKING_FILE" "TRACKING_FILE" ON "company_file"."ID"="TRACKING_FILE"."f450#loan_officer_code") LEFT OUTER JOIN "MVData"."dbo"."tracking_file_heart2" "tracking_file_heart2" ON "TRACKING_FILE"."Loan_ID"="tracking_file_heart2"."Loan_ID"

WHERE "company_file"."Code"='LO' AND ("tracking_file_heart2"."FU40#Date_Registered">={ts '2008-09-01 00:00:00'} AND "tracking_file_heart2"."FU40#Date_Registered"<{ts '2008-09-08 00:00:00'})

ORDER BY "TRACKING_FILE"."f448#branch_code", "TRACKING_FILE"."f451#loan_officer_name"

[Former Member · Accepted solution]

Yes, it looks like the linking is the problem because of ""MVData"."dbo"."company_file" "company_file" INNER JOIN "MVData"."dbo"."TRACKING_FILE"". I believe this needs to be a left outer join. Since you can't seem to access the link options between company_file and TRACKING_FILE maybe it would be best to replace company_file with a new table that contains 1 record for each of your loan officers with the loan_officer_code as the single field (as you mentioned in a previous post). Then see if you can do a left outer join between that new table and TRACKING_FILE. Hope this helps.

[Former Member]

Hi Diann,

Created a formula1 :

if isnull() and {LoanOffer) = 0 Then 0 Else

Now create a group on the above formula. You will get a group with 0 where there is no LoadOffer.

Now Create another Formula to get the sum

If Formula1 <> 0

Then

Sum(LoanAmount)

Now Create another Formula to get the count

If Formula1 <> 0

count(LoanOffer)

In place of database fields place this formulas.

Thanks,

Sastry

[Former Member]

Will this approach work with the cross-tab report. I have been able to complete the cross-tab report with everything that I want except for this issue of when the loan officer doesn't have any applications during the date range chosen. If the loan officer doesn't have any apps, I need the loan officer name to print in the row and I need 0 to print for the count and 0 to print for the sum. Thank you.

[Former Member]

Yes this can be implemented for crosstab. Go into the field where you want to insert sum or count

Right click on the fieldgo in Format fieldclick on Display string formula editor and type the said formula.

It will take the formula value insted of field value in cross tba

Hope this will help you

Regards,

Sastry

[Former Member]

OK, I actually have another table involved that was already linked, so now I have three tables. I cleared the linking and now have them linked as follows:

Company File linked to Tracking File and Tracking File Linked to Extended Tracking File.

The company file contains loan officer codes (and other codes). I linked the loan officer code to the loan officer code in the tracking file. Then I linked the Loan ID in the tracking file to the Loan ID in the Extended Tracking File. The Link Options button is greyed out. What am I doing wrong?

[Former Member]

1a. There is only one table in the database expert. There is a company table that contains all the loan officer codes and names. It contains a lot more than just the Loan officer codes, but the loan officers are listed in this table as well. I will see if I can add this table and link it. Is there anything else I should do, or is linking this other table all that is requried? I really appreciate your help!! Thank you so much.

[Former Member]

Once you add the table:

1. Click on the "Links" tab in the Database Expert.

2. Right-click on the line linking the two tables and select "Link Options".

3. At the top of the window it should have something like companytable.loanofficercode->loantable.loanofficercode. This means that the company table is the main link table. If the table names are switched around pick the "Reverse Link" option first before step #2.

4. In the Link Options window, click the circle next to "Left Outer Join", the circle next to "Enforced Both" and the circle next to "=" for the Link Type.

This should return a record for each record in your company table (which should show all your loan officers) but with zeroes/nulls in the fields from the loan table for the officers who didn't have any loans. Hope this helps!

[Former Member]

1. I am the report creator, but I don't have access to see the sequal tables. I know that all the fields are in the same table. I have the cross tab set up to show the count and sum weekly. If I run the date range for a month and the loan officer has a loan (or loans) during the date range, the count and sum will show up under the appropriate week, and 0 will show for the count and sum for the week that he/she did not have a loan. This is good, but if he/she didn't have a loan application during the entire month, I want them to show up with 0 for count and 0 for sum during each weekly period.

2. What do you mean by looking at the data in a list? Is this something that requries access to the sequal tables?

[Former Member]

For #2: Don't worry about this. I believe your answer to my first question gave me the answer.

For #1: The problem is that your SQL table doesn't have a record for the loan officer who didn't have any loans for the month so he/she doesn't show up in the cross-tab.

1a. In the Crystal report if you click on "Database->Database Expert->Links tab" do you see only one table? If so, do you have a table that contains all the loan officers like a LoanOfficer table (say with their name, id, department, etc.)? If you do, maybe you can add that table to the Database Expert-Data tab and join it to your single table. Please let me know if you don't understand what I am talking about.

[Former Member]

It looks like there are two issues:

1. The data from your tables via your SQL should contain one record each for those loan officers who don't have any loans with 0/null in the id field and 0/null for the amount field. Is this happening? If you just look at the data in a list do you see those loan officers? If you do have records for those loan officers then they should show up in the cross-tab as a row with the count(id) showing as 1 and the sum(amount) showing as 0.

2. If those loan officers don't show in your data, then is it possible you are not linking your tables correctly? I would say that your main table should be the loan officer table and the sub-table should be the loan data table. So you want a left outer join for the two tables, i.e. loanofficer.id = loandata.officerid(+). This should return a record for the loan officer that doesn't have any loans also.

If your sql does return records for those loan officers, then we should be able to fix the cross-tab issue.