I am trying to replicate a PDF report that was created in another reporting tool. The report is in the following simplified format. I have searched through the forums and found that using OVER can get me close, but some of the issues I am facing are trying not to repeat the first column for each unique Unit # and how to include column titles. Any thoughts would be appreciated.

 
Dealer/Contact/Phone  Unit#     Customer/Driver 
Dealer_12345          1         Customer A
Contact ABC                     Driver A
111-111-1111          4         Customer O
                                Driver O
                      7         Customer T
                                Driver T
Dealer 67890          3         Customer W
Contact XYZ                     Driver W
333-333-3333          9         Customer E
                                Driver E

This message has been edited. Last edited by: FP Mod Chuck,

Kopi,

Except if you have the following in you code, by default WebFOCUS doesn't display replicated BY field value

SET BYDISPLAY = ON

or
TABLE FILE
....
ON TABLE SET BYDISPLAY ON
...
END

As for title you just have to put a AS

-* With BY field value repeated
SUM RETAIL_COST AS 'Retail Cost $' OVER
    DEALER_COST AS 'Dealer Cost $'
BY COUNTRY AS 'The Country'
BY CAR AS 'The Car'
BY MODEL AS 'Car Model'
BY SEATS AS 'Nb Seats'
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET BYDISPLAY ON
END

-* With BY field value NOT repeated
TABLE FILE CAR
SUM RETAIL_COST AS 'Retail Cost $' OVER
    DEALER_COST AS 'Dealer Cost $'
BY COUNTRY AS 'The Country'
BY CAR AS 'The Car'
BY MODEL AS 'Car Model'
BY SEATS AS 'Nb Seats'
ON TABLE PCHOLD FORMAT PDF
END

This message has been edited. Last edited by: MartinY,

Hi MarinY,

Thanks for your response. Please take a look at my example again. What I am trying to do is "stack" several fields within a column. For example, I have "Dealer", "Contact", and "Phone" which are all separate fields coming from the database, but I need to stack them OVER each other and not repeat them for each unique UNIT #.

I would try adding the "Dealer/Contact/Phone" in a TX field with carriage return & linefeed. Then I think you can simply do a BY on everything.

Kopi,
I am not quite clear with what you want to achieve.
These are my assumptions.
1. Your database has the following fields:

  
Dealer
Contact
Phone  
Unit#     
Customer
Driver

2. You want to have for each Unit:
in the first column Dealer Contact Phone (do they all exist or can some be absent?)
in the second, Unit#
in the third Customer Driver (same question as for the first column)

Is ths correct?

Daniel,
Yes, you are correct ... just a little clarification. In the first column, the Dealer will always exist. The Contact and/or Phone Number could possibly be absent. In the third column it is possible that either could be absent.

Ok.
How is your database organized? 1 table? 2 tables? More?
For each Unit# you have a Dealer, maybe a Contact, maybe a Phone?
For each Unit# you have maybe a Customer, maybe a Driver, maybe neither?
Do you have a Master File to post?

The report is over an Oracle View (so TABLE FILE VIEW_NAME) where the view pulls data from multiple table sources.

You are correct in all your other statements. The UNIT always belongs to a DEALER. So both are always populated. The CONTACT and PHONE belong to the DEALER and are mostly populated but could be absent. The CUSTOMER and DRIVER belong to the UNIT and are also mostly populated as well but need to handle the possibility of being missing. Again, all of this comes from an ORACLE View so nothing in the MASTER file other than formatting which I take the defaults.

So I can write the following:

  
TABLE FILE view
PRINT 
Unit#     
Dealer
Contact
Phone  
Customer
Driver
WHERE some conditions
ON TABLE HOLD
END

In my HOLD file I'll have a record for each UNIT# (or more than one?) and all the other fields, some with values and some null.
Correct?
I am asking all these questions because I want to reformat the HOLD file with an alternate master.

Yes, you are correct. There will only be 1 record for each unit number.

Ok.
Can you send me an extract of your view (a HOLD FORMAT ALPHA file) with its MASTER? I think I have a solution, using the alternate master technique.

Thank you for all your efforts, Daniel. Do you want the extract as a .ftm? How would you like me to send these to you?

This message has been edited. Last edited by: Kopi,

Yes a master and an .ftm. Format ALPHA.
Send to my personal e-mail (see on my profile).