11 Replies Latest reply: May 15, 2014 10:14 AM by Olly Bond RSS

    Looking for Grouping strategy ideas

    Patt _

      I'm working in v9.5 and I have a report with the following structure for each record.  I'm looking to retrieve the Description and Amount for each unique combination of the Project and Account fields at the bottom. 

       

      -


       

      Position                         Start    Review   General     End   Starting End                                                  

      Employee                   Seq  Rate     Amount    Amount    Rate    Grd/Stp Stp  Description                             Amount  

      -


                                                                                      WATER TREATMENT SPECIALIS    6   32.5230    .0000     .0000   32.5230  625  1   1  REGULAR HOURS                        67,833.68  

      FRANKS, JEFF J                         12/30/11   7/01/11                         Total wages                          67,833.68 *                                                                               

      CLASS 4 LIFE INSURANCE                  240.23                                                                               

      HEALTH FAMILY CITY                   12,964.80                                                                               

      MEDICARE ONLY FICA                      935.44                                                                               

      NM RETIREE HEALTH CARE                1,356.75                                                                               

      PERA - IBEW  CITY                     6,206.82                                                                               

      SOCIAL SECURITY CITY                  3,999.61                                                                               

      WC                                        9.20                                                                               

      Total benefits                       25,712.85 *                                                                               

      Total expense                        93,546.53 **                                                                               

      Percent   Project   Account                                                                               

      50.000   E50210    601-6020-551.10-11                                                                               

      50.000   E50510    601-6020-551.10-11                                                                               

      /CODE

       

      Therefore, the table view I'd like to see from this would be:

      E50210 601-6020-551.10-11 REGULAR HOURS                67,833.68

      E50210 601-6020-551.10-11 CLASS 4 LIFE INSURANCE          240.23

      E50210 601-6020-551.10-11 HEALTH FAMILY CITY           12,964.80

      E50210 601-6020-551.10-11 MEDICARE ONLY FICA              935.44

      E50210 601-6020-551.10-11 NM RETIREE HEALTH CARE        1,356.75

      E50210 601-6020-551.10-11 PERA - IBEW CITY              6,206.82

      E50210 601-6020-551.10-11 SOCIAL SECURITY CITY          3,999.61

      E50210 601-6020-551.10-11 WC                                9.20

      E50510 601-6020-551.10-11 REGULAR HOURS                67,833.68

      E50510 601-6020-551.10-11 CLASS 4 LIFE INSURANCE          240.23

      E50510 601-6020-551.10-11 HEALTH FAMILY CITY           12,964.80

      E50510 601-6020-551.10-11 MEDICARE ONLY FICA              935.44

      E50510 601-6020-551.10-11 NM RETIREE HEALTH CARE        1,356.75

      E50510 601-6020-551.10-11 PERA - IBEW CITY              6,206.82

      E50510 601-6020-551.10-11 SOCIAL SECURITY CITY          3,999.61

      E50510 601-6020-551.10-11 WC                                9.20

      /CODE

       

      I can't for the life of me figure out the strategy I need to use to do the grouping.  Obviously I need some way of reading the record twice to do this.

       

      If anyone can point me in the right direction I'd really appreciate it!  Thanks!

        • Looking for Grouping strategy ideas
          Olly Bond

          Hello Patt,

           

          You can do this, using the Multi-Column Region. There are examples on my blog, but I'll have a bash at your sample data tomorrow. A word of warning - there's an annoying bug in MCR that only got fixed properly in v11, so if you want to automate this kind of work in a production environment then I would strongly recommend an upgrade.

           

          Best wishes,

           

          Olly

            • Looking for Grouping strategy ideas
              Patt _

              Hi Olly,

               

              Thanks for the idea ... I'll play with it some today as I can, and will update if I have any success.  Any details on what the MCR bug is?

                • Looking for Grouping strategy ideas
                  Olly Bond

                  Hello Patt,

                   

                  In v10, the last two columns of the last row before a page break can be misnumbered in the table window. You can get around it by using two extra columns and discarding the redundant data, or with careful use of dummy footer templates to stop the MCR before a page break occurs. Also in v10, you define a model as having, say, 10 columns, close it, and when you re-open it it defaults to showing two columns in the MCR dialog - very annoying... In v11 it's rock solid.

                   

                  With your data, you'll need to define a multi-column region on your detail template, with enough columns to cope with the maximum number of lines in the Project region:

                   

                  Percent   Project   Account                                                                               

                  50.000   E50210    601-6020-551.10-11                                                                               

                  50.000   E50510    601-6020-551.10-11[/CODE]

                   

                  Grab this region as a multi-line memo field, and then break it up using Textline(MemoField;Column()). I hope that you don't have any coworkers assigned to more than 40 projects? That's the hard-coded limit in Monarch for multi-column regions - so if you need to get around that you'll need multiple models. I have been asking Datawatch for four years to increase that limit, but haven't had any joy yet...

                   

                  Best wishes,

                   

                  Olly

                    • Looking for Grouping strategy ideas
                      Patt _

                      Thanks for the updated suggestion Olly.  I have a meeting early this morning, but will be working on this thereafter, and will post progress.  I must admit, I don't exactly understand how I need to implement the MCR, but I haven't had the chance to really try to apply this yet.

                        • Looking for Grouping strategy ideas
                          Olly Bond

                          Hello Patt,

                           

                          The crucial thing to keep in mind is that in Monarch you get one row in your table for each time the detail template hits a match in your report.

                           

                          Filters only ever let you reduce the number of rows. In your case, you want each row (I'm guessing that's the payroll items) to appear twice, so that you can associate the first instance with one project assignment, and the second with the other.

                           

                          Defining a MCR on the detail lets you do this - you double the number of rows in your table, and the Column() function returns 1 for half of them and 2 for the rest, so you can use it to chop your data up into the right pieces.

                           

                          One problem - you're using v9, and the Column() function was introduced in v10. Given that v10 dates from 2008, you might be able to convince IT to support an upgrade. If you're stuck, I can dig out a horrible formula based on the Mod() of the Rowno() that only works after you apply a sort order based on Page() and Line(). For the Column() function on its own, not to mention Cleared By templates, it's worth upgrading.

                           

                           

                          Best wishes,

                           

                          Olly