Skip to Content
0
Former Member
Jan 13, 2012 at 08:04 PM

How do I add fields from two levels of groupings

24 Views

I posted before, this seems pretty straightforward, but maybe I'm complicating too much.

I have an Invoice Line line item coming from table Invoice.

Each Line has many details coming from table Line.

table.Invoice is left joined to table.Line

Report looks beautiful, but what I need to add single value table.Invoice.Price to multiple values from table.Line.Price, giving me total.Price

My design has table.Invoice.Price in the header with table.Line.Price as detail records.

When I use Sum, it adds Invoice.Price as many times as there are Line.Price entries.

Original workaround was a formula - AVG(Invoice.Price)+SUM(Line.Price).

Now I need to Grand Sum this, but it doesn't allow me to Sum this formula.

Any ideas? Restructure somehow?

I've also tried WhilePrintingRecords with formula SUM(Invoice.Price)+SUM(Line.Price), figuring that Invoice.Price is only printing once so should only be counted once, but don't seem to get the expected results.

HALP!