7 Replies Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Multiple Sub-total Lines

    Billy _

      I require assistance in creating multiple sub-total lines for utilizing different rows within each key value break.  See example below of one column in the report:

       

      BJ 1A PRAT AT PAY & TV   551635.00 (1)

              z ALLOTMENT  -211400.00 (2)

              z OBLIGATION      -54693.47 (3)

              z RESERVATION -21638.31 (4)

       

         Subtotal Oblig Plan   475303.22 (A)

         Subtotal Allotment    135068.22 (B)

       

      I require one sub-total (A) --- (1) minus (3) minus (4) and another (B) --- (2) minus (3) minus (4).

        • Multiple Sub-total Lines
          Grant Perkins

          Originally posted by Billy:

          [font="courier"]BJ 1A PRAT AT PAY & TV   551635.00 (1)

                  z ALLOTMENT  -211400.00 (2)

                  z OBLIGATION      -54693.47 (3)

                  z RESERVATION -21638.31 (4)

           

             Subtotal Oblig Plan   475303.22 (A)

             Subtotal Allotment    135068.22 (B)[/font][/quote]I require one sub-total (A) --- (1) minus (3) minus (4) and another (B) --- (2) minus (3) minus (4). /b[/quote]Billy,

           

          Are these fields in the body of the report somewhere as ready calculated values or are they result of some prior Monarch activity?

           

          Are they all part of the same detail 'record' which might be uniquely identified by the line;

           

          [font="courier"]BJ 1A PRAT AT PAY & TV /font[/quote]or some part of it?

           

          How many rows (fields 2,3 and 4, etc.) can there be?

           

          Grant

          • Multiple Sub-total Lines
            Billy _

            Grant,

             

            The data that is reflected are all separate columns and rows with duplicate values suppressed (BJ,1A,PRAT,AT PAY & TV).  Data reflected are from an imported database.  The data you see is exactly how I have it in Monarch except for the 2 subtotal lines which I would like to know how to create in Monarch.

             

            BJ 1A PRAT AT PAY & TV   551635.00 (1)

                        z ALLOTMENT  -211400.00 (2)

                        z OBLIGATION  -54693.47 (3)

                        z RESERVATION -21638.31 (4)      Subtotal Oblig Plan       475303.22 (A)

            Subtotal Allotment        135068.22 (B)

            • Multiple Sub-total Lines
              Grant Perkins

              Hi Billy,

               

              Is this were you have got to with a summary definition from an table created using a database import?

               

              It looks and, from your description, sounds like it is but I'm not entirely sure. I may have missed something obvious in your description.

               

              Grant

              • Multiple Sub-total Lines
                Billy _

                I wrote a SQL script to create the database that was imported into Monarch with the following fields:

                 

                PM,AMS,MP,DESC,AMOUNT,MOS

                 

                I imported the data into Monarch and utilized the summaries to create the report but do not know how to create 2 subtotal lines as noted in example.

                • Multiple Sub-total Lines
                  Grant Perkins

                  Originally posted by Billy:

                  I wrote a SQL script to create the database that was imported into Monarch with the following fields:

                   

                  PM,AMS,MP,DESC,AMOUNT,MOS

                   

                  /b[/quote]Hi Billy,

                   

                  If your incoming records had a value for each description in each record you could perform calculation in the table (or in the summary) to deliver the number you need but Monarch does not offer a formula capability that allows a 'spreadsheet' type calculation of the sort you require.

                   

                  So one option would be to export the values to Excel for the final calculation.

                   

                  If I was doing it in Monarch I would Export what you have (or what you have but with the PAY & TV,

                  ALLOTMENT, OBLIGATION and RESERVATION values displayed "Across" perhaps) and then use the resulting file with a new model to get the 4 values into a single record and allow the calculations to be performed.

                   

                  If this was something that would be required regularly a simple batch file would allow the two stage process to be run as a single user action.

                   

                  Does this suggestion help you at all?

                   

                  I would guess that a third option might involve revisiting the original SQL extraction to consider doing that slightly differently but I have no idea whether that would be possible Or even beneficial  - just added it here for completeness!

                   

                  HTH.

                   

                  Grant

                  • Multiple Sub-total Lines
                    Billy _

                    Grant,

                     

                    Thank you very much for the assistance and advice.  I'll have to revisit this problem and maybe the only way is to create everything in SQL. 

                     

                    I like the idea of displaying the fields across but I already have a field (MOS) that is displayed across which reflects the months actions occurred.

                     

                    Again, thank you

                    Billy

                    • Multiple Sub-total Lines
                      Grant Perkins

                      Originally posted by Billy:

                      I like the idea of displaying the fields across but I already have a field (MOS) that is displayed across which reflects the months actions occurred.

                       

                      /b[/quote]Hi Billy,

                       

                      I wondered where that field went ... 

                       

                       

                      If 'across' is not available down is OK if you can treat the black of data (as per sample) as a single record. Just a little bit more work to set it up. If the output always has just those values and in that order it would be quite easy to establish a 4 line template. If there might be other lines at some point or the order might vary (but why would it since you are  preparing it in Monarch anyway ...) then you might need to do something to deal with the vertically moving fields using the 'Preceding String' feature in the Advanced Properties tab.

                       

                      From where you have already reached it would not take long to set up.

                       

                      HTH.

                       

                       

                      Grant