complex time related reports with subsummaries and groups
I'm wanting to create a sales report for a gallery which has several layers of complexity. I have invoices dated throughout 2010 of artists, each artist has different items for sale. The tables in my FM Pro are invoices, artists, and inventory, and have key fields that relate records between the tables. I can import data that summarizes the total amount of each item per month. Each item is coded for a particular medium: jewelry, fiber, etc. How can I create a report that is grouped by media and artist, has summary totals by month, for each artist and is sorted by grand annual total for the artist? An artist may have items all in one media or in more than one. Please see screen shot below for the report I'm aiming to get:
so each line is a subsummary of an artist, with corresponding items in that category grouped together. Should I import the total sale of an item in a month into a field "Jan sale", "Feb sale" etc. then create a summary field of Jan sales, all Feb sales, etc.? So a record of each item would have the field "Jan sales", "Feb sales", etc.? What kind of a field would the annual total for each artist be? A calculation of summary fields? What function(s) are best to use for the different totals?