Hi Gerard Since Europe is about 2 hours from the new year I’m sure Olly Bond cant respond. Let me give it a try as a user but no expert. Try and make your detail trap TWO lines. So trap 13 08 XXXXXXX 1.0000 44.86 44.86 but also include the line below in the detail trap. Therefore include the subtotal ACCOUNTING AA.AAAAAA 585.7 as part of the detail trap as an example Hope that helps . Best John
Thanks for the response John.
That didn't work, if I am understanding you correctly that is?
The amount of lines above the sub-totals are variable and could have a hunded lines and then sub-total, then the next group and so on, until finally grand totalling.
Using your method (which I had previously tried), I was unable to get a consistent approach to grabbing the sub-totals and ignoring the grand total - it was getting most sub-totals, but not all for example.
Like Mo, I'm a little unsure about the requirements given the small sample. I'm assuming that the only data you need from this report is contained in the Sub-total lines and that this is in effect your Detail line. Am I correct in this assumption? If this is so, you should be able to capture just the Sub-total lines using a two-line Detail template as follows: (Image pasted below)
Select the line above the Sub-total line and the Sub-total line itself.
Trap on some pattern in the top line - say two numerics at the start of the line. It doesn't matter once it creates an unambiguous trap.
Select the fields you need on the second line (the Sub-total line).
Save your template
Verify your tempate - always a very important step!
In the Table window, you should see the data you require. This should exclude the Grand Total line as it will not satisfy the trap condition.
Please forgive me if I've misunderstood your requirements. But if I read your spec. correctly, this should work.
Create a detail template with three lines, and make the third line as the trap line.
Define the subtotal field on the third line, and another field on the first line. On the first line, I called the field name Flag, and defined it as a character field. In the Table design, create a filter where the Flag field is not empty (flag<>"").
Apply the filter and you end up with three rows that contain the subtotal fields:
The above model is done in Modeler v12.
Mo Just a thought, what about Gerard’s comment “The amount of lines above the sub-totals are variable and could have a hunded lines and then sub-total, then the next group and so on, until finally grand totaling." How can you trap all the details if your detail trap is on the third line containing the subtototal. Most of detail based on Gerard’s comment does not have an immediate subtotal located on the third line. It works based on his sample but not with “hunded lines and then sub-total”?? Would my approach work. Capture the details on a one line detail trap and then capture the subtotal separately as a footer template. Since the grand total is below the subtotal and footer template trap. The grand total would be ignored because it’s below the footer trap and its associated details. Is this correct?? Thanks John
It would be better if the sample data contained all possible scenarios so I could come up with a better traps. Based on the example that Gerard provided, both the sub-total lines and grand total line are identical. I made an assumption that the third line above the sub-total line has data, and there is no data on the third line above the grand total. He needs to inspect lines above the sub-total line (or the grand total line) and find some data that is unique and does not exist above the grand total line, and use it as filter.
As far as your second suggestions goes I am not sure if it works. I would suggest to create a test case and test it.
Mo, Gerard has a solution along with multiple ways to trap. Using my last approach, capturing the subtotal as a footer template works. It ignores the grand total and will capture all the details, multiple details lines with one associated subtotal. The first approach like your response captures the subtotal but only one line of detail immediately above the subtotal. My first solution does not need a filter to remove the grand total. So using my second approach (footer trap for subtotal) will capture all the details with the associated subtotal without capturing the grand total. Mo, thanks for having me test, it was helpful to see if it worked. Regards John
There's a downside to using multi-line templates which is that page breaks might interfere, giving you 2 or 4 lines instead of 3 between the detail and the subtotal. There's a workaround for that using "preceding string" in the advanced field options, if you always have "Accounting" in the subtotal line. Define a 2 line sample, trapping on line 1 using the detail, and in line 2, select a field that lines up vertically with the subtotal data you need. Edit this field's properties so that it is selected when "accounting" occurs as a preceding string.
A filter for non-null subtotal values should then give you the correct results.
Thanks ever so much for all the responses - it is greatly apprecaited!
The two line trap was looking to have been working perfectly - but as Olly suggests I noticed that whena subtoal followed a page break it could treat it as a grand total as it had a null field preceding it.
I want to get your suggestion working Olly, but due to my in exeperience I don't quite get you sorry.
From original example below -
13 08 XXXXXXX 1.0000 13.33 13.33
ACCOUNTING AA.AAAAAA 585.77 < Need this subtotal line
13 08 XXXXXXX 1.0000 44.86 44.86
ACCOUNTING AA.AAAAAA 44.86 < Need this subtotal line
13 09 XXXXXXX 5.0000 .34 1.70
ACCOUNTING AA.AAAAAA 1.70 < Need this subtotal line
ACCOUNTING BB.BBBBBB 11006.84 < IGNORE this total line
I am selecting two lines and on line one trapping by saying if the line has ACCOUNTING in the relevant position - when I move to line 2 I am unsure which field to apply the preceding field rule to (or which field in the Advanced tab should I use)? I'm using Monarch 11.
Sorry for taking up your time.