3 Replies Latest reply on Nov 30, 2011 8:29 AM by philmodjunk

    Group Line Items by Month?



      Group Line Items by Month?


      (Warning! I am using FMP10 without portal filtering!)

      I have projects with line items. Each line item has a date and a dollar value. I want to see the top five projects with the most earnings by month, ideally through a portal.

      For example, Project X has the following line items:
      ItemA 10.22.2011 $500
      ItemB 10.23.2011 $250
      ItemC 11.22.2011 $425

      If I choose "October" in my report layout, the portal displays:
      Project X $750

      If I choose "November", it displays:
      Project X $425

      I can set up the relationships and basic calculations, but this seems to require multiple summary fields. Will that work in a portal in a standard layout?

      If I were to do this just through a report view, how can I limit the number of projects to the top five, and what calculations and summaries would I need to create?

      This seems like a common report. I am just hazy on how summary fields work. Thanks for your help!

        • 1. Re: Group Line Items by Month?

          If your report is to list multiple projects with multiple items in your portal, it's better to use a summary report based on the portal's table. If, as I think is the case here, you just want to list a single total of items from the LineItems table for each project for a specified month and year, then you can use a relationship, but don't need the portal as you are listing only one item. Or do you want a breakdown of several monthly totals for each project?

          Let's assume a single, specified monthly total to get started.

          your 2nd field, 10.22.2011, (I'll call it Itemdate here), in your lineitems table should be a field of type date, not text for this to work.

          Define a calculation field, cMonth as:

          ItemDate - day ( ItemDate ) + 1

          Specify Date as the return type. It computes the date for the first day of the month for each date. Using your example data it would return:


          Now you can add this new relationship that matches by ProjectID and a specified month:

          Projects::ProjectID = MonthlyLineItems::ProjectID AND
          Projects::gSelectedMonth = MonthlyLineItems::cMonth

          gSelecteMonth is a field of type date with global storage specified in field options. I'd use a similar expression to that shown above in an auto-enter calculation so that any date selected/entered in this field is converted into a date for the first day of the same month. That way it matches correctly to the specified records of the same month in the line items table.

          MonthlyLineItems is a new occurrence of your LineItems table that you create with the duplicate button (two green plus signs) in Manage | database | relationships. (You can doubleclick the new occurrence box to open dialog where you can rename it like I have done here.)

          Define a calculation field, cMonthlyTotal in projects as:

          Sum ( MonthlyLineItems::DollarField )

          and place it on your report layout.

          I want to see the top five projects with the most earnings

          If you show all records and then sort them in descending order by the cMonthlyTotal field, you can then use:

          Go To Record/Request/Page [6]
          Omit Multiple Records [no dialog ; Get ( FoundCount ) - 5)

          To drop out all but the top 5 earning projects for the specified month.

          • 2. Re: Group Line Items by Month?

            Thank you!

            I never thought to work inside the Projects table like this: I was struggling, trying to use my "Reports" table. Plus I was able to use portals by just making a self-join of projects. Duh.

            • 3. Re: Group Line Items by Month?

              Please note that I did not mention using a portal in this solution. While that works, it's not necessary if you only want to list one monthly total for each project. You can add the summary field directly to the layout without using the portal.

              It is possible to pull together the same report on a line items based layout with summary fields, sub summary parts and a sort order that specifies "re-order based on summary field". It's just that the approach makes showing "only the top 5" a lot trickier to do as you can't just sort and then omit all records after the 5th one as you may have any number of records used to produce your montly totals. Instead, you'd have to loop through your found set until you reach a line item record linked to the 6th different project record and then do an omit multiple records from there...