4 Replies Latest reply on Oct 15, 2012 2:42 PM by SumeeSandy

    Incorrect Sub Totals

    SumeeSandy

      Title

      Incorrect Sub Totals

      Post

           Hi All,

           I am relatively new to FM and am using FM Pro 12. What i am trying is to generate a report that has a body, as well as 3 levels of sub summary. Essentially, my data relates 2 tables: Projects and Revenue. Projects is the master table and has alist of projects with attributes like Industry, Practice, Sub-Practice etc. For each project, the Revenue table stores monthly revenue, which is for multiple years.

           On my report, i would like to show details like ProjectId, Name, User, Jan Rev, Feb Rev, March Rev and so on, for the 12 months and also a Total and Quarterly Totals. 

           Then, i need to subtotal the data by Industry, Practice and Sub-Practice. For this, i created 3 sub-summaries (leading and trailing) for each of those fields. I am also sorting the report by these fields. In each of the Sub Summary section, i copied the fields like "Jan Rev", "Feb Rev" etc.

           Issue: My details shows the desired result, but the sub summary totals are not correct. The sub-totals for each trailing sub-summary does not add up the data for that group and instead shows the last value from the body.

           So, for 

           Industry A

              Practice P

                  Sub Practice S

           Details:                    Proj1      Some Project     User 1   10     20    30

            

           Details:                    Proj2      Some Project     User 1   5     10    20

                Sub Total Practice S                                                     5       10    20    (Instead of 15, 30, 40)
            
           All subsequent subtotals and grand totals also only shows the last occurance of the groups detail records from the body section.
            
           Hope i stated my issue clearly. If not, happy to clarify. Thanks so much for any help!
            
           Sandy.

        • 1. Re: Incorrect Sub Totals
          philmodjunk

               Describe the set up of the Sub total layout part where you are getting the incorrect values. What kind of fields are producing the 5, 10 , 20 instead of 15, 30, 40 values? Are they summary fields?

          • 2. Re: Incorrect Sub Totals
            SumeeSandy

                 Hi Phil - Thanks for your response.

                 The fields i have in my body section are calculated fields. For e.g. For "Jan Revenue", the calcualtion is something like (If Month = "Jan";RevenueAmount;0). Where Month = Month name as stored in my Revenue Table and RevenueAmount is the revenue amount stored per project, month and year.

                 At first, i copied this same field into the Sub Summary regions and it did not work. Next, i went back into the DB Setup and created Summary Fields that Total the amounts. So, in the above case, i created a new summary field called "Jan Revenue Sum", which is a total of the calculated field "Jan Revenue". I then copied this field into the sub summary, but the result is the same and where i get the 5,10,20 instead of 15,30,40

                 Thanks,

                 Sandy.

            • 3. Re: Incorrect Sub Totals
              philmodjunk

                   Sounds like an issue that has been reported, but not yet confirmed as a bug: Inconsistent Sub- and Grand Summary Behavior when re-sorting

              • 4. Re: Incorrect Sub Totals
                SumeeSandy

                     Awesome! :) My first week with FM and i run into this...

                     I went through that post, so it would seem that at some point, my totals should at least work i.e. if i do not re-sort when displaying or if i use a particular sort order. I will play around with the sorting order or something to see which works. Besides that, Is there another approach i can try? 

                     Thanks again for looking into this.

                     Sandy.