5 Replies Latest reply on Mar 14, 2013 7:23 PM by luisrosario

# Sum of main category and sum of secondary category. How to do it??

I'm trying to make a report where sort by city, and in every city I have clients, and each client has one or more machines.
For each city have a value for the trip (eg "x").
For each machine a value "y".
Want the sub-total sum of "x", the sum of "y" and the sum total of "x" + "y".
It is possible to do this in a single report?

example:

Report

London 1500.00
- Customer A - Machine 1: 100.00
- Customer A - Machine 2: 150.00
- Customer B - Machine 1: 100.00
Paris 2500.00
- Customer C - Machine 1: 200.00
- Customer D - Machine 1: 150.00

Sub Total Machines: 700,00 / Travel Sub-total: 4000.00

Total: 4700.00

I´m using FileMaker Pro 11 Adv.

Thanks

• ###### 1. Re: Sum of main category and sum of secondary category. How to do it??

You would create a subsummary part above the body part that subsums based on location (IE london, paris), just dropping in a merge field to display city name, and a calc(line total) calculation for the location total.

Then you would create a trailing grand summary part that held the sum calculations.

IE

calc_lineTotal - calculation field type = machine cost + travel cost

sum_machineSubTotal - Summary field type = running total of machine cost

sum_travelSubTotal - Summary field type = running total of travel cost

sum_total - summary field type = running total of calc_lineTotal

You would put the sum_total in the subsummary part, then all three sum_ fields in the trailing summary part.

And this one about trailing summaries:

1 of 1 people found this helpful
• ###### 2. Re: Sum of main category and sum of secondary category. How to do it??

Did not result. It still did not give me the travel values.

See it here:

https://dl.dropbox.com/u/42714230/Test_Sum_Category.fp7

• ###### 3. Re: Sum of main category and sum of secondary category. How to do it??

I took a quick look, you didn't mention you're putting fields (and sums) from two separate tables on the single report. That makes things marginally trickier.

All the summary fields for the display should be from the same table the layout is configured for in order for the math to show up correctly. In your case, the "maquinas" table.

Since there is a "cost" for each "machine", you need to calculate the "share cost" of that machine. IE if you have 4 machines in your report, and each of them has a cost of 200, you only want to return 200 (as opposed to a summary returning 800), so you calculate that each machine has a "share cost" of 50, and then summarize that.

I have adjusted, Check it out.

http://www.sendspace.com/file/b07b46

• ###### 4. Re: Sum of main category and sum of secondary category. How to do it??

First of all many thanks.

Two more things.

The real value i have for the machine cost is some value for all machines. i think this could help in the report, but i could not realize how to do it.

One more thing, i did not know that the summary fiels should be all from the some table. I realy have 3 tables that should be in report. Costumers, Machines and Service Orders. My questions is: should it work if i create a new table with lookup to each field i need to put in report. Really the summary fields are only from 2 tables but...

For end, in cetain cases i have 20 or more machines in some city, sharing the cost i think it could be dangerous about the rounding values. I must review the two or more, table problem.

Once again, thanks for the "light"

• ###### 5. Re: Sum of main category and sum of secondary category. How to do it??

As should have realized I am a beginner in Filemaker Pro.

Let me ask you one more thing. I've calculations and values (things like distances, fuel cost, expenses and so on...) in other tables, in "good modeling database" rules, do you think is good thinking to put all those values and calculations in only one table and make relationships between all tables.

Once again, thanks for your time.