Left Outer Join
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10590
Printed Date: 18 May 2024 at 1:35am
Topic: Left Outer Join
Posted By: LShadrin
Subject: Left Outer Join
Date Posted: 20 Jul 2010 at 1:41pm
|
I am new to Crystal Reports and I am building a report where I want to display all the values that are assigned to a person. I have Table 1 = Lead Status and Table 2 = Assigned to. I have a left outer join going from table 1 to table 2. What am I doing wrong?
------------- Luka |
Replies:
Posted By: DBlank
Date Posted: 20 Jul 2010 at 2:27pm
|
what are you joining on?
what is in each table?
|
Posted By: LShadrin
Date Posted: 20 Jul 2010 at 3:31pm
|
There are 11 fields in Lead Status for each Assigned To person and if they did not select the other 10 fields, I want to be able to see them.
The reason I have AMGR Client in there is because I want to be able to pull data only for a certain day and I was going to do a selection expert to retreive that data.
Here is my SQL code:
SELECT "AMGR_Client"."Last_Modify_Date", "A_Assigned_To"."A_Assigned_To", "A_Lead_status"."A_Lead_status"
FROM ("CandidateLeads"."dbo"."A_Assigned_To" "A_Assigned_To" LEFT OUTER JOIN "CandidateLeads"."dbo"."A_Lead_status" "A_Lead_status" ON ("A_Assigned_To"."Client_Id"="A_Lead_status"."Client_Id") AND ("A_Assigned_To"."Contact_Number"="A_Lead_status"."Contact_Number")) LEFT OUTER JOIN "CandidateLeads"."dbo"."AMGR_Client" "AMGR_Client" ON ("A_Assigned_To"."Client_Id"="AMGR_Client"."Client_Id") AND ("A_Assigned_To"."Contact_Number"="AMGR_Client"."Contact_Number") ORDER BY "A_Assigned_To"."A_Assigned_To", "A_Lead_status"."A_Lead_status" Am I way off from what I am trying to accomplish?
------------- Luka |
Posted By: DBlank
Date Posted: 20 Jul 2010 at 3:47pm
|
sorry not sure if you are on the correct track or not.
I am not familiar with the tables and am not quite 'seeing' your data here.
I need to know what is stored in each table independently and then what you want to see at in the report to see if a join makes sense.
Can you post a few sample rows per table and then what you expect to see in your report from those tables.
Also be careful of outer joins and then using select statements in the report. Often a select statement will turn an outer join back into an inner join.
|
Posted By: LShadrin
Date Posted: 21 Jul 2010 at 4:15am
|
In the Assigned_To table, I have names stored there. There are 20 names in there.
In the A_Lead_Stuats, I have the status listed in there. There is:
Contact Attempted (Left Message or Email)
Contact Made (Phone or Face-to-Face)
Contract Pending
Contracted
Already Contracted
Initial (New/Unworked Lead)
Interview Scheduled (Day/Time)
Materials Provided [Email/Interview Packet]
No Show For Interview Not Interested
Not Interested/ Recontact
Not Qualified
In each AMRGR_Client record the "Assigned_To" person goes into and if they select "Contract Pending" I want Contract Pending to show up as 1, then in the next client record, if they select "Contracted", a 1 to apear aftter that one, etc.
John Doe
Contact Attempted (Left Message or Email) 0
Contact Made (Phone or Face-to-Face) 0
Contract Pending 1
Contracted 1
Already Contracted 0
Initial (New/Unworked Lead) 0
Interview Scheduled (Day/Time) 0
Materials Provided [Email/Interview Packet] 0
No Show For Interview 0 Not Interested 0
Not Interested/ Recontact 0
Not Qualified 0 ------------- Luka |
Posted By: DBlank
Date Posted: 21 Jul 2010 at 5:28am
|
So all your incidents of contact for a client (from an 'employee') is in the AMGR_Client table.
All of your types of contact are in the a lead status
all of your 'employees' that make contact are in the assigned to table.
Is that all correct?
1. Is the AMGR_client table one row per contact with a code that matchs to the lead status table or can you have more than one contact type in one row of this data?
2. In your sample is 'John Doe' an 'emplyee' from 'assigned to' or a 'customer' from AMGR?
|
Posted By: LShadrin
Date Posted: 21 Jul 2010 at 5:32am
|
1. I am not really understanding your question but the AMGR_Client has all the contact information for the customer and only one employee (Assigned_To) is assigned to that customer.
2. John Doe is the employee and is the person in the Assigned_To field ------------- Luka |
Posted By: DBlank
Date Posted: 21 Jul 2010 at 5:36am
| Does AMGR_Client have somehting like one column called 'COntact_type" with a code in it that matches the codes from A lead status? |
Posted By: LShadrin
Date Posted: 21 Jul 2010 at 5:40am
|
The Client_ID and the Client_Number are the ones that are the same in both tables. ------------- Luka |
Posted By: DBlank
Date Posted: 21 Jul 2010 at 6:19am
|
Can you post sample rows from AMGR Client?
|
Posted By: LShadrin
Date Posted: 21 Jul 2010 at 6:28am
|
No don't apologize! I am new at this so I am probably not explaining it right. ------------- Luka |
Posted By: DBlank
Date Posted: 21 Jul 2010 at 6:57am
|
So you join AMGR Client.lead_status to A_Lead_Statuts.code? to get the verbage for the current status and that is what you want counted for all the records in the AMGR table grouped on worker?
But you want to make sure to show all Statuses even if a worker has none of that particular type?
|
Posted By: LShadrin
Date Posted: 21 Jul 2010 at 7:05am
|
I am joining Assigned_To to A_Lead_Status and to AMGR_Client and I want to show all the people who is listed in the assigned to field to display the lead status for all the records they are assigned to. So for instance, if John Doe (Assigned_To) person selected "Contracted" as the A_Lead_Status, I want it to show John Doe has 1 in Contracted and 0 for the rest of the Lead_Status fields. ------------- Luka |
Posted By: DBlank
Date Posted: 21 Jul 2010 at 7:14am
|
My guess is inner join AMGR_client to A_Lead_Status on Lead Status and
inner join A_Lead_Status to Assigned_To on Client_ID and the Client_Number.
Group on John Doe name field from A_Lead_Status (group level 1)
Then group on status description field from A_Lead_Status (group level 2)
do a insert summary using probably sequence number as a distinctcount at group level 2.
Move to group header 2 next to the status description.
|
Posted By: LShadrin
Date Posted: 21 Jul 2010 at 7:37am
|
OK, that works but it is not showing the Lead_Status of the ones that are not selected. ------------- Luka |
Posted By: DBlank
Date Posted: 21 Jul 2010 at 7:59am
|
meaning you want to show zero if they did not have any for that 'type' as in your earlier sample?...
Contact Attempted (Left Message or Email) 0
Contact Made (Phone or Face-to-Face) 0
Contract Pending 1
Contracted 1
Already Contracted 0
Initial (New/Unworked Lead) 0
Interview Scheduled (Day/Time) 0
Materials Provided [Email/Interview Packet] 0
No Show For Interview 0 Not Interested 0
Not Interested/ Recontact 0
Not Qualified 0 |
Posted By: LShadrin
Date Posted: 21 Jul 2010 at 8:08am
|
You got it. ------------- Luka |
Posted By: DBlank
Date Posted: 21 Jul 2010 at 8:24am
|
OK
Maybe a left join would work in here somwhere but a simpler approach IMO is
Get rid of Group level 2
expand your group footer and add text fields for each of your types (12 total).
Make 1 Running Total
name=LeftMessageCount
Field to summarize=probably sequence number
Type of summary= DistinctCount
Evaluate=Use a formula
A_Lead_Status.Lead Status="Contact Attempted (Left Message or Email)"
Chnage the option in the formual editor to 'Use Default values for Nulls"
Reset = Group Level 1
Place in Group footer 1 next to the "Contact Attempted (Left Message or Email)" text field.
Reapeat for RT as ContactMadeCount but change the formula to use the correct text for that type.
Repeat for all 12
|
Posted By: DBlank
Date Posted: 21 Jul 2010 at 8:25am
| suppress detail section as well |
Posted By: LShadrin
Date Posted: 21 Jul 2010 at 9:09am
|
DistinctCount ({A_Lead_status.A_Lead_status} = "Contacts Attempted") is how far I got and I get an error message asking for a number. ------------- Luka |
ok. having issues with the formula:Posted By: DBlank
Date Posted: 21 Jul 2010 at 9:15am
|
Not a formula field but a Running Total
Toward the bottom of the Field Explorere you will see 'Running Total Fields'
Right click on it and select new
then...
name=LeftMessageCount
Field to summarize=probably sequence number
Type of summary= DistinctCount
Evaluate=Use a formula
A_Lead_Status.Lead Status="Contact Attempted (Left Message or Email)"
Chnage the option in the formual editor to 'Use Default values for Nulls"
Reset = Group Level 1
Place in Group footer 1 next to the "Contact Attempted (Left Message or Email)" text field. |
Posted By: LShadrin
Date Posted: 21 Jul 2010 at 9:28am
|
OMG it worked!
THANK YOU SO MUCH!!!
I have another question, why did we summarize this field?
Field to summarize=probably sequence number ------------- Luka |
Posted By: DBlank
Date Posted: 21 Jul 2010 at 9:37am
|
You could have done it other ways (maybe a count of the lead status field?) but you wanted a to count each 'contact' only one time. To make sure, I was trying to use the primary key (unique identifier) for that table which i was guessing was the sequence number and use a distinctcount for it. This way if any of our joins made duplicate rows (happens a lot) we would not count each row more than a single time. make sense?
|
Posted By: LShadrin
Date Posted: 21 Jul 2010 at 9:50am
|
Yes, that makes sense!
Thank you again.
Also, is there a way to get a percentage of those counts?
For example, I want to know out of the "Contacts Made" count, how many resluted in "Interview Scheduled" count. ------------- Luka |
Posted By: LShadrin
Date Posted: 21 Jul 2010 at 9:52am
|
Never mind. I think I am going to have to split those out of the table and then create a group.
Thanks again for your help!!!  ------------- Luka |
Posted By: DBlank
Date Posted: 21 Jul 2010 at 9:57am
|
No you can get it for each one
you will create a formula fo reach one as:
#RunningTotal % DistinctCount(sequencenumber,group1field)
something like
{#InterviewScheduleCount}%DistinctCount({AMGR_client.sequencenumber},{A_Lead_Status .name})
|
Posted By: LShadrin
Date Posted: 21 Jul 2010 at 10:13am
|
It's not likeing the
A_Lead_Status .name
{#InterviewScheduleCount}%DistinctCount({AMGR_client.sequencenumber},{A_Lead_Status.name})
I tried changing it to {A_Lead_Status.A_Lead_Status} but that was not working. ------------- Luka |
Posted By: DBlank
Date Posted: 21 Jul 2010 at 10:15am
| use the field you grouped on to create group level 1 |
Posted By: LShadrin
Date Posted: 21 Jul 2010 at 10:32am
|
Ok. I changed it to my Group Level 1 and it's not pulling correctly
IE:
John Doe
Contacts Attempted 117 //This is my ContactsAttemptedCount
Contacts Made 11 //This is my ContactsMadeCount
Interviews Scheduled 3 //This is my InterviewsScheduledCount
Close Ratio 0.63 // This should be 0.27 because we should be dividing interviews scheduled by contacts made ------------- Luka |
Posted By: DBlank
Date Posted: 21 Jul 2010 at 10:38am
|
sorry,
i though you wanted a percentage of each type per total contacts...
change it to use those two RTs, interviews and contacts made)...
{#InterviewsMadeCount}%{#ContactsMadeCount}
|
Posted By: LShadrin
Date Posted: 21 Jul 2010 at 10:41am
|
I tried that before and it didn't work. I tried it now and it works.
Thanks again!!!!! ------------- Luka |
Posted By: LShadrin
Date Posted: 23 Jul 2010 at 12:32pm
|
I just tested the count fields and noticed they are not calculating again. Do you have time to help me out? ------------- Luka |
Posted By: DBlank
Date Posted: 23 Jul 2010 at 12:45pm
| what is it doing? |
Posted By: LShadrin
Date Posted: 23 Jul 2010 at 12:49pm
|
I figured out that if there is nothing in the field "Lead Status" it does not calculate the "Contact Made" or "Contact Attempted" part. ------------- Luka |
Posted By: DBlank
Date Posted: 23 Jul 2010 at 1:35pm
|
Did you do the step in red?
name=LeftMessageCount
Field to summarize=probably sequence number
Type of summary= DistinctCount
Evaluate=Use a formula
A_Lead_Status.Lead Status="Contact Attempted (Left Message or Email)"
Change the option in the formula editor to 'Use Default values for Nulls"
Reset = Group Level 1
Place in Group footer 1 next to the "Contact Attempted (Left Message or Email)" text field. |
Posted By: LShadrin
Date Posted: 23 Jul 2010 at 1:38pm
|
Yes. I just doubled checked and default is set for each Count that was created. ------------- Luka |
Posted By: DBlank
Date Posted: 23 Jul 2010 at 2:23pm
|
rereading the posts I thought all the status types were held in the Lead_status field...
Where (what field in the AMGR table?) are the contact made or contact attempted stored?
|
Posted By: LShadrin
Date Posted: 23 Jul 2010 at 2:25pm
|
In the Action Taken field. ------------- Luka |
Posted By: DBlank
Date Posted: 23 Jul 2010 at 2:49pm
|
Change your RT formulas to use that condition...
table.ActionTakenField=???
add what constitutes contact made for one and contact attmepted for the other
|
Posted By: LShadrin
Date Posted: 23 Jul 2010 at 2:53pm
|
It currently does.
{A_Action_Taken.A_Action_Taken} = "Contact Made (Phone or Face-to-Face)"
I think to make sure this works, I was going to make this field a Mandatory field that way it won't be skipped by the users and it will work that way. ------------- Luka |
Posted By: DBlank
Date Posted: 23 Jul 2010 at 3:14pm
|
I agree if you want it to be counted some value has to be there. You could do an unknown here it is is NULL or ="" but i think you want every record to fall into one or the other...
|
Posted By: DBlank
Date Posted: 24 Jul 2010 at 3:58am
| unless you can infer the answer from another piece of data. Then you can include that as an OR statement in the evaluation formula |
Posted By: rameshcrystal
Date Posted: 28 Jul 2010 at 11:02pm
|
Generally Outer join has two types,
1.Left outer join
2.Right outer join
if you want show all the details from left table 1, you should select
Left outer join, other wise select Right outer join.
 |