Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Insert timestamp data field of max summary

Posted on 2016-07-25
8
Medium Priority
?
64 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 101

Accepted Solution

by:
mlmcc earned 2000 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 101

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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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 101

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 101

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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…
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview

972 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