10 Replies Latest reply on Aug 21, 2016 3:57 PM by sccardais

    Grand Total of Summary Fields in filtered portals

    sccardais

      I want to show the total of the Orders Received and Orders Canceled columns at the bottom of each column (outlined in red on attached screenshot). Adding the Summary fields in the portals to a Trailing Grand Summary part does not work. It shows the value of the last records rather than a total of the columns. I've run into this problem before but cannot remember how to fix it.

       

      More Info if Needed

       

      The layout is based on table: PRODUCTS - one record per Product. Global fields in PRODUCTS hold start and stop date ranges used to create relationships to another table: ORGs. These global date values are set by the User in the row near the top labeled, "Group by Date Ordered."

       

      Each row in the body is a record in PRODUCTS. The first column "Overall" shows summary data by Product in an un-filtered portal.Orders Received is a summary field (ORGs::s_CountOrders that counts non-blank values in a Date Ordered field. Orders Canceled is the same - it counts non-blank values in ORGS::DateCanceled. The adjacent columns show summary data from ORGs in single row, filtered portals based on the number of days entered above each column (I.e. 60, 180, etc.) Retention % is an unstored calculation in ORGs 1 - (count Date Canceled / count  Date Ordered). I would like to show this at the bottom of each column, too.

       

        • 1. Re: Grand Total of Summary Fields in filtered portals
          alangodfrey

          Create another portal with exactly the same relationship, and exactly the same filters.

          Make it a one-row portal.  Put it separately, below the existing portal, and put the summary fields there.

          • 2. Re: Grand Total of Summary Fields in filtered portals
            sccardais

            Alan:

             

            If I'm understanding your suggestion, I tried that and it didn't work.  See below.

             

            The area outlined in red is a copy of the single row portal in the body part above.

             

             

            The portal in the body area is a single row portal based on a multi-key relationship between Products and ORGs. The portal in this column is not filtered. The columns to the right (from the screenshot in first post) are filtered. The field showing a value of 395 in the body is a summary field in ORGs that counts the number of records in ORGs with a value of anything in ORGs::DateOrdered.

             

            Not sure if that detail helps or not. I'm sure I'm going to slap my palm to my forehead when I see how to do this. !

            • 3. Re: Grand Total of Summary Fields in filtered portals
              alangodfrey

              Sorry - I may not have read your question correctly.

              Most people have the problem that they create a portal of records, then they add a Summary field (from the same relationship) below the portal, and it doesn't show the summary data they expected.  Then they realise they have filtered the portal.  Creating a duplicated (inc. the filters) one-row portal makes the data match.

               

              Are you wanting to put the summary of the portal data in a Summary Layout Part?  Or is this one-row duplicated portal also in the Body part?

               

              (Have I mis-interpreted your question?)

              • 4. Re: Grand Total of Summary Fields in filtered portals
                sccardais

                Using the screenshot included in the original post, I want to show a total of the columns in a Trailing Grand Summary.

                 

                For example, the first column (Overall > Orders Received) should total 7,654 --- the sum of 51 + 36 + 542, etc. . These are summary fields in a single row portal. This portal is not filtered. The portals to the right are filtered I think the problem / solution is the same. If I can figure out how to total the first column (should equal 7,654, I think it will apply to the other columns as well.

                 

                The layout is based on a Products table that contains 10 records. The values in the cells are from a related table, ORGs that holds customer information.

                 

                Thanks very much for your help.

                • 5. Re: Grand Total of Summary Fields in filtered portals
                  alangodfrey

                  I'm a tad confused.  Your post title is '...summary fields in filtered portals'.

                   

                  If you have a 'portal', then the concept of List View is semi-redundant, so why do you need a Trailing Grand Summary?  Why not just place the single-row portal in the Body Part?  Trailing Summary suggests you want the total of many records in a List view, not many related records in a (filtered) portal.

                  • 6. Re: Grand Total of Summary Fields in filtered portals
                    sccardais

                    Alan:

                     

                    I apologize for the confusion. I'm creating a cross tab layout using single row portals with summary fields from related tables. Most of the portals are filtered but not all. The brown areas in the screenshot below are filtered portals. Just to be clear, in the  first row, the values 51, 35 and 31.37% are in a single, unfiltered portal. 3 and 94.12% are in a different single row and filtered portal. 10 and 80.39% are in another single row, filtered portal.

                     

                    The next 9 rows are summaries for the other 9 records in the Products table. These are all in a layout body part.

                     

                    The row at the bottom labeled "Totals" is a Trailing Grand Summary part. The values, a copy of the single row portal above, are incorrect. As you can see, they are simply the same values as the  last row in the body part. I incorrectly thought I would get a total of all the rows immediately above by putting the portal in a Trailing Grand Summary part. I want to total the values in the 10 rows above. The total for the first column should be 7,654 - a total of 51, 36, 541, etc.

                     

                    Thanks again for your help.

                     

                     

                    • 7. Re: Grand Total of Summary Fields in filtered portals
                      alangodfrey

                      No, sorry - my fault entirely.

                       

                      You have a body part with multiple single-line portals, some filtered, and you want to see their total in a Trailing Grand Summary part?

                      If that is the case, it is beyond me.  I have to hand over to higher authorities.  Sorry.

                      • 8. Re: Grand Total of Summary Fields in filtered portals
                        philmodjunk

                        Try coming up with either a new relationship and/or portal filter that would list all the values you need in order to compute your summary value. If you use a portal filter, put the summary field into the portal row like the others in your cross tab report. If you don't use a portal filter, you can just put the summary field from your new table occurrence directly on the layout.

                        • 9. Re: Grand Total of Summary Fields in filtered portals
                          sccardais

                          No problem. Thanks very much for trying. Wish I had the knowledge to phrase the issue so succinctly as you did in this reply.

                          • 10. Re: Grand Total of Summary Fields in filtered portals
                            sccardais

                            Thanks. I’ll try that.