13 Replies Latest reply on Mar 10, 2016 7:40 AM by beverly

# Report

I'm trying to create a usage report for a customer that tells how many of each product they've ordered in the last year. So I need to have a total when sorted by a part number.

Heres what it looks like unsorted

Sorted by Product ID with a "Trailing Group by Product:ID"

As you hopefully can see, the Total does not compute correctly

This is what it looks like behind the scenes- Maybe the problem is that its running a Trailing subtotal COUNT as opposed to a TOTAL? But I'm not able to select "TOTAL"...

• ###### 1. Re: Report

Are you counting by ProductID?

Or do you need to Sum (Total) by Quantity Order... (I can't see the field name)?

beverly

• ###### 2. Re: Report

I need a sum total of the quantity ordered when sorted by product ID

Katie Neal-Rizzo

The Tree House, Inc.

PO Box 413

Norwood, MA 02062

Ph: (800) 595-6651

Fx: (781) 278-0706

http://www.thetreehouseinc.net

thetreehouseinc1@aol.com

• ###### 3. Re: Report

Hi Katie

1. Add a Trailing Group by Product ID

2. Click on the Price column to add a Trailing Subtotal --> Total

It should work

Chloe

• ###### 4. Re: Report

THank you. I have done both of those and its only totalling the quantity ordered from the first invoice.

Katie Neal-Rizzo

The Tree House, Inc.

PO Box 413

Norwood, MA 02062

Ph: (800) 595-6651

Fx: (781) 278-0706

http://www.thetreehouseinc.net

thetreehouseinc1@aol.com

• ###### 5. Re: Report

I think you mean (#2) that Quantity Order... should be the column for the trailing subtotal.

beverly

• ###### 6. Re: Report

Judging from the column headers, your layout is based on the wrong table occurrence.

You want the layout based on a table occurrence of line items.

• ###### 7. Re: Report

Great! So I created a new layout based on the Line Items which is now computing correctly, but it's running the report for every customer, not just the customer I specified...

Katie Neal-Rizzo

The Tree House, Inc.

PO Box 413

Norwood, MA 02062

Ph: (800) 595-6651

Fx: (781) 278-0706

http://www.thetreehouseinc.net

thetreehouseinc1@aol.com

• ###### 8. Re: Report

Re: "it's running the report for every customer, not just the customer I specified..."

If you've got that far, try doing a find in your line items TO to isolate the records for the customer you want to report.

• ###### 9. Re: Report

It is creating the totals based on the records you specified.

If you want to generate totals only for certain customers then find only the line item records associated with those customers.

And any other conditions you require.

• ###### 10. Re: Report

You guys have been so helpful! I have the report showing up as I want it to now.

HOWEVER, I can save it as PDF no problem. When I try to Export to Excel it says I need Rosetta which doesnt work with my Mac OSX software? If I try to save as a .txt file, I lose the formatting... any ideas?

• ###### 11. Re: Report

are you using Export or Save/send Records?

beverly

• ###### 12. Re: Report

I've tried both

The Tree House, Inc.

PO Box 413

Norwood, MA 02062

Ph: 800 595-6651

Fax: 781 278-0706

www.thetreehoiseinc.net

• ###### 13. Re: Report

unfortunately, FM export (either one) is not data AND summary aware:

Exporting data from FileMaker Pro | FileMaker

(#9 - Export grouped summary values instead of individual records)

ExecuteSQL() along with Virtual List (temporary table) may be a way to do this.

I usually use XML and XSLT to get formulas (thus summaries are in Excel calculations) or a grouping technique called Muenchian  (grouping or method in XSLT) that rearranges any export from FM that might also include a summary field in the XML export.

Make a request to have an export that includes these summaries!

Product Ideas

beverly