Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Report with OVER and BY fields in first column
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Report with OVER and BY fields in first column
 Login/Join
 
Member
posted
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,


WebFocus 8.2.02
Windows, HTML, Excel, PDF
 
Posts: 8 | Registered: July 16, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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,


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2192 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
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 #.


WebFocus 8.2.02
Windows, HTML, Excel, PDF
 
Posts: 8 | Registered: July 16, 2018Reply With QuoteReport This Post
Guru
posted Hide Post
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.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 405 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1934 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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.


WebFocus 8.2.02
Windows, HTML, Excel, PDF
 
Posts: 8 | Registered: July 16, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1934 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Member
posted Hide 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.


WebFocus 8.2.02
Windows, HTML, Excel, PDF
 
Posts: 8 | Registered: July 16, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1934 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Yes, you are correct. There will only be 1 record for each unit number.


WebFocus 8.2.02
Windows, HTML, Excel, PDF
 
Posts: 8 | Registered: July 16, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1934 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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,


WebFocus 8.2.02
Windows, HTML, Excel, PDF
 
Posts: 8 | Registered: July 16, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Yes a master and an .ftm. Format ALPHA.
Send to my personal e-mail (see on my profile).


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1934 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Report with OVER and BY fields in first column

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.