Solved

Insert timestamp data field of max summary

Posted on 2016-07-25
8
50 Views
Last Modified: 2016-07-26
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?
0
Comment
Question by:phil435
  • 4
  • 4
8 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 41727825
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
 
LVL 2

Author Comment

by:phil435
ID: 41728077
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 41728099
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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 2

Author Comment

by:phil435
ID: 41728113
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 41728178
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
 
LVL 2

Author Comment

by:phil435
ID: 41729366
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
 
LVL 2

Author Closing Comment

by:phil435
ID: 41729369
Thanks for the help!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41729414
I don't know why sometimes it does an automatic adjustment and at others you have to do it.

mlmcc
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question