Solved

Calculated field based on a subreport field divieded by main report field

Posted on 2014-11-17
5
212 Views
Last Modified: 2014-11-18
I have a Non-Conformance report that has a couple of subreports.  One of the subreports displays a sum of invoices by vendor id.  My main report displays the Sum of the Total NC Cost.  I want to calculate the Impact $ which would be (Sum of @ Total NC Cost / Subreport: Sum of Command.ext_recvd)

How do I do this?  

I was reading something about variables, but I have never used them so I don't know how to use them.
Vendor-Issues---Summary.rpt
Vendor-Spend-Subreport.rpt
Vendor-Name-Subreport.rpt
0
Comment
Question by:ITworks
  • 3
  • 2
5 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 500 total points
ID: 40448464
Basic idea

In the main report
Add a formula to the report header
WhilePrintingRecords;
Shared NumberVar SubreportTotal;
''

Open in new window


IN the subreport add a formula to get the total
WhilePrintingRecords;
Shared NumberVar SubreportTotal;
SubreportTotal := Sum({YourField});

Open in new window


IN the main report in a section after the subreport add a formula to display or calculate
WhilePrintingRecords;
Shared Number{mVar SubreportTotal;
{MainReportTotal / SubreportTotal 

Open in new window


mlmcc
0
 
LVL 4

Author Comment

by:ITworks
ID: 40448498
I created the first formula in the main report and named it calc
I created the second formula in the sub report and named it calcsubreport
I'm trying to create the third one and get a bunch of errors.

Should it really be?  Because I get errors when I enter it exactly as you wrote it down.   When I try to enter my report name in there, I get errors too.

  WhilePrintingRecords;
Shared Number{mVar SubreportTotal;
{MainReportTotal / SubreportTotal
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 500 total points
ID: 40448608
Sorry missed the typo
If the mainreport total is a variable use

WhilePrintingRecords;
Shared NumberVar SubreportTotal;
Global NumberVar MainReportTotal;

MainReportTotal / SubreportTotal
 

Open in new window


If it is a formula
WhilePrintingRecords;
Shared NumberVar SubreportTotal;

{@MainReportTotal} / SubreportTotal
 

Open in new window


mlmcc
0
 
LVL 4

Author Comment

by:ITworks
ID: 40449876
My main report is a summary,  Will that work?

So my final formula on the main report is:
WhilePrintingRecords;
Shared NumberVar SubreportTotal;

Sum ({@Total NC Cost}, {@Vendor}) / SubreportTotal

I don't get any errors when I test the formula however when I try to run it I get "Division by zero" error and it highlights
Sum ({@Total NC Cost}, {@Vendor}) / SubreportTotal
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 40450212
Anytime you do a division unless you KNOW the divisor cannot be 0 you need to test for it and handle the 0 divisor

WhilePrintingRecords;
Shared NumberVar SubreportTotal;
If SubreportTotal = 0 then
    0
Else
    Sum ({@Total NC Cost}, {@Vendor}) / SubreportTotal 

Open in new window


mlmcc
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

761 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