7 Replies Latest reply: Jun 24, 2016 6:48 AM by Wendy Hilliard RSS

    Monarch v13 multicolumn Design Mode help

    Wendy Hilliard

      OK!  I have a report with all data per record on one line.  Each line contains the information for 2 entries.  The amount column has to be negated for the 2nd entry.  Here is an example:

       

      TRXTRXalistqtycostamountacct1acct2
      56I WIPI1058 040239795.000-1.441157348.57-1140 23001150 2385
      55I WIPI1058 040239325.000-1.441156671.26-1150 23001150 2885
      85I WIPI1166 220157200.000-1.131864738.96-1150 23001150 2885
      85I WIPI1176 220129575.000-1.131833472.99-1150 43001155 2385
      67I WIPI1176 220153625.000-1.131860692.78-1180 23001155 2385
      55I WIPI1176 220132825.000-1.131837151.34-1150 23001150 2385
      55I WIPI1176 220157200.000-1.131864738.96-1150 23001150 2385

       

      I need to pull all of the data per line, twice.  Once for Acct1 and once for Acct2.  Amount column can be absolute value for both.

       

      help!

       

      Wendy

        • Re: Monarch v13 multicolumn Design Mode help
          Lloyd L

          Hello Wendy,

           

          Good question. I'm not quite sure if I get your expected outcome correctly. Can you make here a sample of your expected output?

           

           

          -Lloyd

          • Re: Monarch v13 multicolumn Design Mode help
            Grant Perkins

            Wendy,

             

            From the tags on the post it looks like you are anticipating using Multi Column Region.

             

            My initial instinct is try just that  but only for the two Acct columns.

             

            Assuming the report format is pretty much as you have laid it out, treat all columns to the left of Acct1 as fields in an append.

             

            If you use the Column() function or the column header as another  append (assuming the header is meaningful)  you should be able to see 2 lines for every original row and ID the lines that need to be made negative values - then just create a calculated field for each record that gives the value (+ or -)  you required based on a conditional formula of the type (If Col = A , Amount, 0 - Amount)

             

            That sort of thing, roughly.

             

            HTH.

             

             

            Grant

              • Re: Monarch v13 multicolumn Design Mode help
                Wendy Hilliard

                Thanks!!  This worked for me.  I ended up using the column() function which provided the answer.  Then several functions later I had exactly what was required.  Thanks to all for the help!

                 

                Wendy

                  • Re: Monarch v13 multicolumn Design Mode help
                    Grant Perkins

                    Hi Wendy,

                     

                    Excellent news.

                     

                    The alternative suggestions from Paul (Data Prep Studio) and Olly (extreme use of MCR where the format of the original may be nasty) are also good approaches. Much depends on how the original report looks and what can be done with it. I'm guessing the original may not be quite as "clean" as your posted illustration. Mostly with Monarch it's just a case of working out which approaches and tools in the tool box give the most effective result compared to the need.

                     

                    Thanks for the update!

                     

                     

                    Grant

                • Re: Monarch v13 multicolumn Design Mode help
                  Paul Jones

                  In Monarch Classic:

                   

                  1) Open report

                  2) Report Design

                  3) Create Append Template

                  4) I used a numeric trap as the first one to highlight each of the lines

                  5) Create fields for everything except acct1 and acct2

                  6) Create Detail Template

                  7) Enable MCR

                  8) Set the Left Position of first column so that it begins just to the left of the acct1 data

                  9) Set Number of Columns to 2 and set the width of the columns so that the second column begins just to the left of the acct2 data

                  10) Select Accept

                  11) Define the trap with a numeric trap in the position of acct1

                  12) Define the field for acct1; take note that it also captures the acct2 data as acct1 (or maybe just call it acct)

                   

                  Data will look like this in the table view:

                   

                  TRX    list    qty    cost    amount    TRXa    acct

                  56    1058 0402    -39795.000    1.4411    -57348.57    I WIPI    1140 2300

                  55    1058 0402    -39325.000    1.4411    -56671.26    I WIPI    1150 2300

                  85    1166 2201    -57200.000    1.1318    -64738.96    I WIPI    1150 2300

                  85    1176 2201    -29575.000    1.1318    -33472.99    I WIPI    1150 4300

                  67    1176 2201    -53625.000    1.1318    -60692.78    I WIPI    1180 2300

                  55    1176 2201    -32825.000    1.1318    -37151.34    I WIPI    1150 2300

                  55    1176 2201    -57200.000    1.1318    -64738.96    I WIPI    1150 2300

                  56    1058 0402    -39795.000    1.4411    -57348.57    I WIPI    1150 2385

                  55    1058 0402    -39325.000    1.4411    -56671.26    I WIPI    1150 2885

                  85    1166 2201    -57200.000    1.1318    -64738.96    I WIPI    1150 2885

                  85    1176 2201    -29575.000    1.1318    -33472.99    I WIPI    1155 2385

                  67    1176 2201    -53625.000    1.1318    -60692.78    I WIPI    1155 2385

                  55    1176 2201    -32825.000    1.1318    -37151.34    I WIPI    1150 2385

                  55    1176 2201    -57200.000    1.1318    -64738.96    I WIPI    1150 2385

                   

                  In Data Prep Studio (included with Monarch Complete)

                   

                  This is much simpler.

                   

                  1) Select transform data. Unpivot.  Select acct1 and acct2 to UnPivot.

                  2) You will have the same table as above except there will be an Attribute column stating whether that particular value was acct1 or acct2.  Remove this column (unless it is helpful for you).

                  3) Rename Value column to acct.  You will now have the exact same table as above.

                   

                  Hope that helps!

                   

                  Paul Jones

                    • Re: Monarch v13 multicolumn Design Mode help
                      Olly Bond

                      Hello Wendy, hello everyone,

                       

                      It's very interesting to see that the unpivot option in Prep might be helpful, but this is also easy to do in Monarch Classic. You don't need to define an Append template. Just a single line detail, with the MCR enabled as 2 columns, 1 character wide, starting in column 100, and then paint a single field Accounts covering both codes. A calculated field if (Column() = 1; intrim(left(Accounts;10)) ; intrim(right(Accounts;10)) ) will then break it up for you.

                       

                      I've emailed you a model.

                       

                      Best wishes,

                       

                      Olly