3 Replies Latest reply on Jan 14, 2011 3:49 PM by philmodjunk

    complex time related reports with subsummaries and groups



      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?


        • 1. Re: complex time related reports with subsummaries and groups

          This is called a "cross tab" report and can take a bit of work to set up in FileMaker....

          I'm assuming you have FileMaker 11. If you don't, we'll need to change this approach slightly.
          Can your imported data fit this record design?


          Whether amount is the total for the month or an individual item sold, this data can be made to work for the report you specify. DateSold needs to be a date that falls somewhere within the month. If the imported tate is imported as text, you'll need to set up a calculaition field that uses the imported date text to compute an actual date.

          We'll add calculation field, cMonth, that will convert the date in DateSold to be a date for the first day of the same month:
          DateSold - Day ( DateSold ) + 1

          Define a global number field, gYear for recording the current year.

          Define 12 calculation fields for fields named, cJanuary.... cDecember

          The calculation for cJanuary would be: Date ( 1 ; 1 ; gYear )

          Define a summary field, sTotalAmt as the total of Amount. This will be used for at least the grand total in the 13th column.

          Now we need a relationship we can use with 13 portals to get our monthly and annual totals.

          POSData::ArtistID = POSData 2::ArtistID AND
          POSData::ItemCategory = POSData 2::ItemCategory

          Now create a list view layout with a "when sorted by ItemCategory, Print Above" sub summary in which to place the POSData::ItemCategory field to form the "Jewelry", "Fiber" sub headings in your report.

          Change the body layout part into a "when sorted by Artists::ArtistID" sub summary part.

          Now place 12 portals to POSData 2 on your layout to display the 12 monthly totals: For the first portal, Use this portal filter expression: POSData::cJanuary = POSData 2::cMonth. Place POSData 2::sTotalAmount as the lone field inside each of these portals.

          For the 13th portal, make it to POSData 2, but use POSData::gYear = Year ( cMonth )

          Now, import your data and sort it by ItemCategory, ArtistID to get your report. You can link this table to an Artists table by ArtistID in order to display a name or other data about the artist in each row of the report.

          • 2. Re: complex time related reports with subsummaries and groups

            Wow, thanks Phil for this detailed answer. I'm afraid I'm used to FM Pro 9 although I've just downloaded 11. I'm not really familiar with working with portals so I'm not following how the portal filter can "choose"/separate out the different months. I can import the data with the design you suggested, and I follow converting the date sold into the format described. Is there another way to create the report without using portals?

            Thanks so much, Elaine

            • 3. Re: complex time related reports with subsummaries and groups

              Without the portals, you have to build the same logic into at least 13 different relationships, one for each month and the 13th for the grand total. Using the portal filters is much simpler to set up.

              Here's the relationship for the January column:

              POSData::ArtistID = POSData 2::ArtistID AND
              POSData::ItemCategory = POSData 2::ItemCategory AND
              POSData::cJanuary = POSData 2::cMonth

              cJanuary would be a calculation field: Date ( 1 ; 1 ; Year ( DateSold ) )

              You'd need 12 more. Each relationship requires a new table occurrence and an additional calculation field to provide the date key.