Left Outer Join
Printed From: Crystal Reports Book — Forum Name: Technical Questions
Posted By: LShadrin — 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?
Posted By: DBlank — 20 Jul 2010 at 2:27pm
what are you joining on?
what is in each table?
Posted By: LShadrin — 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?
Posted By: DBlank — 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 — 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]
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
Not Interested 0
Not Interested/ Recontact 0
Not Qualified 0
Posted By: DBlank — 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 — 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
Posted By: DBlank — 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 — 21 Jul 2010 at 5:40am
The Client_ID and the Client_Number are the ones that are the same in both tables.
Posted By: DBlank — 21 Jul 2010 at 6:19am
Sorry for all the questions but I just can't seem to get a handle on your structure.
Can you post sample rows from AMGR Client?
Posted By: LShadrin — 21 Jul 2010 at 6:28am
No don't apologize! I am new at this so I am probably not explaining it right.
I don't have the capabilities to copy a screen shot so I posted them here
http://connectpro16283582.acrobat.com/amgr_client/
Posted By: DBlank — 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 — 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.
Posted By: DBlank — 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 — 21 Jul 2010 at 7:37am
OK, that works but it is not showing the Lead_Status of the ones that are not selected.
Posted By: DBlank — 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
Not Interested 0
Not Interested/ Recontact 0
Not Qualified 0 Edited by DBlank - 21 Jul 2010 at 8:00am
Posted By: LShadrin — 21 Jul 2010 at 8:08am
You got it.
Posted By: DBlank — 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 — 21 Jul 2010 at 8:25am
suppress detail section as well
Posted By: LShadrin — 21 Jul 2010 at 9:09am
 ok. having issues with the formula:
DistinctCount ({A_Lead_status.A_Lead_status} = "Contacts Attempted") is how far I got and I get an error message asking for a number.
Posted By: DBlank — 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 — 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
Posted By: DBlank — 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 — 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.
Posted By: LShadrin — 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!!! 
Posted By: DBlank — 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 — 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.
Posted By: DBlank — 21 Jul 2010 at 10:15am
use the field you grouped on to create group level 1
Posted By: LShadrin — 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
Posted By: DBlank — 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 — 21 Jul 2010 at 10:41am
I tried that before and it didn't work. I tried it now and it works.
Thanks again!!!!!
Posted By: LShadrin — 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?
Posted By: DBlank — 23 Jul 2010 at 12:45pm
what is it doing?
Posted By: LShadrin — 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.
Posted By: DBlank — 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 — 23 Jul 2010 at 1:38pm
Yes. I just doubled checked and default is set for each Count that was created.
Posted By: DBlank — 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 — 23 Jul 2010 at 2:25pm
In the Action Taken field.
Posted By: DBlank — 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 — 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.
Posted By: DBlank — 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... Edited by DBlank - 23 Jul 2010 at 3:14pm
Posted By: DBlank — 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 — 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.
|
