6 Replies Latest reply: May 15, 2014 10:04 AM by Olly Bond RSS

    Tidying up a summary

    Kamawell2 _

      I've made a model and summary that do almost[/I] everything I want them do - but not quite!  Here's what it looks like at the moment.


      Name                          Number      Band  Element                  Amount   Note


      Employee A                    12345678       3     Basic NR NHS              37.5       January

                                                   Enhanced NR NHS              3.5       January

                                                   Enhanced NR NHS              10.66       January

                                                   Enhanced NR NHS              28.5       January

                                                   Overtime NR NP NHS      11.16       January


      Employee B                    45678912       2     Basic NR NHS              18       February


      Employee C                    45874587-2  4     Basic NR NHS              32.25       March


      Employee D                    99887766       3     Basic NR NHS              37       April

                                             Enhanced NR NHS              2       April

                                             Enhanced NR NHS              6       April


      Employee E                    11410567       3     Basic NR NHS              37.5       May

                                             Enhanced NR NHS              3.25       May

                                             Enhanced NR NHS              10.66       May

                                             Enhanced NR NHS              11       May

                                             Enhanced NR NHS              38.75       May

                                             Overtime NR NP NHS      26.16       May[/CODE]


      The first three columns in the summary have had the "supress duplicate values" box ticked.  I want to do the same with the Note column on the right hand side but the box is greyed out.


      I've tried playing around with varying results. Putting the notes column fourth from the left allowed me to tick the box and hid the duplicate values but made the report a bit harder to use.  Adding a dummy field to the far right allowed me to tick the box but didn't hide the values. 


      I know it seems like a small point but keeping that column on the right and hiding the duplicates is the best layout for what I need!  The whole idea of this report is to improve on the usability of its source.


      A few details - All the fields are defined as key (I've played about with that too to no effect) and the Notes field is currently a calculated field that copies an external lookup field (when it was just a straight external lookup I didn't even get the 'displayed' tab in the field properties!)


      If anyone can help I'd be very grateful.

        • Tidying up a summary
          Olly Bond

          Hello Kamawell,


          There are probably a couple of ways of fixing this, and which one is best depends on the export format and the complexity of the formatting options you want to apply.


          I'd suggest putting the Notes in column 4 so they behave themselves, then exporting to Access format, then opening this as a database in a second Monarch project, and simply re-ordering the fields to suit.


          Best wishes,



            • Tidying up a summary
              Kamawell2 _

              Hello Olly


              Thanks - I'll give that a try.



                • Tidying up a summary
                  Grant Perkins



                  What are you doing with the results to present it to the users? Printing as a report or exporting as an Excel output? (Or perhaps even sending it out as a PRF file ? ?


                  If you are exporting it to Excel (or even Access iirc) Monarch will export column to column by column name so you should be able to re-order the columns in your target export file to get the result you want. If you are exporting directly then I would go with Olly's suggestion.




                    • Tidying up a summary
                      Kamawell2 _

                      Hi Grant


                      I'm printing straight from the summary.  I'm a fairly recent convert to summaries - previously I was mainly using Monarch to put pdf reports into Excel and making them presentable there.  A summary saves so much time!


                      I haven't had a chance to try Olly's method yet but I'll give it a go later today.





                        • Tidying up a summary
                          Kamawell2 _

                          OK - here's where I look dim.  My apologies if I've missed the point altogether.


                          I've taken my summary with the well-behaved Notes column in position (4th from the left) and exported it as a summary in .mdb format.


                          I've then gone File>Open Database and opened the .mdb file.  I get a table view that looks almost exactly like the table view from my original report - all duplicate values showing in all columns.


                          I can then go through the usual summary creation but with the same problems as before.  I feel I'm missing something here!