2 Replies Latest reply on Aug 21, 2012 10:16 AM by jerryclinebell

    Help with consolidated monthly income and expense report

    jerryclinebell

      Title

      Help with consolidated monthly income and expense report

      Post

      We manage properties as short term vacation rentals and report to our owners monthly for expenses incured and revenue generated. I have no problem generating monthly expense and revenue reports based upon the respective tables however I can't figure out how to create a report that shows consolidated monthly revenue and expenses. My preference is to be able to generate this monthly profit/loss report in FMP instead of exporting the records into Excel.

      The Properties table has a one to many relationship to the Expenses table AND a one to many relationship to the Reservations table. (One property has many expenses and One property has many reservations). The foreign key in both the expense and reservations table is the _kf_PropertyID. Both the expense and reservations tables have fields that calculate the month number, the month name, and the year for each record from the expense date and reservation date fields, respectively, for sorting into year and month in subsummary reports. Fields for total expenses and total reservation revenue are also in the repsective tables.

      Properties:_kp_PropertiesID = Expenses: _kf_PropertiesID

      Properties:_kp_PropertiesID = Reservations: _kf_PropertiesID

       

      I suspect the answer is a simple one and any assistance is greatly appreciated. I use FMP12 Advanced.

        • 1. Re: Help with consolidated monthly income and expense report
          philmodjunk

          This one isn't all that simple due to your table/relationship structure.

          Expenses>----Properties------<Reservations

          If Expenses and Reservations were records in the same table(and that would have it's own set of design challenges), I'd tell you to base your report layout on that table and pull data from teh Properties table into sub summary layout parts. The resulting report is very flexible.

          But that doesn't work given the above relationships.

          Base your layout on either Expenses or Reservations. I'd use whichever table usually has the most records for a given month.

          In a grand summary layout part, add a Portal to the other child table. Size the portal to have more rows than you ever expect to need for an entire month. Set this portal to slide up and resize the enclosing part. Use global fields and a portal filter to filter the portal to only the month and year specified in your global fields or use the global fields as additional match fields in the relationship used for your portal--this may be necessary depending on the calculations you need to perform for your report.

          An alternative to the portal, is to define a report table with fields that correspond to the needed fields in reservations and expenses. Then you import the designated month's records from each into this unified table. This can be simple for some data models and nearly impossible for others. A list view layout based on this report table then offers formatting options difficult to do with a portal.

          • 2. Re: Help with consolidated monthly income and expense report
            jerryclinebell

            Thank you Phil for your prompt reply and suggested solution. I will work with the grand summary portal option as it seems the simplier solution.