I am going to run into a problem where I cannot sum...

[cmyoung]

Hey everyone, thanks for taking the time.

I am trying to avoid using a sum to solve this problem I have, because later on I need to summarize my formula.

What I have right now is a invoice totals and the payments to that invoice. But the invoices are paid gradually. So in my report I am faced with something like this:

INV# INV Total PMT
001 1,600 800
001 1,600 800

The payments are for the same invoice so they have paid off the entire invoice. But I need a way to subtract both payments from the one invoice total. I cannot sum the pmts because I need to summarize a formula I am using later. So basically I need a formula that is the invoice total (1600) - the payments (800 + 800) just to get the 0.

Aswell not all invoices are paid like this, some are paid off in one payment so I just cannot divide my invoice total by a number.

I am using crystal reports 2008.

Thanks!

 

[lbass]

You have to sum the payments somehow. You could just use:

{table.invoiceamt}-sum({table.totalpmt},{table.invno})

Then use a variable to do your later summary. It might help if you explained what that is.

Alternatively, you could create a SQL expression for the sum:

(
select sum(`totalpmt`)
from table A
where A.`invno` = table.`invno`
)

You would likely have to build any selection criteria into the SQL expression also, since it accesses the database directly.

-LB

 

[cmyoung]

Thanks lbass for your reply,

I have set up my report with the sum of the pmts and I get the numbers that I need but I also need to sum my formula that uses the sum of the pmts in it. So I cannot sum the formula.

Here is the a part of the formula that i am using:

if {OITR.ReconDate} <= {?EndDate}
and ({JDT1.Debit}+{JDT1.Credit}) - Sum ({ITR1.ReconSum},{JDT1.TransId}) <> 0 then ({JDT1.Debit}+{JDT1.Credit}) - Sum ({ITR1.ReconSum}, {JDT1.TransId})

({JDT1.Debit}+{JDT1.Credit}) = Invoice Total
Sum ({ITR1.ReconSum},{JDT1.TransId}) = Sum of the payments

But ofcourse I cannot summarize this formula.

Thanks

 

[lbass]

You want to sum it at the report level? Create a formula like this and place it in the invoice number group section and suppress it:

whileprintingrecords;
numbervar x := x + {@yourformula};

In the report footer use the following to display the results:

whileprintingrecords;
numbervar x;

-LB

 

[cmyoung]

That's a Bingo! I had seen this around the forums but I didn't quite get where to put it in my report.

Thanks a bunch for your help lbass, it works, i just don't fully understand

I understand the variable part but what does the whileprintingrecords do? forcing a second-pass?

 

[lbass]

Yes. The whileprintingrecords is necessary to the accumulation across records.

-LB

 

[cmyoung]

Uh oh. problem with my report...

is it possible to have this equation not make a cummulative total? because what I have is multiple companies on the same report as groups. So I have:

Company 1:
Totals: x

Company 2:
Totals: x+y

Company 3:
Totals: x+y+z

Hope that makes sense but the total for company 2 should just be y and company 3 should just be z. I am not too sure why this is happening unless the equation is saying to do this, which it probably is. I just need to see the totals for each company as its own.

Thanks!

 

[lbass]

Add a reset formula in the group header for the company group:

whileprintingrecords;
numbervar x;
if not inrepeatedgroupheader then
x := 0;

The display formula belongs in the company group footer. Suppress the accumulation formula, if you haven't already.

-LB

 

[cmyoung]

That works great!

Ofcourse that resets my report totals. Would I be able to place a statement in the report header so that the report footer total works again?

Thanks again and again

 

[cmyoung]

wait could i just double the accumulation by giving it another variable and then just totaling it as a different variable correct?

 

[cmyoung]

Sweet that worked. Thanks LBass for your help!

 

[cmyoung]

and just so I don't confuse anyone, when i said double, i really just meant make another equation for the accumulation of the equation but give it a different variable. This way the restriction did not affect the new variable and I was able to sum it as a report total. All thanks to LBass's formulae.

Sorry for all the reposts.

 

[lbass]

You can just add the new variable to your first accumulation formula, but you would need a separate display formula for the report footer. Glad you figured it out.

-LB