Running total based on header amount plus detail

[Luvsql]

I have the following report"

GH1 Customer
GH2 Document
Det Applied Documents (could be no details for a document)

Example of data

GH1 ABC Company
GH2 Inv1000 $1000
Det Pymnt200 (600)
Det Pymnt201 (250)
GH2 Inv 1001 $600
GH2 Inv 1002 $400
Det Pymnt202 (150)

What I Need is a new column for each record for the balance ie

$1000
$400
$150
$750
$1150
$1000

The balance is the first amount, then difference between next amount and previous amount etc.

 

[synapsevampire]

An odd layout...

Create formulas to maintain the current balance, so each section which impacts the total would need a formula containing:

whileprintintingrecords;
numbervar MyBalance:=MyBalance+{table.field}

or if you're adding in an aggregate to it:

whileprintintingrecords;
numbervar MyBalance:=MyBalance+sum({table.field},{table.group})

-k

 

[Luvsql]

This is a very basic customer summary report based on invoices and payments. I just need to show how much each customer owes in a running balance.

 

[Luvsql]

I can get a running total based on the GH amounts fine, but if there are no details ie no payments for an invoice, how could I place the formula into the details section, if there aren't any details?

 

[synapsevampire]

The way that the data is generated is part of the problem.

Usually this type of report demonstrates it's values ALL at the detail level, your example shows sum unknown group header values that are somehow being used as part of the aggregate, although they must be values in the details as well.

The post doesn't make sense to me, so I urge you to provide example data (not what data looks like in the report), and the expected output of that data (what it should look like in the report).

What you're also asking for is to show data when there isn't any, and has nothing to do with the original post. If they are static values, then you might hardcode them. If they are values in the database, you might use the table that contains all of them and left outer join it to your current data so that you get all of the values.

-k