Monthly Data List Report

[jrm]

Running FMP Adv 16. Looking for the best way to handle this situation. Examples are simplified.

 

I have a list of stores:

Red, Blue, Yellow, Green

Each store has a number for sales in each month (Jan, Feb, Mar, etc.) I would like to generate a report for output so I can plot the monthly sales on a graph (via Excel). Since sales data involves a new entry each month, I didn't think it a good idea to put everything in one table.  I currently have two tables - stores and sales. These are related via a unique code number for each store.

I can easily see monthly sales for each store in a layout using a portal. I cannot figure out how to have a report that would provide a list similar to:

Store:Red     JanSales     FebSales     MarSales     AprSales  
Store:Blue     JanSales     FebSales     MarSales     AprSales
etc...

Even better if the sales columns can total for each month, but that isn't necessary as it can be done after output to a spreadsheet.

Again, the full list is more complicated than this as I have individual department sales for each store and other factors I would need to use as filters for data.

What would be the best way to set things up and achieve the above result? I realize it's probably something simple, but I am just approaching things the wrong way.

Edited October 18, 2018 by jrm

[comment]

A Filemaker report can have a flexible number of rows, but the number of columns must be determined in advance. You can easily produce a report in the form of:

Store:Red
    - JanSales
    - FebSales
    - MarSales
    - AprSales
Store:Blue
    - JanSales
    - FebSales
    - MarSales
    - AprSales

but if you want a tabular format where all JanSales would be listed in a column, then it gets complicated.

[jrm]

Yes, a tabular report where all JanSales are in a column is exactly what I need. I need all twelve months (jan-dec) in their own columns.

This is just being set up. I am open to different table structures/relationships to make this work. Complicated doesn't bother me. A complicated up-front setup beats monthly hand sorting several departments over hundreds of stores to do individual and regional comparisons.

The easily produced format you show is where I am at now. Simple to do with a portal. It's the column format that has me stumped.

 

 

Edited October 18, 2018 by jrm

[bcooney]

You might want to check out this javascript approach:

https://www.soliantconsulting.com/blog/pivot-table-filemaker

[comment]

2 hours ago, jrm said:

I need all twelve months (jan-dec) in their own columns.

There are several possible ways to approach this.

My favorite method is to define a repeating calculation field (in the Sales table) with 12 repetitions, and have it populate the sale amount in the repetition corresponding to the month of the sale. This is easily summarized in both horizontal (subtotal by store) and vertical (subtotal by month) directions. I am sure I have posted a demo file showing this method, but I can't find it.

Another option is to used filtered portals - see an example here:
https://fmforums.com/topic/71836-getting-more-out-of-filtered-portals-in-version-11/

Note that these methods provide a solution for displaying the crosstab report within a Filemaker layout. If your goal is to export the data to Excel, then we are talking about a completely different issue.

 

 

Edited October 18, 2018 by comment