Crystal Reports Left Outer Join

Technical Questions

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

Topic: Left Outer Join

<< Prev Page of 2

Author	Message
yerram Newbie Joined: 07 Sep 2010 Location: United States Online Status: Offline Posts: 19	Posted: 16 Sep 2010 at 4:18am
	fyi..there are numerous (amy a hundred or more) flod-Ids for every inp_id. so, getting all flo-ids onto the report is not an option.
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 16 Sep 2010 at 4:25am
	Why is that not an option? You can conditionally suppress which would not hurt your counts... You said you wanted to count all flo ids and then another count of all flo ids with 11/14. Rather than sticking with just joins as the issue can you explain the full scope of what is expected in the report?
	IP Logged

yerram Newbie Joined: 07 Sep 2010 Location: United States Online Status: Offline Posts: 19	Posted: 16 Sep 2010 at 4:31am
	On the report, I want the list of all Inp_ids (stands for total number of patients) and the list of 11 and 14 flod_ids (stands for documentation of two different items). Then I need a count of total patients (total inp_ids) and count of documentstion done for two different items (total of flo_ids 11 and 14 individually)
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 16 Sep 2010 at 4:39am
	so why not do your left joins with no select criteria conditionally suppress the detail rows with NOT (fli_id in [11,14]) this should get to showing the data for counting I am still not sure if you want a count or a distinctcount but assuming you want a distinct count use DISTINCTCOUNT(inp_id) then for a distincy count of patients that had 11 running total as name=11 field to summarize=inp_id type=distinctcount evalaute =use a formula flo_id=11 reset=never place in report footer (RTs won't work in headers) for 14 repeat but chaneg the evaluate formula to use that Does that do what you need? Edited by DBlank - 16 Sep 2010 at 4:39am
	IP Logged

yerram Newbie Joined: 07 Sep 2010 Location: United States Online Status: Offline Posts: 19	Posted: 16 Sep 2010 at 6:27am
	Here is where I am having a problem with suppressing the recods in Detial view. If inp_id do not have a corresponding record for flo_id 11 or 14 the whole record gets suppressed meaning the report will lose all the inp_ids with no flo_ids of 11 or 14. But I also want the inp_ids with no data for flo_ids 11 orr 14 also to be showing up on the report.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 16 Sep 2010 at 6:32am
	if I understand you correctly you want to show only empIDs with a corresponding flo id of 11, 14 or NULL. just chaneg the suppress criteria to include the NULL condition NOT (isnull(flo_id) or flo_id in [11,14])
	IP Logged

<< Prev Page of 2

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