1 Reply Latest reply on Mar 3, 2016 11:52 AM by taylorsharpe

    I need help creating reports in an O&G database.... ??


      I am totally new to FileMaker and have a database that I created in MS Access a million years ago. The individual using this database has now switched to a Mac and FileMaker was the only program I could come up with to try and duplicate it in.  I've got the data entry part of it taken care of so its a simple input screen etc. for them. But I'm not having any luck with getting reports set up.  Here is a screenshot of the data entry screen... I understand to do a new layout I think but I've looked at this so long I've confused myself I think.  Any help would be really appreciated.

      I have the columns totaling up in the YTD row and I have the Month Total column totaling up perfect. Just can't wrap my head around getting the reports/layout/script I need of the reports I want to run.


      O&G SCREENSHOT.tiff


      There will be 100+ records to total up.


      1st report - a monthly report listing all properties that would have this listed for each record.

                          It would look like this...


                          "month" 2016 summary


             Header-             Purchaser/Operator     Prop ID     Lease Name     O&G     Taxes     Bonus     Totals


                                         each property listed w/ info below                         0.00          0.00     0.00          0.00





      2nd report by "State" would be a grand total of all the records YTD oil/gas , then taxes, then bonus and then a total of those all added together.

                         I need to be able to do a report for each state that might be needed.


                     2016 Year End By State


                               State     O&G     Taxes     Bonus     Totals


                                    OK     0.00     0.00     0.00          0.00

                                     IL      0.00     0.00     0.00          0.00    



      3rd report - would include grand totals for each State on a separate report


                     2016 Year End Totals - IL

                               STATE          STATE O&G     STATE TAXES     STATE BONUS

                                    IL               0.00                    0.00                         0.00

        • 1. Re: I need help creating reports in an O&G database.... ??

          Is the database normalized.  It looks like you have a bunch of the month fields all in a form view for a single record.  If so, you want to be in list view and each month is its own record and you just find each year and have the totals in the summaries. 


          In Access, you tend to think of reports.  FileMaker is very different.  Each layout is live data (if you change it, it is changed in the database without running a process).  The layout determines the context and not a SQL script. 


          So for this report, you would have a list view where you searched for the property and the year and sorted by month and have totals at the bottom.  There would be a single Body in the layout for Month, Oil/Gas, Taxes, Bonuses and Month Total.  Each field would exist once from the table occurrence that the layout is based on.  You will have to have a relationship in the Relationship Graph between the Property and the Monthly data.