Insert timestamp data field of max summary

Hello. I have two data fields in my crystal report that have a grouping based on month. I have inserted the max value of field2 in the group footer and would like to have field 1's corresponding value inserted in the footer as well. Here is an example:

Field 1 (time)          Field 2 (value)
07-01-2016             100
07-02-2016             200
07-03-2016             300

Group footer
07-03-2016             300

I can get the max of field 2 to show up in the group footer, but cannot get the associated time of the max in the group footer. Anytime I insert field 1 in the footer in picks the last value an inserts it there. Any suggestions?
LVL 2
phil435Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
mlmccConnect With a Mentor Commented:
There are 3 ways to do this

First would be to use a SQL expression to select the date based on the group values and the max value.

Second would be to use a command for the data source and do it in the SQL.

The thirds method is to use a set of formulas.  DO you want the last date with the max value or just any date that corresponds to the max value?

Basic idea.
Add a formula to the report header.
Name Declare Variables
WhilePrintingRecords;
Global NumberVar MaxValue;
Global Date Var MaxValueDate;
''

Open in new window


In the group header add a formula to reset the values
Name Reset Variables
WhilePrintingRecords;
Global NumberVar MaxValue;
Global Date Var MaxValueDate;
MaxValue := 0;
MaxValueDate := Date(1900,1,1);
''

Open in new window


In the detail section
Name CalcMaxValueDate
WhilePrintingRecords;
Global NumberVar MaxValue;
Global Date Var MaxValueDate;

If ({Field2} > MaxValue  then
(
      MaxValue := {Field2};
      MaxValueDate := {Field1};
      '';
);
''

Open in new window



In the group footer
Name DisplayMaxValueDate
WhilePrintingRecords;
Global Date Var MaxValueDate;
MaxValueDate

Open in new window


mlmcc
0
 
phil435Author Commented:
Hi Michael,

Yes, I want the date that corresponds to the max value of field 2. The third method appears to work as expected. If I use the second method using a SQL eqpression field, then do I need to specify the grouping by month in the SQL statement or will the group section filter this for me?

Also, I may need to end up using the SQL method anyway since my date/time field is in Eastern standard time and the presentation needs to be adjusted for Central standard time.
0
 
mlmccCommented:
Crystal should handle the time conversion so long as you are running in the Central time zone.

SQL Expression
You would need to specify the date range for the group in the SQL Expression.

mlmcc
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Access It Now
 
phil435Author Commented:
The data is already stored in the database as a datetime, so how does Crystal know to apply the 1 hour offset to this field? Does a datetime field have the timezone associated with it?
0
 
mlmccCommented:
It depends on the database and how the data is stored.  I worked a project using XML files as the data input and when I ran the reports (Central time zone) I saw one hour earlier than the client who ran them on a East coast server.

Many database store the time in GMT rather than local time and Crystal accounts for it that way based in the server/client settings for time zone.

mlmcc
0
 
phil435Author Commented:
Ok, thanks. I was able to use the shiftdatetime function in a formula to offset this by one hour and it looks good now.
0
 
phil435Author Commented:
Thanks for the help!
0
 
mlmccCommented:
I don't know why sometimes it does an automatic adjustment and at others you have to do it.

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.