Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Display the prior date  when grouping in Crystal Reports

Posted on 2014-04-03
13
Medium Priority
?
230 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
13 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 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 101

Expert Comment

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

mlmcc
0
 
LVL 35

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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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 35

Assisted Solution

by:James0628
James0628 earned 1000 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
 
LVL 35

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 35

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 35

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 35

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

604 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