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.
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!
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!
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.