2 Section report help

[t1machado]

Hi Everyone,

I've just started working with Mornarch/Datawatch and am having a problem creating a model for a report. I have this 11,000+ page report that I need to strip out 4 or so pages towards the top of the report and then the last 20 or so pages (length is variable so I can't trap any fixed amount of pages, etc). So far I've been able to get all the data I need, but the problem is I can't get rid of the data I DONT want. Currently I break out the reports by type of report (which i'll explain below), but the problem is that I get all the junk from the top of the report to the sections I want, then the data I want, then the bulk of the report I dont want, and finally the last 20 pages broken out correctly.

For us visual learners:

Report1: Top of report data I dont want + first correct report
Report2: second correct report
Report3: third correct report
Report4: fourth correct report + 1100 pages of data I dont want
Report5: 5th correct report (start of bottom 20 pages I want)
Report6: 6th correct report
Report7: 7th correct report
Report8: 8th correct report


So it looks like I need to find a better way to break the pages out to eliminate the data I dont want.

Below is an example of the first page of the report (data I dont want) (numbers and text changed for confidentality):



1 UGAP110 UNIVERSITY OF xxxx PAGE: 1
RUN ON: 10/04/08 AT 04:51:42 xxxx GENERAL LEDGER SEPTEMBER
xxxx CURRENT MONTH TRANSACTIONS
FOR THE PERIOD 09/01/08 - 09/30/08
ACCOUNT: 555555 INTERFUND CASH ACCOUNT Assets - Cash
DOCUMENT TRANSACTION DOCUMENT RULE/
FUND DESCRIPTION REFERENCE DATE NUMBER TYPE DEBIT CREDIT BALANCE
-------------------------------------------- ----------- ----------- ---------- --------- ----------------------------------------------------------
55555Z BALANCE FORWARD .00 5,555.55
55555Z CURRENT MONTHS ACTIVITY .00 555.55
------------------ ------------------ ------------------
TOTAL 110000-55555Z .00 5,555.55 5,555.55CR

55555Z BALANCE FORWARD .00 555,555.55
55555Z CURRENT MONTHS ACTIVITY 55,555.55 555,555.55
------------------ ------------------ ------------------
TOTAL 110000-55555Z 55,555.55 555,555.55 555,555.55CR

55555Z BALANCE FORWARD 55,555.55 .00
55555Z CURRENT MONTHS ACTIVITY 5,555.55 .00
------------------ ------------------ ------------------
TOTAL 110000-55555Z 55,555.55 .00 55,555.55

55555Z BALANCE FORWARD 555,555.55 .00
55555Z CURRENT MONTHS ACTIVITY 55,555.55 5,555.55
------------------ ------------------ ------------------
TOTAL 110000-55555Z 555,555.55 5,555.55 555,555.55

------------------ ------------------ ------------------
TOTAL 110000 555,555.55 555,555.55 555,555.55CR



Here is the first section of desired data:



1 UGAP110 UNIVERSITY OF xxxx PAGE: 39
RUN ON: 10/04/08 AT 04:51:42 xxxx GENERAL LEDGER SEPTEMBER
zzzz ACCOUNT SUMMARY
FOR THE PERIOD 09/01/08 - 09/30/08
ACCSUM
Assets
CURRENT FISCAL
ACCOUNT TITLE MONTH YEAR TO DATE
------------------ ------------------
555555 INTERFUND CASH ACCOUNT 55,555.55CR 555,555.55CR
555555 PETTY CASH FUND .00 555.55
------------------ ------------------
TOTAL Assets 55,555.55CR 555,555.55CR



And the second piece of data I dont want is identical in structure to the first piece as well as the bottom 20 pages are identical in structure to the first piece of data I do want. The only difference from the first piece of wanted data to the second is that "zzzz ACCOUNT SUMMARY" (in the header) is "xxxx ACCOUNT SUMMARY", but other than that the header is exactly the same in both desired data pieces.

Some traps i've employed:

* trap for "ACCOUNT SUMMARY" and capture the "xxxx" or "zzzz" part (used for the report title)

* trap for "ACCSUM" in header

* trap for "Assets" (well really the trap is any alpha character where the "A" is in assets are the reports I want only are the ones that have text in that position) - this is what I use for my report breaks.

* my details view consists of capturing the account, title, and both decimal points (notice how they are differently aligned in the two examples).

Again I capture all data correctly and MY TABLE VIEW IS EXACLY HOW I WANT IT but the report does not come out as I would like it.

Any help would be greatly appreciated. Let me know if you need more information.

Thank you,

Tom

[Data Kruncher]

Hi Tom,

Am I correct in thinking that you're only interested in including the records for the group totals, like "Assets", and you don't want the records with the account number level detail, like "110000-55555Z" in the table?

You can filter out the numbered accounts, assuming that they all begin with a digit in the first position, with this expression:
IsAlpha(Left(ActDesc,1))

Numbers will cause the IsAlpha function to return a FALSE boolean value (0), so they won't be returned by the filter.

Sorry if I'm oversimplifying. Not feeling so good today, and the cold medicine might be playing with me.

[t1machado]

Thank you for your response.

Really what I am looking for are specific pages in the report. I dont want of the information in the first example at all but want everything in the second example, including the "TOTAL Assets" row.

As I mentioned i'm capturing all the right data in my table view, but trying to view it through Datawatch all of the rows that aren't in my table still appear in the reports as described in my first post.

Sorry if i'm being confusing. I think this is probably more of a Datawatch problem with setting up my document type then a monarch modeling question...

[Grant Perkins]

Tom,

You write that the TABLE is exactly how you want it but that the REPORT does not drop the pages you do not want. And you also mention potential problems differentiated between Monarch and 'Datawatch'.

Are you by any chance using Datawatch ES? (Or RMS)

Or are you Printing/Exporting the REPORT rather than the Table? If you are exporting a Table that looks correct on screen when 'filtered' by a Trap mechanism and get the additional unwanted pages back I would be very puzzled. On the other hand if you are extracting all pages to the table but filtering the table to include only the desired pages then it sort of sounds like your export (whatever method) is not applying the filter. (By which I am suggesting a genuine FILTER using the pre-extracted fields rather than a 'trap filter' effect.

Now, if you are using ES then, in the absence of any alternative processing before adding to the archive, the original report will be the original report but the data presentations for the prepared analyses will be the tables/summaries, etc., that have been pre-prepared using the Monarch component.

If you need to pre-process the report to remove some pages (assuming the page numebrs and the number of pages to be removed are not constants for all report runs) then one option I have used would be to extract every line of the report as a complete line (easily done) and then find a way to filter out the pages/lines that you don't want, displaying the result in a table with very wide fields. If you then export or print that table to a new file you will get a report that can be processed directly to a table or summaries having had all the unwanted pages removed.

A basic manual version of the pre-process might involve assessing the report visually to decide which pages to reject. Enter the page numbers in a calculated field that requests User data input and use that input in conjunction with another calculated field created using the PAGE() function to filter the pages to be included in the table.


My apologies if I am reading too much into the information in your posts but you seems to have good success in extracting the information you want from the report to the table and that leaves me a little puzzled about the problems you have with the output for the Report.

HTH.

Grant