3 Replies Latest reply on Dec 9, 2011 12:45 PM by philmodjunk

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

    MichaelWorden

      Title

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

      Post

      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)

        Name

      Projects  (tracks info about each project)

        Project ID (Primary key)

        User ID (foriegn key)

        Title

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

        Usage ID (Primary key)

        Project ID (foriegn key)

        Date

        Hours

        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.

      Thanks!

        • 1. Re: Monthly cross tab report - can't get grand summary to work
          philmodjunk

          You'll need a portal with a relationship/filter setup that matches only by month so that a summary field in the portal gives you the grand total for the month.

          You may also wan to rethink your cMonth based relationship a bit. What happens to your totals when you start recording data for the same month as data already recorded but for the next calendar year? You can update either the portal relationship or filter to take the year into account for that.

          • 2. Re: Monthly cross tab report - can't get grand summary to work
            MichaelWorden

            "You'll need a portal with a relationship/filter setup that matches only by month so that a summary field in the portal gives you the grand total for the month."

            I thought that's exactly what I have.  In the grand summary I have a single portal under each month (just like in the sub-summary part).  The portal is to the Usage table and the filter is usage::cMonth = 1 (or 2, 3, etc, depending on the month).  There is a single field in the portal which is usage::sTotal Hours.  This works perfectly in the sub summary part but doesn't work in the grand summary.  In fact, in the grand summary it is working exactly like the last row in the sub-summary: it is giving me the sub-summary data from the last listed record.  If I don't use the month filter for the portal then I get the grand total for the last listed user/project only (not across all users and projects, which is what I want). I can't figure out what I'm missing.  I suppose I might be able to define a new field called unity that has the same value (1) for every record and add a new sub-summary field that sorted by that value but that seems like a kludge.

            With regard to your last point, my plan was to use a script that would limit the found set to the date range for a particular Fiscal Year (note that my FY starts in July and goes to June).  This way I could also use the same layout if I was interested in analyzing usage by month agregated across multiple years by simple specifying a broader found set.  Does this make sense or am I missing some potential pitfall?  

            Thanks again for your advice.

            • 3. Re: Monthly cross tab report - can't get grand summary to work
              philmodjunk

              I thought that's exactly what I have.  In the grand summary I have a single portal under each month (just like in the sub-summary part).  The portal is to the Usage table and the filter is usage::cMonth = 1 (or 2, 3, etc, depending on the month). 

              Yes, but what is the relationship you have for this portal? Doesn't it match by ProjectID? You need a relationship different from that used for the sub summary portals: Something like this:

              Projects::anyField X Usage::AnyField

              and then filter it by month.

              Does this make sense or am I missing some potential pitfall? 

              There's a pitfall. As I understand it, your layout is based on projects and the portals refer to usage. Your find will limit the project records, but makes no limit on the usage records. They are only limited by the relationship and your portal filter. If no project ever exceeds 12 months in time, you are fine, but on the 13th month a portal will combine data from the same month, but from two years. If you include the year in your portal filter expressions, you'll keep this from happening.