Tips and Tricks: Determining "Max" Record

Determining "Max" Record

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1972
Printed Date: 28 Apr 2024 at 8:05pm

Topic: Determining "Max" Record

Posted By: mrc161
Subject: Determining "Max" Record
Date Posted: 03 Jan 2008 at 10:38am

Any help on this would be appreciated.

I'm working with a table that has multiple rows for one account. One of the columns is a date column. All I care about is the date in the last row regarding each account.

There is a sequence column, as well, which tracks the number of rows per account. So in other words, if the account has 2 different dates, it would have two rows, one with sequence 1 and one with sequence 2. All I care about is the date in the row with sequence 2.

But there could also be accounts with 3,4, up to 8 rows. I've tried playing with the Maximum function, but every time I do, it tells me I'm trying to utilize the function too early, or there are sums and/or running totals that it can't perform it on.

Bottom line, any idea how I can specify that for each account, all I care about is the information in the LAST row without pulling in any info from the prior rows...

Thanks, Mike

Replies:

Posted By: Lugh
Date Posted: 03 Jan 2008 at 11:34am

From the error you described, there is clearly a bit more going on here that just returning the last row of a relatively simple dataset. You may need to get more specific, if these solutions don't help.

A couple of questions, first. Do you need the entire last row, or just the greatest date? Are you using the other rows in the report, or can you afford to have the report only return the last row? Where on the report (i.e., in what section) are you trying to use this information?

Am I correct in understanding that an account can have multiple records for the same date, and that in such a case we look at the sequence field to sort within the date?

If you do want just the last row on the report, and don't care about the rest of the data, then I would perform this operation in SQL. It's a relatively simple self-join operation there, and you can find the details in any SQL book (or I can provide it, if you need).

If you are returning all the rows, and need to pull out the last record for additional display purposes, you have several options. The simplest is to create a new details section, with a formula to suppress that looks like:

{MyReport.MyDate} <> Maximum({MyReport.MyDate}) OR {MyReport.Sequence} <> Maximum({MyReport.Sequence})

If you need to reference it in a group footer, you can try a few things. One is to use a SQL Expression to retrieve the information. This may be harder than you want to go with, though. You can create a series of global variables, and store the data in them when the maximum record is evaluated. This approach may create issues with timing, depending on what you're doing with this information. You can simply sort all of your records appropriately, in which case the "current" record when processing the group footer is the last record. This method is not always reliable, though.

If you need to reference multiple "last" records, and do some kind of analysis on them, I would strongly recommend using a subreport.

Posted By: mrc161
Date Posted: 03 Jan 2008 at 12:17pm

Thanks for the info - to get more specific, it's basically a workflow problem.

Specifically, imagine there are several "queues" an account can go through - however that account can jump from queue to queue at any time (but one of the fields tracks the start date of each time it hits a queue).

So imagine I want to find all accounts submitted in the "Submit Queue" in December.

Say an account initially hit the "Submit Queue" in November (so it has a start date in November) - then it went through a few other queues, but someone put it back in the "Submit Queue" in December again (so now there are two rows of information, one with a start date in November, and one with a start date in December). My report would say that the account originated in December AND November. However, I would just want to pull November (this is more of a "Minimum" than Maximum", but same essentially concept).

Does that help clarify? Again, thanks for the help! Mike

Posted By: Lugh
Date Posted: 04 Jan 2008 at 4:46am

That does seem to clarify the situation.

I would go with a SQL solution. If possible, change the query in your database. If that's not possible, use the Add Command feature when creating your datasource to enter a SQL query.

Your query should look something like:


SELECT Main.*
FROM MyTable Main
JOIN (SELECT AccountID, 
         MAX(QueueDate) AS MaxDate
         FROM MyTable
          GROUP BY AccountID) Filter
ON Main.AccountID = Filter.AccountID
AND Main.QueueDate = Filter.MaxDate

Obviously, your solution will actually be a bit more detailed. But hopefully that should give you a push in the right direction.