Solved

Insert timestamp data field of max summary

Posted on 2016-07-25
8
32 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 2

Author Comment

by:phil435
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for the help!
0
 
LVL 100

Expert Comment

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

mlmcc
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now