7 Replies Latest reply on Sep 29, 2009 8:40 AM by comment_1

    Creating a calculation for totals

    Nicole82

      Title

      Creating a calculation for totals

      Post

      <!-- [if gte mso 9]><xml> <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:PunctuationKerning/>   <w:ValidateAgainstSchemas/>   <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>   <w:IgnoreMixedContent>false</w:IgnoreMixedContent>   <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>    <w:DontGrowAutofit/>   </w:Compatibility>   <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif] --><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif] --><!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:"";      margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:"Times New Roman";      mso-fareast-font-family:"Times New Roman";} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;} --><!-- [if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable      {mso-style-name:"Table Normal";      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:"";      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin:0in;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:"Times New Roman";      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} </style> <![endif] -->

      I have a table of events and their income and expenses.  Each line shows only one amount (like a checkbook register) so there are multiples per event and I would like to create a table with one line per event with one column showing the total income and the next column showing the total expense, with the final column showing the grand total profit or loss.  How do I go about creating a calculation for this?   

        • 1. Re: Creating a calculation for totals
          davidanders
            

          EventID

          EventExpense  Number ($$)

          EventExpenseSum  Summary  Field

          EventIncome Number ($$)

          EventIncomeSum  Summary  Field

          EventTotal  ($$)

          EventTotalSum   Summary  Field 

          Summary fields show in Preview Mode when Records are sorted by EventID 

          When placed in SubSummary Part  

          • 2. Re: Creating a calculation for totals
            Nicole82
               Sorry, I should have also mentioned I am new to the program.  I'm not really familiar with creating calculations or doing totals... If I have a field called Income and I put IncomeSum in as a number field will it automatically generate a sum or is there a calculation that I need to add there?  Thanks for your help!!
            • 3. Re: Creating a calculation for totals
              davidanders
                

              When you create a field it can be

              Text (text, numbers and punctuation)

              Number (numbers only)

              Date  (date only)

              Time (time only)

              TimeStamp (date and time)

              Container (file, image or animation)

              Calculation (calculated data)

              Summary (summary of a field for use in a Summary Part) 

              • 4. Re: Creating a calculation for totals
                comment_1
                  

                Nicole82 wrote:
                <!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:PunctuationKerning/&gt;   &lt;w:ValidateAgainstSchemas/&gt;   &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt;   &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt;   &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt;   &lt;w:Compatibility&gt;    &lt;w:BreakWrappedTables/&gt;    &lt;w:SnapToGridInCell/&gt;    &lt;w:WrapTextWithPunct/&gt;    &lt;w:UseAsianBreakRules/&gt;    &lt;w:DontGrowAutofit/&gt;   &lt;/w:Compatibility&gt;   &lt;w:BrowserLevel&gt;MicrosoftInternetExplorer4&lt;/w:BrowserLevel&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:LatentStyles DefLockedState=&quot;false&quot; LatentStyleCount=&quot;156&quot;&gt; &lt;/w:LatentStyles&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 10]&gt; &lt;style&gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&quot;Table Normal&quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&quot;&quot;;      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin:0in;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&quot;Times New Roman&quot;;      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} &lt;/style&gt; &lt;![endif]  -->

                Each line shows only one amount (like a checkbook register)


                Do you have separate fields for income and expense? Or are they all in the same column, with expenses being negative amounts?


                • 5. Re: Creating a calculation for totals
                  Nicole82
                     Yes, I do have seperate fields for income and expense so the chart I have now has columns for Event, Income, Expense.  I want my next chart to have the same information but to give me a grand total of Income along with a grand total of Expense, all on the same line. 
                  • 6. Re: Creating a calculation for totals
                    Nicole82
                      

                    I think I might be running into a problem when I'm selecting options for the summary field? I just tried doing an expense summary field so I selected: Total of expense, when sorted by category, running total, restart summary for each group...

                     

                    I've tried changing it a few different ways but keep ending up with multiple lines for each event.  I'm using FM9, by the way.  Thanks for your help with this! I've been stuck on it for days.  

                    • 7. Re: Creating a calculation for totals
                      comment_1
                        

                      Define two summary fields: a total of Income, and a total of Expense. Do not select any other option.

                       

                      Create a new layout for the report (based on the same table). Add a sub-summary part (when sorted by Event) to the layout. Place the Event field and the two summary fields in this part, and delete the body part.

                       

                      Tp produce the report, go to the report layout, find the records you want to report on and sort them by Event - then enter Preview mode (all this can be scripted).