Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2014-11-17
5
208 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Suggested Solutions

Title # Comments Views Activity
Crystal reports filter out records 2 35
Run a batch file when a Crystal Report is opened 5 46
A string is required here error 20 21
Crystal Report sorting 6 16
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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

792 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