2nd last commend
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=16640
Printed Date: 18 May 2024 at 4:13pm
Topic: 2nd last commend
Posted By: Gurbs
Subject: 2nd last commend
Date Posted: 30 May 2012 at 5:57am
Hi all,
I am currently working on a report, given the last comment made on each case. However, every once in a while, we check if these cases are still correct. We place a comment in the 'Comment' table, saying 'Folder examined'.
However, if this is the case, I don't want to show the last comment, but the second last comment. I've tried nthlargest(2, {comment.comment}). But then he is just picking up the comment that starts with a Z or something. Is there a way to look at the comment date, determine if the comment is only 'Folder examined', and based on that show either the last comment or the second last comment?
Thanks in advance
Kind regards,
Gerben
|
Replies:
Posted By: lockwelle
Date Posted: 30 May 2012 at 6:30am
|
if the records are consecutive, you might try Prior or is Previous...but I don't know how nthlargest works. my personal favorite answer is to write a stored proc (which I always do), then you are in complete control of what value is written.
|
Posted By: Gurbs
Date Posted: 30 May 2012 at 10:16pm
I've never worked with stored proc before... How would I be creating this? Could you help me with that? Everything I know about Crystal is what I found out myself and what I've been told here
Thanks in advance
|
Posted By: rkrowland
Date Posted: 31 May 2012 at 12:59am
|
A way to do it without prior/next and without a stored proc would be to merge your comment date with the comment so the nthlargest can work correctly;
Merge
totext(comment.date,"yyyyMMdd")
&
Comment
if "Folder Examined" in (nthlargest(1,merge,table.casegroup))
then mid(nthlargest(2,merge,table.casegroup),9)
else mid(nthlargest(1,merge,table.casegroup),9)
It might take abit of tinkering as I haven't tested it but that should get you what you need.
I too would create a stored proc for something like this though.
Regards,
Ryan.
|
Posted By: lockwelle
Date Posted: 31 May 2012 at 3:33am
|
Never used nthLargest, but seems like a nifty solution Ryan.
|
Posted By: Gurbs
Date Posted: 31 May 2012 at 4:44am
Thanks for your help Ryan. I do have 1 question though
if "Folder Examined" in (nthlargest(1,merge,table.casegroup))
What do you mean with table.casegroup? the field I am grouping on?
|
Posted By: rkrowland
Date Posted: 31 May 2012 at 4:56am
|
That's the one, if you're not already grouped by "case" you'll need to for this to work.
Regards,
Ryan.
|
Posted By: Gurbs
Date Posted: 31 May 2012 at 5:09am
I'm getting the message "There must be a group matching this field", highlighting
(nthlargest(1,{@merge},{@03. C.D.#})
|
|