Solved

Crystal Reports XI Display 0 for Negative Number

Posted on 2013-12-27
7
1,414 Views
Last Modified: 2013-12-27
Same report as my initial Date Filter question from yesterday... in the attachment you will see I've suppressed the report detail line and am looking at the Sum of TimeTicketDet.ManHrs in GF1 and Sum of OTHrs as the formula @OT...
Sum ({TimeTicketDet.ManHrs}, {TimeTicket.EmplCode})-40

When the Sum of GF1-EmplCode TotalHrs is < 40 the @OT formula is producing a negative number. It's the negative OT value I would liked displayed as 0.

I thought to rewrite @OT as...
if {@OTSumm} < 0 then
0
Else
Sum ({TimeTicketDet.ManHrs}, {TimeTicket.EmplCode})-40

But I get "formula cannot refer to itself, either directly or indirectly"

@OTSumm looks like...
WhilePrintingRecords;
NumberVar OTSumm:=OTSumm  + {@OT};

Which leads me to believe I need to declare a NumberVar for the OTHrs. I feel like I'm mixing summary and running total techniques making it unnecessarily convoluted and complex. There's got to be a more elegant way write this report.

The ultimate goal is to have the report filter for LastFullWeek TimeTicket records by EmplCode; sum total hours and display OT as anything > 40 and anything in the OT column < 40 as 0.
Doc5.pdf
0
Comment
Question by:Lee Ingalls
  • 3
  • 3
7 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 39742036
Did you try to create a new formula :
if {@OTSumm} < 0 then
0
Else
Sum ({TimeTicketDet.ManHrs}, {TimeTicket.EmplCode})-40

and to use it instead of {@OTSumm}  ?
You will still need to place {@OTSumm} on the report in order to calculate the values , but it can be suppressed
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 39742253
Try this for @OT

If Sum ({TimeTicketDet.ManHrs}, {TimeTicket.EmplCode}) < 40 then
  0
Else
   Sum ({TimeTicketDet.ManHrs}, {TimeTicket.EmplCode})-40

Open in new window


mlmcc
0
 
LVL 7

Author Closing Comment

by:Lee Ingalls
ID: 39742449
Thank you mlmcc! While both vasto's and your suggestions worked; I choose yours for the simplicity of the single edit. Felt like I was close; but don't work with Crystal everyday to be sure... repetition is the father of skill!
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 7

Author Comment

by:Lee Ingalls
ID: 39742457
A follow up question is... is this the most efficient way to construct this report?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39742693
Without seeing the actual report and the data it is hard to say.

DOes it run quickly or take several minutes?

mlmcc
0
 
LVL 7

Author Comment

by:Lee Ingalls
ID: 39742714
It's pretty quick... about one second to refresh report data. Refining the question... is summary more efficient than running totals? Does one have benefit over the other?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39742731
They really serve different purposes though the end result is much the same (a summary of the data)

Summary functions are evaluated early in the process so they can appear in a group header or the report header where as running totals are evaluated as the report is printed so the final value is only available in the group footer or report footer

Running totals can be used to show the running total in the detail section but a summary will show the final value.

Running totals provide the ability to determine when they are executed and when they are reset.  Summary functions include all records and are controlled through the 2nd parameter for the group to summarize on.

mlmcc
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 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

12 Experts available now in Live!

Get 1:1 Help Now