4 Replies Latest reply on Nov 8, 2012 7:21 AM by philmodjunk

    Calculations on Reports



      Calculations on Reports


           So, I'm trying to create a report (layout) that has calculations on it.  For example.  I have 2 fields VAC & LAC and I want to multiply them together.  This is the value I want to appear on my report (called Power).  Is the only way to do this is to add a field to the table that does this calculation?  Can I just do a on the fly calculation on the report?

           Then I want to summarize Power.  By month, year, all data.  Do I need to add summary fields to the table too? 

           General questions (are calculated fields stored in the database or are they calculated on the fly?)  It seems strange to me to have all these fields in the table just for reports.  If I have to add summary fields to the table for every report I need to make, my table is going to have twice as many fields, it seems wrong.

           Am I missing something?

           I appreciate your guidance and help.



        • 1. Re: Calculations on Reports

               If you just wanted to display a power calc for each record, there is an alternate approach using Filemaker 11 or 12 that can be used, but since you need summary values, you will need to define calculation fields. Get used to it, it's a common requirement in FileMaker Design that is different from how you would do this in other systems.

               Calculation fields can be stored or unstored. Unstored calculate "on the fly". You can select this option by clicking the Storage Options button in the Specify Calculation dialog. If you can't find this button you are creating an auto-entered calc in a data field and this cannot evaluate on the fly, it's always stored.

               It may seem wrong, but it's how FileMaker works. While it can add a lot of fields to your table, it also allows you to "standardize" the calculation and any updates to it need be done only once in the field definition rather than opening what could be several different forms/reports and/or SQL queries and updating each of them identically. Thus, there is at least this small possible "plus" to go with the "minus" of adding those extra fields.

               (For me, it was a shock the first time I created an MS Access database and found that I couldn't define ANY calculation fields in the tables...) wink

          • 2. Re: Calculations on Reports

                 There is an option under storage options that you can select that states do not store - recalculate as needed.  Yes you would need a summary field if you are totaling/counting  by month and year.    No there is nothing wrong,  it just organizes your fields where everything is located in one place, your databases. No i don't thing your missing anything.

            • 3. Re: Calculations on Reports

                   OK, its good to know that I was on the right track and I wasn't missing something big.


                   My question now is how to get it to group by month, year, etc.  I have a date field (right now I have it grouping by date and that was easy).  But, when grouping there doesn't seem to be an option to group on calculations.  So, do I have to add a field to my table for each of those variables (ie calculate a month/year and year field).  It just seems so clunky....sure I will adjust.




              • 4. Re: Calculations on Reports

                     A nicer alternative to a separate month and a year field is to use this calculation field defined to return a date:

                     DateField - Day ( DateField ) + 1

                     For any date in DateField, this calculation returns the date of the first day of the month. That gives you a common value you can use for grouping and it will naturally sort into chronological order as well.