Solved

Insert timestamp data field of max summary

Posted on 2016-07-25
8
59 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

710 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