Multiple level report giving me a headache

[steviev]

Hi Guys,

I'm unfortunately stuck again. I have below report and need to extract from each level certain details.

- Level 1: From Office
- Level 2: Confirmation Number
- Level 3: Color
- Level 4: Which tickets received and in transit

I thought I had the solution but it seems to be skipping information on the level 4 (problem I guess is that level 4 is not always shows. Level 3 states how many are received or in transit. If >0 then the details are shown in Level 4).

Am using Pro 9.01


------------------------------------------------------------------------------------------------------------------------------------

REPORT1 TICKET REPORT PAGE NBR: 2

------------------------------------------------------------------------------------------------------------------------------------
CLIENT: A00001 FROM OFFICE: US ROUTE#: 1
------------------------------------------------------------------------------------------------------------------------------------

CONFIRMATION NUMBER : 008

----------------------------------------------------------------------------
| COLOR | BOOKED | RECEIVED | IN TRANSIT |
| 20 | 014 | 000 | 000 |
----------------------------------------------------------------------------

CONFIRMATION NUMBER : 009

----------------------------------------------------------------------------
| COLOR | BOOKED | RECEIVED | IN TRANSIT |
| 20 | 002 | 000 | 002 |
----------------------------------------------------------------------------

---------------------------------------------
| RECEIVED TICKETS | IN TRANSIT TICKETS |
| | APLS285948 |
| | TGHU212510 |
---------------------------------------------

(CONTINUED TO NEXT PAGE .....)

------------------------------------------------------------------------------------------------------------------------------------

REPORT1 TICKET REPORT PAGE NBR: 3

------------------------------------------------------------------------------------------------------------------------------------
CONFIRMATION NUMBER : 010

----------------------------------------------------------------------------
| COLOR | BOOKED | RECEIVED | IN TRANSIT |
| 40 | 001 | 000 | 000 |
----------------------------------------------------------------------------

[Data Kruncher]

Hi Stevie,

You have another candidate for the "guru trap" on your hands, I think.

Here's how I was able to model this one.

Since your level 4 items don't always appear, you can't use that as your detail template without dropping the other values. You wouldn't capture color 40 if level 4 was your detail, in other words.

So instead we'll use Color as the detail template. I used a two line sample with the word Color as the trap.

Add to that two simple append templates for each of the level 1 and level 2 items.

Now the (somewhat) tricky part.

Highlight the two color lines again, say for color 20, then create a new append template. Again use the word color as the trap, exactly as done with the detail template.

Now highlight the two lines in the report for the IN TRANSIT TICKETS - the heading and the first number. Paint the field for the first number, name it InTransitTickets and go to the advanced tab.

Specify "in transit tickets" (without the double quotes) as the string anywhere in the previous line. Now End Field On End of Left Justification.

Use a similar approach for received tickets.

Now in the Table window you'll have the correct data capture for each record, but we need to break out the individual In Transit Ticket numbers. Create at least three calculated fields (you may need more depending on your real data in the report. In Transit Ticket 1 will have the formula:
Strip(Textline([In Transit Tickets],1),"-")

Change the value of 1 to a 2 and a 3 for each of In Transit Ticket 2 and In Transit Ticket 3 respectively. This will isolate the ticket numbers and clean up the dashes from the box border that get captured.

Create similar calculated fields for the Received Tickets and you're done.

Does this solve the challenge for you? Are there any other complications in the full report that require changes to this approach?

HTH,
Kruncher

[steviev]

Hi Kruncher!

Thanks for your help! I'm almost their but have two issues.

Is it possible to get the Level 4 (received tickets and tickets in transit) items displayed vertically? So the first levels are repeated and only 1 ticket no. is shown in received container and container in transit column. Now I need to add a lot of fields horizontally but it is not possible to predetermine the max number of calculated fields.

If the level 4 is not shown in the report the table seems to display the next level 4 occurance for the non existant level records.

Hope you can assist? :)

Thanks anyway for all the help sofar.
Stevie

[steviev]

I managed to fix the horizaontal to vertical with some small VBA in Excel however just saw that the LEVEL 4 records seem to display the previous LEVEL 4 and not the current LEVEL. I probably did something wrong with you tricky part but can't see what the issue is? Please can someone assist?

[OllyInMunich]

Hello Stevie,

You might get the vertical result you want by using a summary. For the fields you don't want repeated, set the display option to "suppress duplicate values".

HTH,

Olly

[steviev]

I have been able to extract all the data I need by using above Guru Trap but am seeing that for one reason the very first record always seems to be skipped. The rest works perfect. Is this something that is normal with this solution?

[Grant Perkins]

Unless for some reason the first record in the report is presented differently - highly unusual but not unheard of - the trap should work just fine for the first record if it works for the others.

One possible problem might be if you have a Page Header template in use. First pages can often have a little different page headers to the rest o fthe report. If the data sample for he header template has, say, 1 line more than is required for the first page's header it may overlap the first real detail record trap. That would make the detail trap 'invisible' for that line - an intended function of the Page Header template - and produce the anomaly you see.

Assuming the the first record appears to be trapped according to the field shading when you are defining the template the page 1 Page Header variation is the only frequently observed anomaly that springs to mind in this context. That is not to say there are no others, but this one is more often observed.

If the first record is not trapped than perhaps the first page has a variant format compared to you Detail trap defined in the template? Sometimes the variation can be well camouflaged.

HTH.


Grant