AnsweredAssumed Answered

Monthly cross tab report - can't get grand summary to work

Question asked by MichaelWorden on Dec 9, 2011
Latest reply on Dec 9, 2011 by philmodjunk


Monthly cross tab report - can't get grand summary to work


I'm newish to Filemaker, using FMPro 11 on a Mac, and I'm having a very difficult time getting a monthly cross tabulated report to work (it seems that this is something that would be very common to want to do.)

I have a resource for which I am tracking usage.  There are multiple users and each user can have multiple projects.  Users book the resource for a particular project on various dates and bookings can be of variable duration.  I need to be able to produce a report that looks something like this:

Montly Usage by Project for FY12

Bob Jones              Jul  Aug  Sep  Nov  Dec  Jan  Feb  Mar  Apr  May  Jun    Total

  Jones Project A        2             3                         4                                  9

  Jones Project B        1                                                                           1

Jane Smith

  Smith Project A        4                                       2                                  6

  Smith Project B        3                                                                           3

Totals by Month         10           3                          6                                 19

(I'd also like to be able to graph the Totals by Month as a line graph.  That might be another question but any insight welcome.)

I have these Tables:

Users  (tracks info about each user)

  User ID (primary key)


Projects  (tracks info about each project)

  Project ID (Primary key)

  User ID (foriegn key)


Usage  (tracks info about each booking including the date and number of hours)

  Usage ID (Primary key)

  Project ID (foriegn key)



  sTotal Hours:  (Summary = total of Hours)

  cMonth  (calculation = Month(Date))

I've been able to create a cross tab report using Projects as the reference table for the layout.  No layout body.  Sub-summary when sorted by User::Name. Sub-summary when sorted by Project::Title.  Under each month there is a portal to Usage filtered like this: usage::cMonth = 1 (for January, 2 for February, etc).  In each portal is a single field for usage::sTotal Hours.  The total at the end of each row is just usage::sTotal Hours without a portal.  This all seems to work fine (though it took me a while to figure out).  I can't get the last part to work.  I put a Trailing Grand-summary at the end and put in matching filtered portals for each month but it won't give me summary totals across all users and projects for each month.  Instead, I just get totals for whatever the last project is that is listed above.  I would be really grateful for any pointers here as I seem to be totally stuck.