| Author |
Message |
Curbish
Newbie
Joined: 04 Nov 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
|
  
Topic: Selecting the 2nd most recent row of data Solved
Posted: 04 Nov 2011 at 6:21am |
|
Hi All,
I am new to the forum and reasonably new to Crystal Reports XI. What I am trying to achieve is we have time stamped records within a database e.g
User : Joe blogs created: A car On: 20/1/2011
I have worked out that using the Maximum(Creations.On) will give me the most recent action relating to that user, However what I cannot get working is return the result previous to the most recent.
At the moment I have used the code:
Maximum({Creations.On}) as a formula called MaxDate
then created a new formula called PrevDate with the code
if ({Creations.On} < {@MaxDate}) then {Creations.Created}
The above will return all previous results, where as I only need the 1 before latest.
Any help or advise will be greatly appreciated.
Kind Regards
Curbish
Edited by Curbish - 09 Nov 2011 at 4:11am
|
 IP Logged |
|
|
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 04 Nov 2011 at 9:48am |
|
this might work
formula {@DDate}
{Creations.On} <> {@MaxDate}
formula {@MaxDDate}
maximum({@DDate})
|
| IP Logged |
|
Curbish
Newbie
Joined: 04 Nov 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
|
Posted: 06 Nov 2011 at 11:16pm |
|
Hi Kostaya,
Thanks for the speedy response I tried what you suggested which helped for the first part, However when using Maximum({@DDate}) I get the error "cannot summarise field".
What I am trying to fix now is narrowing down that last bit, I tried using Recordnumber>1 to suppress the extra result however due to the first result being suppressed (@{MaxDDate}) it returns a blank result.
is there a formula to return a particular record out of a range?
Curbish
|
| IP Logged |
|
Curbish
Newbie
Joined: 04 Nov 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
|
Posted: 06 Nov 2011 at 11:34pm |
|
Hi Kostaya, after some playing I have got it working based on your initial advise.
I used Section expert to suppress the first record with recordnumber=1 I also adding to each formula field suppress recordnumber>2.
And this worked!
Kind Regards
Curbish
|
| IP Logged |
|
Curbish
Newbie
Joined: 04 Nov 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
|
Posted: 07 Nov 2011 at 2:35am |
|
Need more help on this report, 2 issues are now in the picture.
1st: my date order has suddenly reversed which means the suppression no longer works correctly.
2nd: Also I need to calculate the difference between 2 numbers in 2 separate sub reports, I tried using shared variables but it was returning a null value due to the suppression.
I believe the fix to the 1st issue will fix the 2nd, So its back to the original question how can I select the 2nd most recent data? regardless of the order in the database.
Kind Regards
Curbish
|
| IP Logged |
|
Curbish
Newbie
Joined: 04 Nov 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
|
Posted: 07 Nov 2011 at 6:04am |
|
I have made slight progress my data field is no longer reversed used report - record sort expert and sorted the dates by descending.
still require help with the other bits though.
|
| IP Logged |
|
Curbish
Newbie
Joined: 04 Nov 2011
Location: United Kingdom
Online Status: Offline
Posts: 12
|
Posted: 09 Nov 2011 at 2:21am |
|
Hi All,
SOLVED IT!!!!
I created the main report with the max date, this allowed to filter by the most recent date, I then created a sub report linking to main report the max date.
Using Formulas I filted the record selection to be < Maxdate.
This gave the correct break down, I then suppressed using recordnumber>1 to return just 1 result.
Thanks to the help you guys gave me it allowed me to figure out how to do it
Thanks again.
Kind Regards
Curbish
Edited by Curbish - 09 Nov 2011 at 2:22am
|
| IP Logged |
|
|
|
