1 2 Previous Next 15 Replies Latest reply on Nov 26, 2013 9:35 AM by FilipePimentel

    Report/Layout with montlhy totals

    FilipePimentel

      Title

      Report/Layout with montlhy totals

      Post

           Hello there. 

           I am building a accounting db and I have got stuck in a report. I need to produce a report where the values of expenses and revenue for each month are totaled up per month and they keepo it running every month (they just get added up to the report

           i.e: 

           month   Revenue   Expenses  difference

           jan            1000              500           +500

           feb            2000             1000          +1000

           mar          3000               2000       +1000

           apr            4000              6000       -2000

            

           The problem I am finding is that I only have one table for the values - I mean the field amount is on the same table for revenue and expense. I sort everything with at the beginning when I set the field for revenue or expenses transaction

            

           I hope I explained myself well. 

            

           Filipe

        • 1. Re: Report/Layout with montlhy totals
          philmodjunk
               

                    The problem I am finding is that I only have one table for the values - I mean the field amount is on the same table for revenue and expense. I sort everything with at the beginning when I set the field for revenue or expenses transaction

               Sorry, but that does not make sense when I read it. Do you have one FIELD for revenue and one FIELD for Expense?

               If so, a simple summary report that groups your records by month can be set up with summary fields to show the montlhly sub totals and running balance.

               If you use the same field for expenses and revenue, it's still possible, but we'll need to add some calculation fields to separate out the expenses and revenue entries.

          • 2. Re: Report/Layout with montlhy totals
            FilipePimentel

                 Hi, 

                 No I have one field called amount...which is used to enter the values for expenses and revenue. 

                 They way I report it is by sorting the "transaction field" in expenses or revenues. 

                 Hope it makes sense now. 

                 Thanks

            • 3. Re: Report/Layout with montlhy totals
              philmodjunk

                   I read that to mean that a number field, amount, records the dollar figure and a text field, transaction, identifies whether it is "revenue" or "expense". I will also assume that both revenue and expense amounts are entered as positive numbers.
                    

                   Set up these calculation fields:

                   cMonth: YourDateField - Day ( YourDateField ) + 1 //select Date as the result type

                   cExpense: If ( Transaction = "Expense" ; amount )

                   cRevenue: If ( Transaction = "Revenue" ; amount )

                   cBal: cRevenue - cExpense

                   Set up these summary fields:

                   sTotalExpense: Total of cExpense
                   sTotalRevenue: Total of cRevenue
                   sBalance: Total of cBal

                   Now set up a list view layout and remove the body layout part. Replace it with a sub summary layout part "when sorted by cMonth".

                   put cMonth and the three summary fields inside this sub summary layout part. cMonth can be formatted in the Inspector to only show the name of the month.

                   Now perform a find for the records you want on your report and sort them by cMonth to sort them in proper order and grouped by month. Please note that your layout will appear blank if you do not sort your records by cMonth.

              • 4. Re: Report/Layout with montlhy totals
                FilipePimentel

                     Phil, 

                     Genious. thanks for your help. Really appreciated!!! You guys Rock!!!

                      

                     It works like a charm!!

                • 5. Re: Report/Layout with montlhy totals
                  FilipePimentel

                       Hello, 

                       I have another little issue. I need to run a monthly report where it only shows the totals for the previous months. I have looked in the forum, but could not find something that would work for me. 

                       I would like to be able to select the company I am running the report for (I have one Accounting System for 4 companies) and then select the month I would like the report to be run (this would allow the user to re-print or re-save older reports).

                       At the moment, with the script I have, when I run the report, I get all months which have data (I.e: April and May - are sample data). 

                       Thanks for any help

                       Filipe

                  • 6. Re: Report/Layout with montlhy totals
                    philmodjunk

                         You just need to perform a find for the records that you want in your report, then sort them by month (so that the sub summary layout part is visible).

                         One way to do that is to have the user enter or select data in some fields with global storage while in browse mode and then run a script that pulls up your report.

                         See this thread for examples of scripted finds that you may adapt to your purpose: Scripted Find Examples

                    • 7. Re: Report/Layout with montlhy totals
                      FilipePimentel

                           PhilModJunk, 

                           Thanks for all your help up to here. Now today I was trying to add a grand total to calculate the year amount, but could not add a summary calculation to a already summary field (ie: sTotalBalance = Total of sBalance). How can I do that? 

                           Thanks once again

                            

                           F

                      • 8. Re: Report/Layout with montlhy totals
                        philmodjunk

                             Just use the same summary field. If you put the field in a header, footer or grand summary layout part the same field will show the total for all the records in your found set. If you have data from multiple years, add a sub summary layout part "when sorted by" a year field and put the summary field into it.

                             To get a year field, add a calculation field that uses the Year function to extract the year from your date field.

                        • 9. Re: Report/Layout with montlhy totals
                          FilipePimentel

                               Cool. thanks...

                          • 10. Re: Report/Layout with montlhy totals
                            FilipePimentel

                                 Hello Filemaker Gurus, 

                                  

                                 On the same subject of the date, I have entered data on the db, however, I have some data from 2012. I would like to create a report where the user can select the year for the report. At the moment when I run 

                                 The script suggested to me in older posts, I am getting data from 2012 and 2013. I want the user to be able to make a selection for which year to have the report previewed. 

                            Any ideas?

                            Thanks

                            • 11. Re: Report/Layout with montlhy totals
                              philmodjunk

                                   Let's say that you enter or select 2013 in a global field named gYearSelected

                                   Enter Find Mode []
                                   Set field [YourTable::YourDateField ; YourTable::gYearSelected ]
                                   Set Error Capture [on]
                                   Perform Find []

                                   will find all records dated with a 2013 date, provided that YourDateField is of type date and not type text.

                              • 12. Re: Report/Layout with montlhy totals
                                FilipePimentel

                                     The way I am doing is: 

                                     From a layout, the user clicks a button, which will take to another layout where he is asked to  enter which year he wants to run the report for. 

                                     Then there is where I get stuck. 

                                     I have the calculations suggested by Phil above and I am getting the reports correctly:

                                     i.e: 

                                     month   Revenue   Expenses  difference

                                     jan            1000              500           +500

                                     feb            2000             1000          +1000

                                     mar          3000               2000       +1000

                                apr            4000              6000       -2000

                                      

                                     The problem is that December/2012 is also showing. I want the user to be able to run the report for a particular year. As our db is just starting (always have been doing this in excel), is not a major issue as I only get december from 2012, but I want to transfer more past data to this db and i would be great if the user could select the year for the report.

                                     Thanks for all your help

                                     F 

                                • 13. Re: Report/Layout with montlhy totals
                                  philmodjunk

                                       The script that I posted should find only records of a specified year.

                                  • 14. Re: Report/Layout with montlhy totals
                                    FilipePimentel

                                         Phil, 

                                         Sorry, I wrote my post above without seeing that you actually had already posted. But I am still having some issues, and maybe I did not explain myself properly. 

                                         I want to run a report where I get a monthly total showing for each month (you already helped me with that on earlier posts on this same thread). you suggested: 

                                         

                                    Set up these calculation fields:

                                         

                                    cMonth: YourDateField - Day ( YourDateField ) + 1 //select Date as the result type

                                         

                                    cExpense: If ( Transaction = "Expense" ; amount )

                                         

                                    cRevenue: If ( Transaction = "Revenue" ; amount )

                                         

                                    cBal: cRevenue - cExpense

                                         

                                    Set up these summary fields:

                                         

                                    sTotalExpense: Total of cExpense
                                              sTotalRevenue: Total of cRevenue
                                              sBalance: Total of cBal

                                         

                                    Now set up a list view layout and remove the body layout part. Replace it with a sub summary layout part "when sorted by cMonth".

                                         

                                    put cMonth and the three summary fields inside this sub summary layout part. cMonth can be formatted in the Inspector to only show the name of the month.

                                         

                                    Now perform a find for the records you want on your report and sort them by cMonth to sort them in proper order and grouped by month. Please note that your layout will appear blank if you do not sort your records by cMonth.

                                         This was great and did the trick.

                                         Now, I am adding some past data (2012 and eventually 2011). When I run the report I am getting Dec/2012 up october/2013. 

                                         I tried doing adding a global field and entering 2013 e running the script

                                    Enter Find Mode []

                                    Set field [YourTable::cmonth ; YourTable::gYearSelected ]

                                    Set Error Capture [on]

                                    Perform Find []

                                         But it did not work. What Am I doing wrong?

                                         Once again Thanks for your help

                                         F

                                    1 2 Previous Next