Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] WebFOCUS - One Master Record - Multiple Detail type Records Report

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] WebFOCUS - One Master Record - Multiple Detail type Records Report
 Login/Join
 
Silver Member
posted
Hi All,

Nice to meet you all, This is my first forum.

I have a requirement for designing a WebFOCUS report in Master/Detail format as mentioned below. Can anyone help me on this.

Master Record Heading
Master Record1 (Oracle table1)
Detail Type1 Sub Heading (Oracle table2)
Detail Type1 Reocrd1
Detail Type1 Reocrd2

Detail Type2 Sub Heading (Oracle table3)
Detail Type2 Reocrd1
Detail Type2 Reocrd2
Detail Type2 Reocrd3


Detail Type3 Sub Heading (Oracle table4)
Detail Type3 Reocrd1
Detail Type3 Reocrd2

Master Record2 (Oracle table1)
Detail Type1 Sub Heading (Oracle table2)
Detail Type1 Reocrd1
Detail Type1 Reocrd2

Detail Type2 Sub Heading (Oracle table3)
Detail Type2 Reocrd1
Detail Type2 Reocrd2

Detail Type3 Sub Heading (Oracle table4)
Detail Type3 Reocrd1

Master Record3 (Oracle table1)
.
.
.

Thanks,
Vadivel Nagaraj

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


WebFOCUS 7.1.4
Windows
Both Excel & HTML
 
Posts: 35 | Registered: July 25, 2009Report This Post
Expert
posted Hide Post
Are Oracle tables 2,3, and 4 of the same format?

The basic answer is to join Master 1 to Oracle N, then sort the data by master keys then detail keys. On master sort break, do a subhead. Make the rest of the report detail.

If all the Oracle tables are the same, after each join, extract the columns you need into a hold file. Then use MORE or Universal Concatenation to combine them and do the report as stated in paragraph 2 of this post.

Does this get you started?


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Silver Member
posted Hide Post
Hi Ginny,

Thanks for looking into it,

The oracle tables 2,3,4 are different formats and have different columns. Also, the records in each detail table are different. If all the tables are equi joined, for some of the tables the records are repeated/duplicated as the records counts are different in each detail table.

Brief Details on the Table,
Table1 - Shipment
Table2 - Shipment Parties
Table3 - Shipment Party Items
Table4 - Shipment Discrepancies

Thanks,
-Vadivel Nagaraj


WebFOCUS 7.1.4
Windows
Both Excel & HTML
 
Posts: 35 | Registered: July 25, 2009Report This Post
Expert
posted Hide Post
Can you give an example with date of what the output is supposed to look like?


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Silver Member
posted Hide Post
Shipment Header Details(Oracle Table1)
Shipment_id Shipment_Name ETD ETA LoadDate
S100 Shipper Date1 Date2 Date3

Shipment Party Details(Oracle Table2)
Shipment_id Party_id Party_Name Party_type_code
S100 P100 XYZ Manufacturer MF(Manufacturer)
S100 P101 ABC Buyer BY(Buyer)
S100 P102 123 Seller SE(Seller)

Shipment Item Details(Oracle Table3)
Party_id Item_id Item_Name Department
P100 I100 I100Name D1
P100 I101 I101Name D2
P100 I102 I102Name D1

Shipment Discrepancies(Oracle Table4)
Shipment_id Discp_id Discp_Text
S100 D100 Invalid ETD
S100 D101 Invalid ShipmentName
S100 D102 Item I100 Inlavid



Shipment Header(Oracle Table1)
Shipment_id Shipment_Name ETD ETA LoadDate
S101 Shipper Date1 Date2 Date3

Shipment Party Details(Oracle Table2)
Shipment_id Party_id Party_Name Party_type_code
S101 P103 XYZ Manufacturer MF(Manufacturer)
S101 P104 ABC Buyer BY(Buyer)
S101 P105 123 Seller SE(Seller)

Shipment Item Details(Oracle Table3)
Party_id Item_id Item_Name Department
P103 I103 I100Name D1
P104 I104 I101Name D2
P105 I105 I102Name D1

Shipment Discrepancies(Oracle Table4)
Shipment_id Discp_id Discp_Text
S101 D103 Invalid ETD
S101 D104 Invalid ShipmentName
S101 D105 Item I100 Inlavid



.

.
.
.

Please help on this.


WebFOCUS 7.1.4
Windows
Both Excel & HTML
 
Posts: 35 | Registered: July 25, 2009Report This Post
Silver Member
posted Hide Post
Please share your ideas, Your help on this is much appreciated.


WebFOCUS 7.1.4
Windows
Both Excel & HTML
 
Posts: 35 | Registered: July 25, 2009Report This Post
Virtuoso
posted Hide Post
Have you tried ANYTHING yet to try and solve the problem, or are you waiting for someone else to do it?

Nothing personal, but this sounds like a request for someone to write your code for you more than a request for help with a problem. I'm sure there are plenty of WebFOCUS consultants needing work and $$ who would be happy to help.

I understand that this is your first time here, but the Forum is more to help those who are trying to accomplish things by themselves but are having a particular problem, more than a place to post you report requirement and ask for someone to write your report.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Silver Member
posted Hide Post
Hi Darin,

I developed a report with master and single detail, but when i enhanced the same for multiple details, i found it is bit challenging. Please don't misunderstand, I am looking for the guidance and not for the code. Please share your ideas if you know.

Thanks,
Vadivel Nagaraj


WebFOCUS 7.1.4
Windows
Both Excel & HTML
 
Posts: 35 | Registered: July 25, 2009Report This Post
Virtuoso
posted Hide Post
You might want to try a coordinated compound report with the summary information as the first report and the detail report as the second. The coordination feature allows you to collate the two reports together by a primary sort field.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Expert
posted Hide Post
If you look at the report, the data from Oracle 3 is a subset of Oracle 2, etc. But you want in your report to print, for example, the shipment id from Oracle 2 in the SAME column as the party id from Oracle 3. So with defines or MacGyver or something or both, you will have to force the same column data from the different tables into a field designated for column 1, column 2, etc. You have to homogenize your data to get it to come out the way you want it.

This is a lot of work but very doable.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Silver Member
posted Hide Post
Ginny,

It is not necessary to print under the same columns. As long as the details are grouped under the corresponding sub heading and under the corresponsing master, it should be fine. Darin Mentioned that it can be done thru coordinated compound report, I will try with it.

Thanks,
Vadivel


WebFOCUS 7.1.4
Windows
Both Excel & HTML
 
Posts: 35 | Registered: July 25, 2009Report This Post
Silver Member
posted Hide Post
I completed this report.

Thanks


WebFOCUS 7.1.4
Windows
Both Excel & HTML
 
Posts: 35 | Registered: July 25, 2009Report This Post
Gold member
posted Hide Post
Hi Nagaraj,
Iam trying to do the same report. Would You Please post the sample code on how you did this.

Thanks,
Ram.


WebFocus 7.7.01
Desktop: Windows and Server: Unix
Excel, HTML, PDF
 
Posts: 69 | Registered: September 12, 2009Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] WebFOCUS - One Master Record - Multiple Detail type Records Report

Copyright © 1996-2020 Information Builders