Subsummary not showing expected values

[Kent Searight]

Hi all,

This may actually be a question more geared towards sorting, but I'll try it here and see what happens. What I'm trying to do is report sales by quarters and months with the following criteria:

Invoices must list all line items.

Monthly and quarterly grand totals must be broken into line item sales, taxes and shipping.

As you may see from my example, everything works except the monthly and quarterly tax and shipping grand totals that appear in the trailing subsummary parts.

Many thanks in advance for any help offered!

SubSummaryReport.fp7.zip

[mr_vodka]

Hi Kent. Try and change the print layout to reflect the Invoices table, not the line items.

[Kent Searight]

Hi John,

But how do I list the line items if the layout is based on Invoices? That is, without using the List ( ) function or similar.

Thanks!

[Kent Searight]

The first sample file I uploaded isn't right. I had the wrong field in the Line Items summary by month and quarter subsummaries. Sorry about that!

The issue is still the same, though, in that I need to show the line items AND show the subsummaries of taxes and shipping by quarters and months.

SubSummaryReport2.fp7.zip

[mr_vodka]

Hmmm. I think the issue here is that even though it can be sorted by a related field, the summary and break field should come from the table that the report layout represents. You may have to use some calc fields in the lineitems table to get the values from the related invoices table and then have your summary fields in the lineitems table.

[Søren Dyhr]

the summary and break field should come from the table that the report layout represents

Indeed! Read this:

http://network.datatude.net/viewtopic.php?t=128

Now some hours later have I done what Ilyse suggests, which by and large to change a fair share of the fields location to the LineItems table, one trick though is the shipping cost as well as the tax belong to the first related record, so measures should be instated if you were to delete first portal row to and really would like to save the tax and shipping costs previously entered ...but often are such sales dependent anyway - so it's of less importance perhaps.

--sd

SubSummarySdMod.zip

Edited April 24, 2007 by Guest

[comment]

It's a clever idea, but... I think the experience of seeing shipping and tax disappear if the first line item is deleted could be rather jarring.

I believe the simplest way would be to base the report on the Invoices table, and use sliding portals for the LineItems - this seems to work reasonably well in v.8.5.

This kind of reporting is in any case a bit of a 'bastard': suppose for a moment that not all of invoice's item are included in the found set. What then should be the subsummary of shipping for the invoice? Should it come through the relationship (i.e. full shipping charge), or should it come from a summary (partial shipping charge)?

This also raises the question of how are shipping charges determined, and couldn't this be done in the LineItems table to begin with (there seems little doubt that tax could be done this way).

Even if the shipping must be determined at Invoice level, it's possible to arbitrarily calculate partial shipping in LineItems, and use a summary field on the result.

[Søren Dyhr]

This also raises the question of how are shipping charges determined, and couldn't this be done in the LineItems table to begin with (there seems little doubt that tax could be done this way)

Yes I thought of it, but a lot of contries in europe have differentiated sales tax, whether or not an item is considered a luxury or basic such as food... this will of course be done via a lookup of the classification ...but as such would it be guessing beyond the scope of the original template.

The shipping is as you put it:

is in any case a bit of a 'bastard'

....indeed the most complex problem to deal with here, and any measure to make it follow each of the entires in the lineitems would greatly help.

--sd

[Kent Searight]

Thanks for your input, Søren and Michael.

I had to move on to something else, hence the belated thanks.

I'm still on the fence as to how to handle this, but if I come up with something not already mentioned, I'll be sure to post it.

BTW Michael, what do you mean when you say

use sliding portals for the LineItems

IOW what's a sliding portal?

[comment]

Perhaps not the best term, but I meant a portal with enough rows to accommodate the largest expected count of related records, and set to slide up when printing.

[Kent Searight]

Ah, that's what I thought you were talking about, but I just wanted to make sure.

That's probably not practical in this case because the report can get over 100 pages long (I know it sounds like "info overload", but it's what the client wants)

[mr_vodka]

Yes. If you select the portal itself as one of the items that will slide, the empty rows of the portal where there are no related records will be dropped off.

[comment]

I don't think it matters how long the report is - what matters is how long is a single record. If any single invoice can have more line items that would fit on ... I don't recall the exact number now, is it 7 pages? ... then it won't work for you.