Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Tips and Tricks
Message Icon Topic: Determining "Max" Record Post Reply Post New Topic
Author Message
mrc161
Newbie
Newbie


Joined: 27 Dec 2007
Location: United States
Online Status: Offline
Posts: 19
Quote mrc161 Replybullet Topic: Determining "Max" Record
    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

IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet 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.


IP IP Logged
mrc161
Newbie
Newbie


Joined: 27 Dec 2007
Location: United States
Online Status: Offline
Posts: 19
Quote mrc161 Replybullet 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
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet 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.

IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.