Display amount grouped by year

April 17, 201411 yr

hi!

I have a table which has Date, Amount and Source fields. I want to display the aggregated Amount for a particular Source grouped by Year. User can select the year range and we should be able to display aggregated Amount for all the years which fall under the selected range. ( Please refer attached screenshot: 'Required Report')

Any suggestions on how to proceed with this?

Thank you!

Source.fmp12.zip

April 17, 201411 yr

Well, you need …

• a summary field

• two sub-summary parts, using the source and the year field as break field, respectively

• a script to find and sort the appropriate records – you must sort the found set by the break field(s) to make sub-summaries work!

See attached sample.

Note that summaries in Filemaker are vertically orientated; if you need a a cross-tab report, that's a bit more work …

Source_eosMOD.fmp12.zip

April 18, 201411 yr

Author

Thank you very much for your reply and the modified solution file.

Although it makes more sense to design the report vertically as you suggested, my client wants a horizontal report.

I will search about creating cross tab reports and try to make the report horizontal as per the requirement.

Again, thank you for the reply and to get me started.

April 18, 201411 yr

Author

After searching for some time on cross-tab reports, I found this amazing technique by Comment :

http://fmforums.com/forum/topic/71836-getting-more-out-of-filtered-portals-in-version-11/

It almost gives me the output I need. I am thinking of a way to somehow integrate search on year range in this file.

Cheers!

CrossTab using Comment's technique.fmp12.zip

April 18, 201411 yr

It almost gives me the output I need. I am thinking of a way to somehow integrate search on year range in this file.

comment's approach is fine as far as it goes, but I find the portal filters to be high-maintenance, and the physical setup not so easily extensible depending on the year range.

I suggest you look into an HTML/SQL based combination, which is very flexible and easy to setup (until you get to the details of SQL syntax and HTML styling, that is … :laugh: )