0 Replies Latest reply: Jul 2, 2008 8:09 AM by mcandrje _ RSS

    Line Items for Subtotals

    mcandrje _

      I currently have a report that subtotals by salesperson, then ad class, then account.  This works fine, and my manager likes the fact that each ad class is subtotaled, with each account's activities subtotaled under the ad class.  However, she wants to see the ad class subtotals also included as single line items alone at the bottom of each salesperson's tab, along with the total. Kind of like you could do on an Excel spreadsheet if you want to take the time to reiterate all the work within the report and make a short end summary of it.  I'm not even sure that can be done! 


      Can anyone help with this?  It would be greatly appreciated.  I would have tried to include a sample of the spreadsheet, but cut and paste doesn't work here and I'm not familiar with forum posting.


      Thank you!



        • Line Items for Subtotals

          I think this can be done, but the easier solution would probably be to give a second tab instead of rows at the end of the spreadsheet. 


          I often give users two tabs in every report I do.  One is a summary report, and the other contains the detailed data, if they ever want to investigate.


          Or if you're using Monarch V9, you could try selling your manager on the drill down feature, and give them a summary and allow drilling down on details if desired.


          Would either of these solutions work out?

            • Line Items for Subtotals
              Grant Perkins



              Good ideas from Joey. If your manageer is using Monarch to view the result the drill feature in the summaries is a very powerful tool.


              I wanted to confirm where the data source is for this At the start you mention a report but later indicate problems cutting and pasting a spreadsheet example. Is it the report that is hierarchically structured as you describe or a resulting spreadsheet?


              Do you know why your boss wants the extra line? I was just wondering if an additional measure - say % of total - might work for her.



            • Line Items for Subtotals
              mcandrje _

              Thank you both for your suggestions.  Unfortunately, my manager doesn't have Monarch. And, the model already has about 40 tabs for the 40 salespersons and thousands of pages for the accounts with detail.  I don't think she's going to go with another summary page for each salesperson.  I guess she wants it on the bottom of the page, too, so she can just check at a glance without going through the detail until later.


              The hierarchy is in the model itself - the cut and paste I was talking about was concerning trying to show you how the model was set up - I wasn't able to get it to look right in a message. 


              Thanks for your suggestions.  Maybe I can talk her into buying another Monarch license (yeah...right).



                • Line Items for Subtotals
                  Data Kruncher

                  Hi Janet,


                  Would something like Nigel's idea about [URL is no longer valid] help you out at all?



                    • Line Items for Subtotals
                      Grant Perkins

                      Hi Janet,


                      I suspect that Kruncher's memory, being better than mine it seems, may have provided a pointer to a useful approach for you.


                      For the Cut and Paste thing, if you need to post something with retained format click on the # symbol on the text entry window's edit control menu at the top of the message area and then paste the result between the code tags that appear.


                      Or paste the text then highlight it and click on the # button.


                      I'm guessing that what your boss may be seeking is a view of how much of the total each salesperson provides (for example). Something  like that? If so another measure may be an option and obtainable from the summary.




                  • Line Items for Subtotals
                    mcandrje _

                    I'm still having problems with this summary.  I'm including a sample of it with the 4 lines at the bottom as the ones my manager would like to see as  single line items at the end of the summary:


                    SB01     Jodie     INTERNET     MRSG     Account     111111     MGG     $160.00

                                        Account     112211     MGG     $25.00

                                        Account     131155     MGG     $160.00

                                        Account     121212     MGG     $140.00

                              Subtotal INTERNET                - - -          $485.00

                              NATIONAL      NATL     Account     131313     PR     $1,500.00

                              Subtotal NATIONAL               - - -          $1,500.00

                              OTHER MISC REVENUES     OTHR     Account     125511     PR     $85.00

                              Subtotal OTHER MISC REVENUES               - - -          $85.00

                              RETAIL      PP     Account     555511     PR     $50.00

                                        Account     103893     PR     $77.22

                                        Account     123134     PR     $0.00

                                        Account     131355     PR     $250.00

                                        Account     121255     ROC     $130.00

                                        Account     116655     PR     $1,100.00

                                             116655     ROC     $85.00

                              Subtotal RETAIL               - - -          $1,692.22



                              INTERNET                          $485.00

                              NATIONAL                         $1,500.00

                              OTHER MISC REVENUE                         $85.00

                              RETAIL                          $1,692.22



                    Subtotal SB01                         - - -          $3,762.22

                    Summary                         - - -          $3,762.22



                    She still wants to see the subtotals in the report, though, too.  I hope you can tell something from this sample - it doesn't look exactly the same as it does in my worksheet.


                    Any further ideas on whether I can make this happen?  I have about 40 tabs in this model already (one for each salesperson, per the manager's request), so am trying not to make it larger. She wants to see the detail, the subtotals of the categories, and then the totals of all the categories as one line items at the bottom of the sheet.

                    Thanks for any help with this - a lot of what they are asking for now in Monarch is brand new to me!



                      • Line Items for Subtotals
                        Nigel Winton


                        I am sure I can help you with the appending summaries that DataKruncher has pointed you to. I have used this for many similar problems and it can all be automated with Project Exports and I have no doubt with code if necessary. Have a look at the link in Krunchers reply and if you have any questions please contact me. I have PMd you as well to get the solution under way. If you can send me the original file I will set up the model and project export for you. The solution I use is quite easy and will allow you to append sub totals from summaries as long as you follow some simple rules.


                        Looking forward to solving your problem.