Crystal Reports Distinct Count

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

Topic: Distinct Count

Author	Message
sanchezgmc06 Senior Member Joined: 21 Jan 2011 Online Status: Offline Posts: 107	Topic: Distinct Count Posted: 17 Oct 2014 at 12:27pm
	Hello I have created a report where I group on USERID my second group is on DATE and did a distinct count on patientID and now I need to display only records for those where the distinct count is grater than 5. I have tried a running total however I cant select the running total in the select expert. Help!
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 20 Oct 2014 at 3:46am
	group select statement on a summarized field at a group. I do not understand where patientID count is coming in at.
	IP Logged

sanchezgmc06 Senior Member Joined: 21 Jan 2011 Online Status: Offline Posts: 107	Posted: 22 Oct 2014 at 5:11am
	I am trying to view users who accessed over 15 clients that are not within their caseload in 1 day. I am selecting the access.USERID, access.date, access.patid, access.reason. I am grouping on access.USERID, my second group is on access.date. In the details i am displaying the PATID and the access.reason I am also doing a distinct count on PATID for each date. However i need to only see the records when a USERID has accessed over 15 distinct PATID'S in a day. Hope that clarifies Thanks
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 22 Oct 2014 at 5:19am
	So you do not care which date it happened just that it occurred on any date (in the range of the report)? How are you determining 'not on caseload'? Is this being handled by a filter on the 'access.reason' field? Or does it need to be addressed in the distinct count somehow? Edited by DBlank - 22 Oct 2014 at 5:20am
	IP Logged

sanchezgmc06 Senior Member Joined: 21 Jan 2011 Online Status: Offline Posts: 107	Posted: 22 Oct 2014 at 5:36am
	Yes, the report has a Start Date and End Date parameter I am determining they are 'not on caseload' by entering the following in my select expert. NOT ISNULL ({access.reason})
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 22 Oct 2014 at 6:28am
	easiest way would be through a stored procedure or a command object. You can try this. insert a distinctcount of patid at the date group.Make sure the date group is set to 'each day' insert a group select of DistinctCount ({PATID}, {DATE}, "daily") > 15 suppress GH2 details GF2 Edited by DBlank - 22 Oct 2014 at 6:29am
	IP Logged

sanchezgmc06 Senior Member Joined: 21 Jan 2011 Online Status: Offline Posts: 107	Posted: 22 Oct 2014 at 8:44am
	Yes!!! that worked Thank you so much DBlank
	IP Logged

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.016 seconds.