Multiple "Footer" templates?

[Scott B]

I'm having a problem with what seems to be a limitation of Monarch. The reports I am trying to process contain multiple "footer" records. The main footer is guaranteed to appear, but up to 4 other footer records may appear after the "main" footer. As far as I can tell, Monarch only supports a single footer template. If I set the secondary footer template types to "append", the data gets shifted down to the next set of detail records. The report format is below. The MAIN footer records are prefixed with: "-TOTAL ITEMS:", while the secondary (optional, at least 1) footer records start with "VISA", "MASTERCARD", or "AMEX".


1MD-028 3167/0000 REPORT TITLE XXXXXX XXXXXXX XXXXXXXX SERVICES -FC- 10/20/03 PAGE
XXXXX XXXX XX RUN DATE 10/21 TIME 00:57
-
-MERCHANT NUMBER: XXXXXXXXXXXXXXX BATCH NUMBER: 11111111 LOCATOR NUMBER: 999 ETC ID: 00001 CUT-OFF: 1 CONF LETTER: 0
MERCHANT NAME: WIDGETS, INC

0 CARDHOLDER TRAN TRAN TRAN CARDHOLDER TRAN TRAN TRAN
NUMBER DATE TYPE AMOUNT TIME CODE NUMBER DATE TYPE AMOUNT TIME CODE
0 XXXXXXXXXXXXXXXX 102003 SALE 377.56 16:34:07 020723 XXXXXXXXXXXXXXXX 102003 SALE 81.99 16:36:48 993455
XXXXXXXXXXXXXXXX 102003 SALE 50.92 16:35:40 035543 XXXXXXXXXXXXXXXX 102003 SALE 182.88 16:38:07 020133
-TOTAL ITEMS: 4 VOIDS: 0 VOIDED ITEMS: 0 NET ITEMS: 4 NET DEPOSIT: 693.35 CLOSED ON 10/20/03 16:39:04 BY:
VISA 2 0 0 2 428.48
MASTERCARD 2 0 0 2 264.87
-MERCHANT TOTAL ITEMS ACCEPTED: 4 TOTAL NET DEPOSIT: 693.35
-
-MERCHANT NUMBER: XXXXXXXXXXXXXXX BATCH NUMBER: 22222222 LOCATOR NUMBER: 999 ETC ID: 00002 CUT-OFF: 1 CONF LETTER: 0
MERCHANT NAME: STUFF, LLC

0 CARDHOLDER TRAN TRAN TRAN CARDHOLDER TRAN TRAN TRAN
NUMBER DATE TYPE AMOUNT TIME CODE NUMBER DATE TYPE AMOUNT TIME CODE
0 XXXXXXXXXXXXXXXX 101903 SALE 348.30 13:24:51 178237
-TOTAL ITEMS: 1 VOIDS: 0 VOIDED ITEMS: 0 NET ITEMS: 1 NET DEPOSIT: 348.30 CLOSED ON 10/19/03 19:00:55 BY:
AMEX 1 0 0 1 348.30 Is there any way to specify the secondary footer templates to be of type "Footer", and not "Append"? Thanks!!!

[Grant Perkins]

Scott,

Monarch has only one footer (as indeed does your report as I see it) but that footer can have multiple rows. (As does your report footer the way I look at it). Logically the concept of multiple footers is somewhat strange, though I have seen reports with multiple summary calculations where different subtotal groupings happen to coincide. There are other ways of working around that sort of complexity if it is necessary to do so.

The tricky bit is allowing for the varying number of rows in each footer - I assume there will always be at least 2 rows?

There are a couple of possible ways to set up a multi row footer and process the output to get the data you require. I will have a play with your sample and come back with suggestions.

As you have identified the Append feature looks to add data from ABOVE your detail lies. However it is often possible to work around such a limitation by selecting lines for the template that start ABOVE the detail line (or even at the detail line sometimes) but mapping the fields from the sections below. Obviously the format of the report can influence the success of such an approach so careful checking is required.

Just in case it helps make things easier - which version of Monarch do you have?

Grant



Originally posted by Scott B:
I'm having a problem with what seems to be a limitation of Monarch. The reports I am trying to process contain multiple "footer" records. The main footer is guaranteed to appear, but up to 4 other footer records may appear after the "main" footer. As far as I can tell, Monarch only supports a single footer template.

Is there any way to specify the secondary footer templates to be of type "Footer", and not "Append"? Thanks!!!

[Grant Perkins]

Scott,

This report seems to separate quite nicely into Header, a append, detail lines and a
footer up to 6 rows long.

In the footer you have, as I understand it, the possibility of 4 possible variable lines (Visa, MASTERCARD, AMEX +1 ?) plus the first line and a total line. The Section will always have at least 3 potential lines.

Now the question is which fields you want since the answer to that will determine how many rows you need for the Footer template. You will need enough rows to be able to define each of the fields you need from each row without overlapping. Hopefully this will become clearer in the next section.

To keep it simple I suggest you select 6 rows for the footer starting at TOTAL ITEMS:

