Technical Questions: Select the last record and show it in my report

Select the last record and show it in my report

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=5538
Printed Date: 06 May 2024 at 11:52pm

Topic: Select the last record and show it in my report

Posted By: elnino26
Subject: Select the last record and show it in my report
Date 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,

Replies:

Posted By: hilfy
Date 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

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: JohnT
Date 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 !

Posted By: elnino26
Date 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,

Posted By: hilfy
Date 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

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: elnino26
Date 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,

Posted By: elnino26
Date 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

Posted By: hilfy
Date 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

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: elnino26
Date 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.

Posted By: hilfy
Date 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

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: elnino26
Date Posted: 18 Feb 2009 at 11:43am

Dell,

the issue is that in the example I copy the way that the report is supposed to work. Right now, the report looks like the example fields with the difference that I don't get the last transaction date (in red). I don't know how I can get just the last transaction as show in the example. i don't need the details of the others transaction dates, I just need to get the last date in that field for each user. Example:

001 15-May-2006 Albert Brow 0 $3.00

002 16-Jun_2005 Maria Garcia 0 $4.00

Thanks,

Posted By: elnino26
Date Posted: 18 Feb 2009 at 11:47am

Dell,

PatronID LastTransdate User Name Active Balance

001 15-May-2006 Albert Brow 0 $3.00

002 16-Jun_2005 Maria Garcia 0 $4.00

It's not working right now. I'm trying to get this info in the report.

Thanks,

Posted By: hilfy
Date Posted: 18 Feb 2009 at 11:58am

I think we're having a problem understanding each other here. Let me see if I can rephrase my question.

Is the data you show above the only data that's in your report or is there some other data? If there is other data, how is it formatted? Is it summaries? Is it in header sections? Is it in details sections? What does it look like. What the rest of the report looks like has a bearing on how you can format the report to get to the data you're looking for.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: elnino26
Date Posted: 19 Feb 2009 at 6:33am

Dell,

I think you're right, we're having issue understanding each other.

The example info that is in the message is the info that is supposed to be in the report. All the info is showed in the report, but with the only issue that the 'transaction date' is not in the report. This is the issue that I'm trying to fix. There is not other data in the report. I'm using UserID, UserLast and first name, Last TransactionDate (which is not working now), Active, and account balance (I'm using three different tables in order to get all the info needs). The data is group by User Classification. I made two statement (Select Expert) to filter the data. First, the date is filter if the user is inactive (0). Second, if the user have at leasta $10 in his/her account (>=10). I have two summaries in my the report one for each user "clasification". And second, one for the grand summaries total. So as yuo can see, the report is almost working, but I'm missing part where the last transaction date is supposed to be showed in the report. This info that is not working right now. I need to add this info into it, but I don't know how to do it.

I hope that now it's more clear for you.

Thank so much.