Full page detail or append?

[rbrohman]

Hi,

New user working on my second model in Pro v10.5. Each page in the source report represents a single employee. Each page (employee) is in some ways unique, as not all employees work overtime, have the same deductions/benefits, etc. What I'm trying to do is build an export table that would have all the various details for each employee on a single line.

A screenshot of my report:

http://www.screencast.com/t/ZTI3ZGZkY2Ut

To do this, I've identified the employee name/address block as the detail template, and then have created an append template for each additional line. For example I created a trap that looks for "Regular" and then defines fields for Rate, Current hrs and Current amt:


http://www.screencast.com/t/MWE0OWNiOT


But the result has been that these will append to the next detail, rather than the current one. So my first employee comes up with a bunch of nulls, and the amounts for the first employee get attributed to the second, and so on. Not only that, but it took a heck of a lot of templates (each line is a new template).


What is the correct way (the best practice) to achieve what I'm after here?


Thanks,

Ryan

[rbrohman]

Just found another thread with similar issue - now reading up on the "Guru Trap", as I think this is exactly what I'm after...

[rbrohman]

I've been reading a bunch of threads about the Guru trap, but I'm unsure how to apply this to my model. A nudge in the right direction would be greatly appreciated.

Thanks

[OllyInMunich]

Hello Ryan,

It really does depend on how irregular the records are. You can solve the first issue by using footers instead of appends. This would work 100% if every page had had data that matched very template. Otherwise, Employee A would end up with footer data from the following record of Employee B.

To avoid this, you can use four approaches:

1. Guru traps.
2. Multiple passes joined on the page number.
3. Grab one big memo field from a single template and define some calculated fields.
4. Using Monarch v10, make the detail template at the bottom of the page, and turn off append templates at the next occurrence of a page header template.

It's largely a matter of personal preference - (3) is usually a quick and efficient route to prototype the data with.

HTH

Olly

[rbrohman]

Thanks, Olly.

I was able to switch to using an append template at the bottom of each page as the Detail and my Append fields are now associated with the correct employee.


As I continued on with the model though, I hit the 20 Append Templates limit. Hitting this makes me really think I'm doing something wrong with trying to define so many separate appends. But I'm at a loss for an alternative.


This screenshot may help me explain my question better:

http://www.screencast.com/t/ZTViNTFiNWQ

ormation?
Each employee will have the "Regular" row, with the pay rate, hrs worked, and amount fields. Most will have the "OVT 1.5" as well. But the "Temp Upgr", "Retro Pay" and a handful of other pay codes not shown here are splashed across some, but not all employees. Same goes for various benefit and deduction codes.

I need to be able to pull the Rate, Hrs and amount fields for all of these various codes. I had been trying to create each line as a separate Append Template, with the trap being on the paycode descriptor. I then set the templates to "Clear on...." the detail template.

What is the better way to build these templates, given that each page of the report will have a different set of pay, benefit and deduction codes to be captured?


Thanks again,

Ryan

[Data Kruncher]

Like many others who've come before you Ryan, you've been forced to dive into the deep end of the pool right from the start.

Based on the screenshots that you've so nicely formatted and posted, I'd be tempted to go with a combination of Olly's suggestions numbers 1 and 2.

Try breaking up the modeling process. One model for "header" type data (seniority, birthday, that stuff,), another for the earnings block and another for the deductions block. Each of these may employ guru traps to account for now-you-see-it-now-you-don't data. Export each of these to temporary working files which will be compiled with a final model which opens the first export as a database source and employs external lookups to add the remaining data from the other exports.

I have a particularly ugly customer master report that shares aspects of your report and benefits from this divide and conquer approach.

HTH,
Kruncher

[Grant Perkins]

Ryan,

Further to the excellent advice already offered by Olly and Kruncher ...

It looks like the report has at least some lines in each 'block' of employees data - Payments and deductions.

You can therefore work with bliocks of data fields - maybe all the fields in a single block - just depends how many unique fields you may need to extract and how few lines the smallest length record has available.

So if you can make the data sample fo each append template, say, 20 lines long and you need 20 possible named non-overlapping (in terms of horizontal position on the line) fields from one or both columns of the record, you could do that with oe template. If the shortest number of lines in a record was, say, 10, you would need 2 templates.

The data sample itself is of no importance here (unless you are using a floating trap in which case all bets are off.) The number of lines simply defines the max number of rows you can fit in without overlapping the next record down the page and provides space for painting the fields you nee in their horizontal line position. Any order will do unless there are shared parts of preceding strings in the field name tags - in which case some care may be required.

If you define a field that may fall outside the number of lines in the sanmple, but before the next record starts (or any end template marker you may have set in V10), the field will still be picked up.

Does this information allow you any additional flexibility with the basic guru trap process?

HTH.


Grant