Jump to content
Sign in to follow this  
Kent Searight

Subsummary not showing expected values

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.