Crystal Reports Displaying NULL Date fields as 'incomplete'

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Displaying NULL Date fields as 'incomplete'

Author	Message
Cramp11 Newbie Joined: 09 Mar 2011 Online Status: Offline Posts: 5	Topic: Displaying NULL Date fields as 'incomplete' Posted: 09 Mar 2011 at 3:38am
	Hello, I'm new to Crystal Reports and am trying to wrap my head around a report. I have Crystal 12.2.0.290 and am connecting to a SQL Server. What I am trying to do is look at a range of assessment dates and flag incompletes. {AssessmentDate} in {?startDate} to {?endDate} (ie Mon to Sun) I have tried the following: If isNull({AssessmentDate}) Then "Incomplete" Else "Complete" Unfortunately it just returns values when there was an assessment done. Mon---Complete Tues--Complete Wed---Complete Sat---Complete Sun---Complete instead of Mon---Complete Tues--Complete Wed---Complete Thurs-Incomplete Fri---Incomplete Sat---Complete Sun---Complete I'm probably missing something very simple. Can someone lead me in the right direction? Thanks, Cramp11
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 09 Mar 2011 at 3:58am
	no you are not missing anything. Crystal does not create data rows. YOu either need a source that has all of your dates and use an outer join to your other table or you have to write a much more complex looping formula to fillin the missing items on a detail B section.
	IP Logged

Cramp11 Newbie Joined: 09 Mar 2011 Online Status: Offline Posts: 5	Posted: 09 Mar 2011 at 4:05am
	Thanks DBlank. Good to know. I was going in circles thinking I was doing something wrong. I'll be looking into a "much more complex looping formula." (yikes) Not today though. lol ;) Cramp11
	IP Logged

vivien Newbie Joined: 07 Apr 2010 Location: United States Online Status: Offline Posts: 3	Posted: 09 Mar 2011 at 5:14am
	I have similar problem and believe looping formula maybe able to solve this problem but have no luck so far. Please share your looping formula if you don't mind. I'll post my formula if I have better luck today Thanks, Edited by vivien - 09 Mar 2011 at 5:24am
	IP Logged

Cramp11 Newbie Joined: 09 Mar 2011 Online Status: Offline Posts: 5	Posted: 10 Mar 2011 at 4:27am
	From what I understand I will not be able to display all of my results in the details section as follows: Mon---Complete Tues--Complete Wed---Complete Thurs-Incomplete Fri---Incomplete Sat---Complete Sun---Complete but I should be able to make another detail section and produce Detail A Mon---Complete Tues--Complete Wed---Complete Sat---Complete Sun---Complete Detail B Thurs-Incomplete (flagged somehow with a looping formula) Fri---Incomplete (flagged somehow with a looping formula) I can't wrap my head around what needs to be done in a loop to flag the incomplete date though. Can someone give a high level pseudo code of what needs to happen? Also, if I using a formula "{AssessmentDate} in {?startDate} to {?endDate}", how do I show/check the date in the formula as it runs? I think knowing this will get me going in the right direction. I appreciate all of the help. Cramp11
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 14 Mar 2011 at 4:32am
	if distinctcount(date) = datediff('d',begin param,end_param)+1 = 0 then '' else if no records (null) then use while do to loop all days in your range else if first date> param begin loop until pram begin + 1 = first date else if previous date <> date-1 then loop until previousdate =date-1 else if last record date < param end then loop entil date +1 = param end
	IP Logged

Cramp11 Newbie Joined: 09 Mar 2011 Online Status: Offline Posts: 5	Posted: 15 Mar 2011 at 2:22am
	Thanks DBlank That will give me something to work with. I have some A work around I've been tinkering with is to bold the assessment date of a returned field if the next or prev assessement date diff is greater than one. If 1 < DateDiff ("d",{AssessmentDate},next({AssessmentDate})) then crBold Else If 1 < DateDiff ("d",previous({AssessmentDate}),{AssessmentDate}) then crBold This sort of works which is odd. For one assessment it works and then it doesn't for the next. It was just something I slapped together last minute on a Fri afternoon so I'll try to tweak it today. There is a lot more I need to add to it (ie if the assessment date equals the start or end date of the range... don't do anything)
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 15 Mar 2011 at 4:06am
	you can easily insert a missing record option if you do not have tio show all of the dates. Depends on what your customer/boss will accept. If a 'mssing' is workable create 2 detail sections ans an extra group footer a 'Missing date' text field b date field gfa 'Missing date' text conditionally suppress a as if date=?parambegin or previous(date)=date or previous(date)=date-1 display b conditionally suppress gfa if max(date,group) = ?paramend
	IP Logged

Cramp11 Newbie Joined: 09 Mar 2011 Online Status: Offline Posts: 5	Posted: 16 Mar 2011 at 8:14am
	Thanks DBlank. I'll see what I can come up with. I just found out that there are instances that multiple assessments are done on a particular day, but I only need to show the last one done. One more twist to my puzzle. Cramp11
	IP Logged

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