Display amount grouped by year

[Melborne]

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

[eos]

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

[Melborne]

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.

[Melborne]

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

[eos]

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 …  )