Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Formula? Post Reply Post New Topic
Author Message
sanchezgmc06
Senior Member
Senior Member
Avatar

Joined: 21 Jan 2011
Online Status: Offline
Posts: 107
Quote sanchezgmc06 Replybullet Topic: Formula?
     Posted: 11 Apr 2013 at 12:52pm
Hello
 
Here is what i am trying to do. I have about 1,000 clients. If a client is a dependent of the court we fill out a form with a start date and end date (table_dependency) 
 
Now the clients may have assessmets(assessment_psych) at any time since opening to our program but somewhere in between they might have been a dependent of the court.
 
I would like to only pull assessments for all clients where the assessment date is between those dependency start and end date and those clients without and end date.
 
I am not even sure how to begin my report.
 
Please Help
IP IP Logged
joeg1962
Newbie
Newbie


Joined: 01 Mar 2013
Location: United States
Online Status: Offline
Posts: 35
Quote joeg1962 Replybullet Posted: 12 Apr 2013 at 3:07am
Lost in your description. Can you provide a sample of some data and what you want to report?
IP IP Logged
sanchezgmc06
Senior Member
Senior Member
Avatar

Joined: 21 Jan 2011
Online Status: Offline
Posts: 107
Quote sanchezgmc06 Replybullet Posted: 12 Apr 2013 at 9:57am

Here is what i have so far

Fields:
dependent.patid
dependent.start_date
dependent.end_date
 
I have inserted a sub report in the details to display assessments
 
Fields:
assess.PATID
assess.type
assess.date
 
I would like to only display assessments done between the dependent.start_date and end_date with no date parameter.
 
Hope that clarifies a little more
 
I appreciate your help
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 12 Apr 2013 at 11:12am

group th emain report on patid

link the sub report on the patid and start and end dates
in the sbubreport adjust your select statement o what you want
 
?pm-patid=assess.patid and
assess.date in ?pm-startdate to ?pm-enddate
IP IP Logged
sanchezgmc06
Senior Member
Senior Member
Avatar

Joined: 21 Jan 2011
Online Status: Offline
Posts: 107
Quote sanchezgmc06 Replybullet Posted: 01 May 2013 at 6:50am
@DBlank
thank you it worked!
 
However I dont see any assessments for the patients whom do not have a depentent.end_date.
 
How can i edit my select expert to have it show assessments for those patients who do not have an end date?
 
thank you again. I appreciate it
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 02 May 2013 at 4:35am
in the main report use a formula to insert today for NULL's (I assume future dated assessments are not allowed).
If is null(depentent.end_date) then currentdate else depentent.end_date
now change your link to use this formual field instead of the depentent.end_date.
IP IP Logged
Gurbs
Senior Member
Senior Member
Avatar

Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
Quote Gurbs Replybullet Posted: 02 May 2013 at 4:36am
change your select query in your subreport to

?pm-patid=assess.patid and
(if not isnull(?pm-enddate) then
assess.date in ?pm-startdate to ?pm-enddate
else assess.date >= ?pm-startdate)
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 02 May 2013 at 4:52am
imo-i do not recommend using "if-then" in the select statement. if you wish to use Gurbs process, I would recommend using an or statement instead
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.