Research "Cross Tab" Reports
These are not the simplest thing to set up in FileMaker and the end result isn't as flexible as I'd like--the number of columns will be fixed, but they can be done in FileMaker.
Much depends on how the data that you want to use to create the report is structured in your tables.
Thanks. That definitely looks like the direction I'll be going.
The fact that you can get a summary report with the sub totals that you want is a pretty good indicator that your data is properly structured for a cross tab report.
1 of 1 people found this helpful
'cross tab' this forum:
Then there is the Virtual List...
(research that too, here and elsewhere)
'pivot table' is another phrase to search here and elsewhere
Thanks. Also very helpful.
Thanks for pointing me in the right direction. I now have a serviceable report with variances with the previous year. But I'm a little stuck on the charting aspect.
To create the report, I couldn't simply work with a found set. Rather, I scripted to prompt the user for entry of a year, which is then stored in a global field and used in calculations to return totals for current and previous years. It works great for the main report. But when I attempt to include a chart at the bottom of the report, I can't get the category breakdowns (the blue lines in the main report) into the chart.
If I point at the data for the summary field, the chart returns the correct total -- but only with one bar and not breakdown. If I point to a calculation with a GetSummary by category, the chart returns only 1 category total. Can you offer any direction on this? Thanks for any help. Pictures of all the basic report and chart below:
The problems you report are precisely why I described using a found set as the basis for the chart. You can't chart groups from related data.
An alternative would be to set up executeSQL to generate delimited data, using GROUP BY to get your subtotals.
Sent from my iPhone