Solved

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

Posted on 2014-11-17
5
191 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

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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
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…

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

10 Experts available now in Live!

Get 1:1 Help Now