Month by Month report

[sal88]

Hi all

I have a list of services with different dates.

e.g.
Broadband, £10.00, 27/09/2015
Domain, £15.00, 27/09/2015
Broadband, £10.00, 27/10/2015
Domain, £15.00, 27/10/2015
Broadband, £10.00, 27/10/2015
Domain, £17.00, 27/10/2015

What is the best way of creating a report that shows totals by service and month?

e.g.
Service        September 2015        October 2015        
Broadband        £10.00                      £20.00
Domain             £15.00                       £32.00

It can be any kind of report, e.g. Excel or csv.

Any suggestions appreciated.

Thanks

[comment]

Filemaker is not really good with cross-tab reports. Could you live with a report formatted as;

September 2015        
• Broadband        £10.00
• Domain           £15.00
October 2015        
• Broadband        £20.00
• Domain           £32.00

or:

Broadband       
• September 2015       £10.00
• October 2015         £20.00
Domain        
• September 2015       £15.00
• October 2015         £32.00

These are very easy to produce.

 

[Josh Ormond]

The best way you are going to generate a cross-tab report is to build it as a Virtual List. If you use that approach, will depend on how you are with both scripting and text parsing.

If you can mange it with what comment posted, that would be an easier route for you.

[sal88]

Ah I see. I'll look in to the virtual list method.

Cheers guys

[comment]

47 minutes ago, sal88 said:

I'll look in to the virtual list method.

Unlike Josh, I am not sure that is the "best method".

Note that no matter which method you choose, you cannot have a dynamic, unlimited, number of columns (unless you export the report as a text file, e.g. as csv). If the categories (Broadband, Domain) are constant, use them as the columns - because months will always be accumulating as time goes by. Use months as the columns only if your report finds records within a date range of known and fixed number of months.

 

 

Edited December 2, 2015 by comment

[Josh Ormond]

Completely agree with you @comment. There is no silver bullet here. It is definitely a case of needs assessment and skills assessment. "What are the options? What skills do I possess to accomplish the goal? What skills can I learn to accomplish the goal?" To name a few questions...

[comment]

13 minutes ago, Josh Ormond said:

a case of needs assessment and skills assessment.

Well put.

[sal88]

16 hours ago, comment said:

you cannot have a dynamic, unlimited, number of columns (unless you export the report as a text file, e.g. as csv).

Yes, I was wondering why I just couldn't get my head around how it was going to be done! I'm going to start with the conventional method that you recommend then may move on to the more manual method of scripting/parsing.

Thanks again.