1 Reply Latest reply on Jan 22, 2014 2:20 PM by philmodjunk

    Aggregated totals in multi column report / table

    andrewwoodward

      Title

      Aggregated totals in multi column report / table

      Post

           Hi all, I have a basic transaction and expense category reporting database that records transactions straight from bank and credit card statements.  I need to produce a number of summary reports each with a common denominator of having multiple (time-based) columns representing summaries of the transactional data by category.  An example might be monthly totals across the page, or quarterly, or annual or even 'this year last year'.  

           Can someone please help me understand if:

           1. I need more sophisticated data modelling (only use two tables at the moment, being transactions and category)

           2. the approach I would take with the Filemaker functions etc.

           Just looking for a thread / approach I can nut through.  Thanks in advance.

            

        • 1. Re: Aggregated totals in multi column report / table
          philmodjunk

               Sounds like you are wanting to set up what is commonly known as a "cross tab" report--where data from multiple records is used to produce data organized in rows AND columns.

               If I read this right, you have  a left hand column of months of the year and columns for each expense category.

               Such can be set up with a list view layout where the category totals are organized into totals using one row portals. In many cases, this would use filtered portals with a different filter for each portal to limit the totals shown to a specific month/year and category.

               I will assume that your basic relationships look like this:

               Transactions>----Categories

               Transactions::Category = Categories::Category

               If so, you can set up a new list view layout based on Transactions where you replace the Body layout part with a sub summary part "when sorted by Transactions::cMonth.

               cMonth is a calculation field that returns "Date" as the result type: TransactionDate - Day ( TransactionDate ) + 1

               This calculation returns the date of the first day of the month for all dates in a given month so we can use it to group records by month/year by sorting and the groups will fall in chronogical order.

               This will give you a single row for each month where you have recorded at least one transaction.

               Put cMonth with a custom date format such as Jan 2014 as column 1. Then add a portal to a new table occurrence of Transactions defined like this:

               Transactions::cMonth = Transactions|SameMonth::cMonth

               For this portal to Transactions|SameMonth, specify this portal filter expression:

               GetValue ( ValueListItems ( Get ( FileName ) ; "YourValueListofCategoriesGoesHereInQuotes" ) ; 1 ) = Transactions|SameMonth::Category

               Make this a single row portal and put a summary field defined in transactions in the portal row to compute the total transaction amount for the first category listed in your value list.

               Now enter browse mode and sort records by cMonth. Check to see if this first portal is working to produce the correct sub total from your data.

               Then return to browse mode and make copies of this portal. The only thing you need change is the "1" in the above portal filter so that you use GetValue to extract the category for value list item 2, 3, 4 and so on for each value in your value list.

               This portal filter expression, BTW, is designed so that if you modify the categories in your value list, you do not have to update the portal filter expressions to filter for the new category.

               To produce a report, you can perform a find for a given year, or range of months, or range of years and then sort the resulting found set by cMonth.