3 Replies Latest reply on May 25, 2009 5:09 AM by LaRetta_1

    FMP reporting

    eibcga1

      Title

      FMP reporting

      Post

      Hello all,

       

      I'm new to FMP and a long time Excel user.

       

      I maintain a DB to keep track of my finances in a table called Ledger, with fields for date, name, memo, account, amount, and calculation fields "total of amount" and "total of amount running with restart".  There are now over 5,000 records which I imported from accounting software which is replaced by FMP.

       

      I also have a linked table called Accounts with fields for account name, type, account number, etc.  I have Account Name in the Accounts table linked to the Account field in the Ledger table.  I use Value Lists when doing data entry for the account name, and the name of a customer or supplier.  All is well.

       

      Now I want to do summary reports from FMP, instead of having to export the records to Excel and run a Pivot Table.  All I need is a simple list of all accounts with the total of amount for each.  I find the New Layout / Report feature a bit confusing and never seem to get to get this simple report.

       

      Any guidance would be appreciated.

       

      Thanks for a great product! 

        • 1. Re: FMP reporting
          LaRetta_1
            

          Create a new columnar list/report.  (next>)

          Constrain to page width

          Report with grouped data (include subtotals and grand totals) (next>) ... notice how it draws the example report to the right.

           

          Fields to put on report:  Date, Account, Amount (next>)

          Organize by Account  (next>) ... see how the report groups the data by symbolic acount for you.

          Sort by account (as FM indicates it needs to). (next>)  This is important; if it does not sort by account, it won't work.

           

          Specify subtotals (click 'specify' and (from pop-up select Manage database and create a SUMMARY field called sTotal (type summary).

          Specify sTotal as 'total of Amount' and say OK (and then OK to exit Manage Database return to your report creator).

          Select sTotal and say OK

          Back at the Specify Subtotals dialog, you want it categorized by Account below record group (as it defaults).

          Now click 'Add Subtutal' then (next>)

          ...Do the same to specify grand totals if you wish.

          ... select a Theme and Headers as you wish.

          Let FM create a script for you because it must be sorted properly and enter Preview mode.

           

          The result will be a report which groups and totals by account number.  To restrict your report to your current fiscal, you may need to include performing a find (by Date date range) at the beginning of the report. 

           

          Also, if you don't want the 5,000-record detail and only want the total by account number then you can now actually DELETE the body of the report.  Take your sTotal field & move it up into the leading part next to your account number and also delete the bottom part where FM default-places the Account and sTotal field in the lower part.  You will end up with a report showing only your Account number and total.

           

          • 2. Re: FMP reporting
            eibcga1
              

            Thank you very much!  I have learned a lot from this exercise and have been experimenting with it.  I'm happy you also mentioned how I could have the report show current fiscal (or any date range) information by performing a Find (in the Ledger Layout, table view, then switch to the Report layout we just added, then run the script saved script.

             

            After I have created this new Layout, if I want to go back and change some criteria, can I go back, or must I create a new Layout? 

             

            Thanks very much for your time. 

             

             

            • 3. Re: FMP reporting
              LaRetta_1
                

              You can change the criteria of an existing report.  But until you are truly comfortable, I'd create a new one (or duplicate the existing one and change IT) - you can always delete what you end up not using and it is nice to compare if you don't understand why one produces or displays as it does (in comparison to another).  If you duplicate a report, remember to duplicate your script and change your new script to switch to the new layout name.  Creating NEW reports has the added benefit of the Report Assistant showing you what your results will look like - and allowing you to create/add summaries and it will create your scripts so you learn how they need to be handled.

               

              But to change criteria, select Layout > Part Setup.  Perhaps you want a second sub-summary totaling records by month/year (within each account number).  Create a calculation (result is date) with: yourDateField - Day ( yourDateField ) + 1.  This creates a date calculation which makes every account date the first of its month.  Now you want Account 215 (leave the sTotal on this part).  Add a new sub-summary when sorted by this date calc (print above).  Notice the Part Setup list shows you how your report will organize (and you can change the order of those sub-summaries by dragging them up or down). Be sure you change your sort in your script if you add a second sub-summary on date (or if you change the sub-summary orders).  Copy your sTotal field and place it in this new sub-summary.   Now your Accounts will total AND they will also total by each month/year within the account (using the SAME summary calculation).

               

              Hint: This is poorly documented but, if all you see are little grey boxes with dots in them when you view left edge of your report in layout mode (you can tell the top two are title and header but you can't read the sub-summary parts), at the bottom of your layout window (where the zoom 100 is) just to the left of the word 'layout', click that little tab thing.  It will turn those grey boxes into meaningful words which will tell you which part is which (displaying horizontally instead of the dumb vertical).  Use your Fm Help for more details (search 'sub-summary' and 'reports').