Technique on creating a report

[Davesmind]

I am looking to develop the correct technique for building out a report from data. Normally this would be easy since all of my data is controlled by me in that I build the tables and fields. In this case I need to pull records from another database system so all of the data will be pulled into one table. This table will also contain data from the year before it which will also need to be compared. What I am asking is what is the best way to attack this data. I guess the first step is to explain the data. It basically consists:

 

sales_person_id

customer_id

order_entered_amount

order_paid_amount

transaction_date

 

I will have one line for each order entered and each order paid (similar to a general ledger).

 

Example data:

 

Sales_person_id   |   customer_id   |  order_entered_amount  |  order_paid_amount  | transaction_date

 

Joe Sales  | WPN202 | 500.00 | 0.00 | 1-20-2014

Joe Sales  | WPN202 | 0.00     | 500.00 | 1-20-2014

Joe Sales  | WPN202 | 250.00 | 0.00 | 1-23-2014

Joe Sales  | WPN202 | 0.00     | 250.00 | 1-23-2014

Joe Sales  | QZZ101  | 250.00 | 0.00 | 1-22-2014

Joe Sales  | QZZ101  | 0.00     | 250.00 | 1-22-2014

 

I need to break the data down so that I can see each sales person and the customer totals for that year along with a comparisons to prior years data. I keep thinking that I can create a second table with all of the sales people (Sales people can be  and relate the data, but then how do I collect each customer and total each years amounts.

 

I want the data to appear as such:

 

Sales Person | Customer_ID | Customer Totals for 2014 (base on orders) | Customer Totals 2013 (based on orders) |

 

Joe Sales | WPN202 | 750.00 | 750.00 | 750.00

Joe Sales | QZZ101 | 250.00 | 250.00 | 250.00

 

Or even 

Joe Sales

               WPN202 | 750.00 | 750.00 | 750.00

               QZZ101 | 250.00 | 250.00 | 250.00

 

Overall I want to develop better techniques within Filemaker and would not like to know let say the easiest way, but the best way to build the report. I also don't want to use portals as I may need to print the various reports and portals will complicate this. Can i use the single table and develop the report with instances of the table and relate the data? How would I pull it all together? Should I create a sales person table and then relate the data, but this would give me the totals for the year but not for each customer. Maybe add in sub-summary parts to the layout, but again how to filter it down to just one year and still keep the ability to pull the prior year. Maybe sql to collect the data, but then I would need to break out the customers. 

 

Thanks all for helping me learn the ways of Filemaker. If you need more information please ask me.

[Davesmind]

I was able to obtain a favorable result using sub-summary and ExecuteSQL. In this instance I have the data layout and then a viewing layout. The viewing layout is just a duplicate of my data layout. I then remove the body of the viewing layout and create 2 sub summary parts. I sort by the first and then the second sub summary. I then use ExecuteSQL with Calculation fields to query the data based on the criteria necessary for each collection of data. This allows me to see exactly the data I wanted and is pretty simple. I would still like to know if there is a much better alternative to this approach. I did notice that I had to refresh the ExecuteSQL fields if data change on the layout. I changed each field calculation storage to calculate as needed. If I switched between layouts the data updated. If data changed on the layout, without switching, then the sql fields did not update. A quick refresh of the layout forced them to update. Just an FYI.

[Kris M]

You should also investigate the Virtual List technique for reporting

http://filemakerhacks.com/tag/virtual-list/

http://www.mightydata.com/blog/taming-the-virtual-list-part-i/

http://www.seedcode.com/virtual-list/