Crystal Reports Left Outer Join

Technical Questions
Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions

Topic: Left Outer Join
	<< Prev Page of 5 Next >>

Author	Message
LShadrin Newbie Joined: 20 Jul 2010 Location: United States Online Status: Offline Posts: 24	Posted: 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/
	Luka
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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?
	IP Logged

LShadrin Newbie Joined: 20 Jul 2010 Location: United States Online Status: Offline Posts: 24	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
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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.
	IP Logged

LShadrin Newbie Joined: 20 Jul 2010 Location: United States Online Status: Offline Posts: 24	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
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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 Edited by DBlank - 21 Jul 2010 at 8:00am
	IP Logged

LShadrin Newbie Joined: 20 Jul 2010 Location: United States Online Status: Offline Posts: 24	Posted: 21 Jul 2010 at 8:08am
	You got it.
	Luka
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 21 Jul 2010 at 8:25am
	suppress detail section as well
	IP Logged

LShadrin Newbie Joined: 20 Jul 2010 Location: United States Online Status: Offline Posts: 24	Posted: 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.
	Luka
	IP Logged

<< Prev Page of 5 Next >>

Post Reply

Post New Topic

Printable version

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

This page was generated in 0.031 seconds.