This is going to be tough to describe, never mind solve but please bear with me.

We have roughly 100 departments in our university, most of which submit funding proposals every year. Each proposal is designated one of ten category types. I have a database which includes the department name, proposal date, proposal type and proposal amount. I need to produce a report which sums the proposal amounts for every category by department, as well as the total for each department, and to list the results side-by-side for each year from 2008 going forward.

Using a report layout it's very simple to get the results for a single year at a time by searching from 1/1 ... 12/31 and running subsummaries sorted by department and proposal type. My problem is how can I show and compare the results for every year in the same layout? I can print out the results for a year at a time and just hold the printouts next to each other with the departments aligned but that's hardly elegant. Also, not every department submits a proposal every year, and not every proposal category is present for each department so even printing out yearly reports and sticking them side-by-side doesn't give a perfect alignment.

Is there any way to show the total amount for each category by department, by year, with everything on the same layout, short of writing individual calculation fields for 100 departments x 10 categories x 5 years = 5,000 fields?

Any hints or pointers greatly appreciated.

Colin Hunter

You need a cross tab report. There is quite some effort in doing this.

You might also look at the virtual list technique, the two often go well together.

See also:

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

Thank you for the replies and information which hopefully should be sufficient to get me on the right track. At the very least I now know the correct name for what I'm trying to create is a cross-tab report.