Solved

Display the prior date  when grouping in Crystal Reports

Posted on 2014-04-03
13
210 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

13 Experts available now in Live!

Get 1:1 Help Now