| Author |
Message |
elnino26
Newbie
Joined: 17 Feb 2009
Location: United States
Online Status: Offline
Posts: 8
|
   Topic: Select the last record and show it in my report
Posted: 17 Feb 2009 at 10:19am |
Hello Guys,
I hope every body is doing ok. I have an issue with on of my reports. The issue is that I don't know what is the best way to show in my report the last transaction of each user? The transaction table has saved all users transactions. The only thing that I want is go to the table, select the last transaction table, and show the last transaction in my report for each user. I was trying to use different ways to do it, but neither of those worked. I was using an SQL statement which works just fine from the DB, but when I set up it on Crystal Repot XI it doesn't. So, I don't know if somebody can help to find the right way to do it.
The SQL is: this statement is working as I want in Oracle DB, but it doesn't work in Crystal Report. I also try to use OnLaststRecord, but it didn't work either. When I used those options, I always get many records with the same info. I just want to get the last transaction date field in my report
select TRANDATE
from TRANS_TABLE
where ROWID = (select MAX(ROWID) from TRANS_TABLE where PATRONID = PATRONID);
I hope that someone can provide some help.
Thanks,
Edited by elnino26 - 17 Feb 2009 at 12:21pm
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 17 Feb 2009 at 1:49pm |
You could change your sql to something like this:
Select PATRONID, max(TRANDATE)
from TRANS_TABLE
group by PATRONID
Link this to your other data on Patron ID.
You could also do this in the report (this solution pulls all of the data...):
1. Group by User.
2. Sort by TranDate descending.
3. Put the data in the User Group Header section. Because the data is sorted with the most recent date first, that's the date that will appear on the report.
-Dell
Edited by hilfy - 17 Feb 2009 at 1:55pm
|
|
|
|
IP Logged |
|
JohnT
Groupie
Joined: 20 Jan 2008
Online Status: Offline
Posts: 92
|
Posted: 17 Feb 2009 at 1:49pm |
Did you enter your SQL as a command or did you try to build it in the selection expert ?
To learn how to enter your SQL into Crystal, go to help and look at "Define a SQL command". You might have to use something other than ROWID but it should work. IMaybe something like this:
Select TRANDATE,
PATRONID
from TRANS_TABLE A
where TRANDATE = (Select max(TRANDATE) from TRANS_TABLE where A.PATRONID = PATRONID)
Good luck !
|
|
IP Logged |
|
elnino26
Newbie
Joined: 17 Feb 2009
Location: United States
Online Status: Offline
Posts: 8
|
Posted: 18 Feb 2009 at 7:21am |
Dell,
Thank you so much for your help.
I did what you told me to do, but it's still not working. What I'm trying to do is to get it just the last transaction day for each user in my report. The report is doing different data source and grouping using different info, but the only thing is missing is the very last transaction day. I tried to do with SQL statement and formula but it doesn't work. Every time that I set up something like the SQL statement, I get an error messages. I don't know what is the best way to do it? but I'm kind of frustrated. This is the only part in my report that is not working.
Let me know.
Thanks,
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 18 Feb 2009 at 7:29am |
I have a couple of thoughts, but I need to know a little more about your report.
What are the error messages that you're getting with the SQL? What is the exact SQL that you're using? How are your groups set up?
-Dell
|
|
|
|
IP Logged |
|
elnino26
Newbie
Joined: 17 Feb 2009
Location: United States
Online Status: Offline
Posts: 8
|
Posted: 18 Feb 2009 at 7:35am |
Thank you for your help.
Well, I tried different way to do it like SQL and formula. But neither one works. This is the only thing that is not working in my report. I just want to reflect when was the last transaction for each user.
Exmaple:
PatronID Last Trandate User_Name Active Current Balance
00001 15-May-2005 Albert Borw 0 $3.00
I got almost all the data work, but I'm missing the last transaction date. I don't know if you have a good way to get it.
Thanks,
|
|
IP Logged |
|
elnino26
Newbie
Joined: 17 Feb 2009
Location: United States
Online Status: Offline
Posts: 8
|
Posted: 18 Feb 2009 at 7:40am |
Dell,
The report is getting inactive users with balance in their account that also reflect the last transaction date. The report is group by Active and Inactive, and users who have at least $10 in their account. The report is reflecting UserdID, UserName and UserFirstname, Active or Inactive, Current Account Balance, and LastTranDate (I'm missing this). I just need to get from the DB the just the last transaction date. For example:
User TransDate:
01/20/2006
02/20/2006
03/15/2007 *
* I need to reflect just this date in my report.
PatronID Last Trandate User_Name Active Current Balance
00001 15-Mar-2007 Albert Borw 0 $3.00
Edited by elnino26 - 18 Feb 2009 at 7:41am
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 18 Feb 2009 at 7:56am |
What I hear you saying is that you have a group on Status (Active vs Inactive) and a group on Current Balanct (>= $10 vs < $10) Try this:
- Add a third group on PatronID or User_name (however you want to sort the users). You're going to put your data in the header section for this group, NOT in the details!
- Add a descending sort on TranDate.
- Suppress the details section and probably also the footer for you new third group.
Because your data is in the group header and you've sorted the dates descending, the date of the most recent transaction should now appear with your data.
-Dell
|
|
|
|
IP Logged |
|
elnino26
Newbie
Joined: 17 Feb 2009
Location: United States
Online Status: Offline
Posts: 8
|
Posted: 18 Feb 2009 at 8:39am |
Dell,
I just need to get something like this:
PatronID Last Trandate User_Name Active Current Balance
00001 "15-Mar-2007" Albert Borw 0 $3.00
The set up that you told will not work as I want. I need to get some the user details with their last transaction date.
Thank you so much for your help.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 18 Feb 2009 at 9:09am |
Have you tried it? What other user details are you looking for? The data that I'm seeing in your example doesn't explain why this won't work.
The only other option I can see involves using a subreport for the date and uses this same technique, but that will considerably slow down the report.
-Dell
|
|
|
|
IP Logged |
|
|
|