Select the field you want from that first row.

On the next row (call it footer detail?), highlight the fields you want to select from the row. For EACH of them, go into the field definition, go to OPTIONS and set the START FIELD ON value to PRECEEDING STRING and enter, say, "amex" as the preceeding string.

On the next row, ignoring what you may have on screen, set up the same field positions and options but in this case set the preceeding string to "visa". Do the same thing on the next 2 (or more if necessary) rows using the other known preceeding string values for the row positions.

Finally map any fields you may want from the final row starting "Merchant Total Items". (If you don't want anything from that row don't include it in the template and you will only need 5 rows - or 4 rows if you don't need anything from the first row either!

The fields you do want can be in any order on any row as long as they don't overlap any other field. However they must be in the correct horizontal position on the row to pick up the data required.

The result should be that any fixed fields from row 1 and the final row are picked up plus any field in any lines which match the Preceeding String entries. On the table the fields will always exist for each detail record but will be blank where no information was collected.

There are other techniques involving a multiline field selection and then splitting the data out with further processing. They work well but in this case you should be able to get what you want using this method. (As far as I can tell from the sample of format and data provided! Sometimes reports have nasty features tht only appear when you get into the big stuff and the details.)

Let us know how it goes. Any problems and we will look for plan B. ;)

Grant

[Scott B]

Grant, thanks for the help! Your method worked out perfectly, and I can now pull out all the data fields I need in the correct locations using the single, multi-line footer. Thanks again for the help!

[Scott B]

Grant, I got your message, thank you for the kind words. I wish I could say everything is working fine, but thats not the case now. I had everything working great, until someone pointed out that I was missing a piece of data....


MERCHANT NUMBER: XXXXXXXXXXXXXXX1 BATCH NUMBER: ######## LOCATOR NUMBER: 980 ETC ID: 07302 CUT-OFF: 1 CONF LETTER: 0
MERCHANT NAME: Merchant X

CARDHOLDER TRAN TRAN TRAN CARDHOLDER TRAN TRAN TRAN
NUMBER DATE TYPE AMOUNT TIME CODE NUMBER DATE TYPE AMOUNT TIME CODE
XXXXXXXXXXXXXXXX 100403 SALE 1760.96 19:25:06 004841 XXXXXXXXXXXXXXXX 100403 SALE 101.83 20:25:52 004220
XXXXXXXXXXXXXXXX 100403 SALE 176.96 19:39:02 004909 XXXXXXXXXXXXXXXX 100403 SALE 106.18 21:41:46 004334
XXXXXXXXXXXXXXXX 100403 SALE 171.60 19:42:18 096732 XXXXXXXXXXXXXXXX 100403 SALE 697.13 22:01:19 004381
XXXXXXXXXXXXXXXX 100403 SALE 144.79 19:49:52 048317
TOTAL ITEMS: 7 VOIDS: 0 VOIDED ITEMS: 0 NET ITEMS: 7 NET DEPOSIT: 3159.45 CLOSED ON 10/05/03 18:00:00 BY:
VISA 6 0 0 6 2,987.85
MASTERCARD 1 0 0 1 171.60

UNMATCHED VOID TRANSACTIONS MERCHANT NUMBER: XXXXXXXXXXXXXXX1 ID: BATCH NUMBER: #####
CARDHOLDER TRAN TRAN TRAN CARDHOLDER TRAN TRAN TRAN
NUMBER DATE TYPE AMOUNT TIME CODE NUMBER DATE TYPE AMOUNT TIME CODE
XXXXXXXXXXXXXXXX 100403 SVOID 176.96-19:35:32
NUMBER UNMATCHED VOIDS : 1 TOTAL AMOUNT : 176.96-
MERCHANT TOTAL ITEMS ACCEPTED: 7 TOTAL NET DEPOSIT: 3,159.45 Notice that the "MERCHANT TOTAL ITEMS ACCEPTED:" value appears below the "UNMATCHED VOID TRANSACTIONS" section. In EVERY OTHER instance, that line appears under the "VISA", "MASTERCARD", "DISCOVER", or "AMEX". The problem is that I am reading the values for the "MERCHANT TOTAL ITEMS ACCEPTED" row in a footer template so I can attach that data to all of the detail lines that appear above it. Now I am missing the "MERCHANT TOTAL ITEMS ACCEPTED" data, and I cannot capture the "NUMBER UNMATCHED VOIDS" data at attach it to the actual voided transactions. Do you think you can fill me in on one of those other methods you were talking about?

[Grant Perkins]

Originally posted by Scott B:
Grant, I got your message, thank you for the kind words. I wish I could say everything is working fine, but thats not the case now. I had everything working great, until someone pointed out that I was missing a piece of data....

Hmm. Thank heaven I included my normal caveat about "based on the data provided ..." ;)

OK, let me find time to have a look at it overnight and I will see if I can come up with some suggests for your tomorrow.

Did we establish which version of Monarch you have? Knowing might open or close some options.

More soon.

Grant