Solved

Display the prior date  when grouping in Crystal Reports

Posted on 2014-04-03
13
212 Views
Last Modified: 2014-10-20
Hi,  I have a report which reports on all the date's that a patient has had office visit.  The data is limited using a parameter for a start and end date for the contact date. I need to display the most recent contact date (as defined in the parameter) and the previous date.  

I am using the MAX function for the Recent Contact Date and that is working,

I really need help displaying the Prior Contact Date field.

Below is an example of the data.   Parameter contact date from 3/1/14 to 4/1/2014

PATIENT_ID     CONTACT_DATE
1234                 4/01/2014
1234                 3/15/2014
1234                 3/04/2014
4567                 4/01/2014
4567                 3/12/2014
8910                 3/05/2014
1247                 3/18/2014

I would like to display a single line for each id:

PATIENT_ID            Recent Contact Date                       Prior Contact Date
1234                        4/01/2014                                         3/15/2014
4567                        4/01/2014                                         3/12/2014
8910                        3/05/2014
1247                   3/18/2014                        

Any ideas how I can accomplish this?
0
Comment
Question by:jul_to_cool
  • 6
  • 4
  • 2
13 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 39976629
Are you sorting the records in descending date order?

If so you can use NEXT({DateFIeld}) get the next date

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39976631
Where are you displaying the single line?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 39977714
FWIW, the Next idea may not work if there could be more than one record for the same date.  Next just gives you the value from the next record, so if the max date is first and there is more than one record with that date, Next may just give you the same date (from the next record).  It depends on exactly where you use it.

 James
0
 

Author Comment

by:jul_to_cool
ID: 39978050
I am grouping by the patient id and sorting the records in descending order in the details. The single line can be displayed in group 1.

I tried using NEXT({DateFIeld}) however I am experiencing the issue that James describes in his post.  When there is only 1 date for a record then the next formula displays the next  date for the next record rather than a null value.

For example id 8910 has only 1 contact date, the next contact date should be null.  It shows as the contact date for the next record, 1247, as 3/18/2014.

PATIENT_ID     CONTACT_DATE            Next Contact Date
8910                 3/05/2014                     3/18/2014  
1247                 3/18/2014                     last record on the report displays a blank
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 39978093
Technically, that's a different issue, but that one is fairly easy to handle.

 You can use Next ({Patient_ID}) to make sure that the next record is for the same patient.  You just have to decide what you want to do when the next record is for a different patient.

 If you want the field to be blank, you could use something like:

if Next ({Patient_ID}) = {Patient_ID} then
  CStr (Next ({Contact_date}), "M/dd/yyyy")
else
  ""


 James
0
 

Author Comment

by:jul_to_cool
ID: 39978803
Thanks!   It works beautifully!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 34

Expert Comment

by:James0628
ID: 39979284
mlmcc should probably get some, if not most, of the points.  He suggested using Next.  I just added to that.  You can use the "Request Attention" link to ask to have the question re-opened, and then split the points between his first post, and my post.

 James
0
 

Author Comment

by:jul_to_cool
ID: 39986572
I have been asked to determine the days between the recent contact date and prior contact date and then give a summary for group 1 regarding the # of patients with a contact greater than 1, 6 and 12 months.  I was able to use the DateDiff function to get the number of months between the two dates (recent date uses a Maximum function and prior using Next function), I also changed the formula to identify as a date instead of a string.  
However but I am not able to use the Datediff formula for months within any of the formulas, running totals, ect.  I am getting a error message..."This formula cannot be used because it must be evaluated later".  

I do not know of another way to get this info, any help is appreciated!

Below is the original next formula by James0628...

if Next ({Patient_ID}) = {Patient_ID} then
  CStr (Next ({Contact_date}), "M/dd/yyyy")
else
  ""
0
 
LVL 34

Expert Comment

by:James0628
ID: 39986788
You should maybe start a new question for that, since it seems like a pretty significant departure from the original question.

 And while I'm here, I'll repeat what I said before, that mlmcc should probably have gotten at least some of the points for this question, since he suggested using Next.

 Getting back to the new question, FWIW, I don't think DateDiff is the problem.  I'm guessing that you're trying to do a summary on a formula that uses Next or Maximum, and CR won't do that.  I'm not sure exactly what you're trying to do, but you may have to use formulas and variables to create your own totals.

 James
0
 

Author Comment

by:jul_to_cool
ID: 39987107
Thank you.  Please re-open the question so I may award some points to mlmcc.
0
 
LVL 34

Expert Comment

by:James0628
ID: 39987242
There's no guarantee that a moderator will see your post.  You can use the Request Attention link to formally ask to have the question re-opened.

 James
0
 
LVL 34

Expert Comment

by:James0628
ID: 40367151
My post # 39979284 was certainly not the solution to the question.  I'm going to ask that the question be re-opened, and hopefully you'll come back and split the points between mlmcc's first post (where he suggested using Next) and my post # 39978093 (where I suggested a possible solution to a problem that you had when you used Next).

 James
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now