6 Replies Latest reply on Sep 14, 2009 11:36 AM by mrvodka

    Calculation or Summary Problem

    roodbk

      Title

      Calculation or Summary Problem

      Post

      I have a report that I am creating that includes a leading subsummary and a trailing summary.....see example below

       

                                                                Estimated Budget    Approved Budget

      Project Number (Leading Subsummary)                      ?

         10500   Architecture      AE Services

         10625   Architecture      Reimburseables

       Design Services (Trailing Subsummary)           $125,000

         13700  Drywall                Install Walls

         13875  Electrical              Data Raceways

         13875  Electrical              Lighting

        Construction (Trailing Subsummary)               $450,000

       

      My problem is that the Trailing Subsummary numbers are from a calculation that I created that first calculates the prefix of the account number (example 10 for the first set of account number, 13 for the second set and so forth.  If the prefix is "10" then it places a number that is entered from another field on another layout.  Because this is a calculation and not a summary I can't get the the total of the trailing subsummary amounts to add up into a field in the leading subsummary (example:  I need it to calculate the $125,000 and the $450,000 and that number to appear in the leading subsummary for the project number total.  It is only putting the $125,000 and not adding them together.

        • 1. Re: Calculation or Summary Problem
          mrvodka
             I dont quite get what you are trying to do. Doesnt this make more sense with a subsummary part grouped by this calc?
          • 2. Re: Calculation or Summary Problem
            roodbk
              

            Mr. Vodka,

             

            It would make more sense, but the numbers that I am trying to sum up are not part of the data that is listed.  For example the items listed with the account numbers are not part of the data that I am trying to show under the column Estimated Budget.  I have fields set up with estimate numbers for Design Services, Construction, Furniture, Equipment & TIS in another location.  I want to show those numbers on the summary line for each project number.  The committed costs are coming from the listed items with account numbers so I can just use a summary field to add them up and show on the summary line.  When I use the calculated field and place it on the leading subsummary line it only puts the number from the first subsummary below each set it doesn't add them and put the total amount.

            • 3. Re: Calculation or Summary Problem
              mrvodka
                 Could you post a smaple of how your raw data would look and then your expected result.
              • 4. Re: Calculation or Summary Problem
                TKnTexas
                  

                You will need to create a calculation field for this.  There has to be a field for the subsummary.  If you called it GROUP, the it would be left(project_number, 2).

                 

                From the data in your example you would have records with 10 and 13.  Sort by this and however else.  The subsummary (both leading and trailing) would pick up this and subtotal as you want.

                • 5. Re: Calculation or Summary Problem
                  roodbk
                    

                  On another page of the database I have these fields & data:

                   

                  Design Cost: 125,000 | Construction Cost: 450,000 | Owner Systems Cost: 100,000 | Furniture Cost: 150,000 | TIS Cost: 50,000 | Equipment Cost: 75,000 | Contingencies Cost: 80,000

                   

                  I have a field set up (Estimated Budget Summary) which is a calculation field and have placed this field in the trailing subsummary so the amount appears for each category.

                   

                  Case (Account Number Prefix = "10" ;Project Management Module::CBCP Design Cost;Account Number Prefix = "13";Project Management Module::CBCP Construction Cost;Account Number Prefix = "14";Project Management Module::CBCP Owners Systems Cost;Account Number Prefix = "16";Project Management Module::CBCP Furniture Costs;Account Number Prefix = "17";Project Management Module::CBCP TIS Costs;Account Number Prefix = "18";Project Management Module::CBCP Equipment Costs;Account Number Prefix = "19";Project Management Module::CBCP Contingencies Costs;0 )

                   

                  Now I am trying to calculate this subsummary field and have that amount appear in the leading subsummary.  When I do this, it only puts the amount of the first trailing subsummary amount in the field instead of adding them up.

                   

                  I haven't figured out how to attached documents to these postings yet otherwise I would give you the actual sample of the report.

                   

                  • 6. Re: Calculation or Summary Problem
                    mrvodka
                       You can try and email me the file if you wish. If my email is not public, send me a private message, and I will give it to yo.