 |
|||||
|
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
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. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| Master detail report in HTML | Login/Join |
| Gold member |
Hi, I'm trying to create a new HTML report for our purchasing department and I'm not sure how to go about it. I need to somehow make a report using separate SQL tables without drilldowns. I have 4 tables I need, a purchase_orders, purchase_items, purchase_contracts and purchase_actions. Each table uses a key that is a PR Number. I would have normally used the SQL to join purchase_orders and purchase_items, then use group bys, but that leaves other tables. And I don't know about joining actions to that, because some orders have dozens of items and dozen of actions (wouldn't each action be joined to each item?). So, how should I do this? WebFOCUS 7.6.3 | Solaris 10 | Excel, PDF | ||
|
| Expert |
I would be inclined to create each as a separate report, held FORMAT HTMTABLE with stylesheets based on CLASS with the whole lot encompassed in a single HTML page using an included CSS file. A brief idea along the lines of - TABLE FILE CAR SUM RCOST BY COUNTRY WHERE COUNTRY EQ 'ENGLAND' ON TABLE HOLD AS REP1 FORMAT HTMTABLE ON TABLE SET HTMLCSS ON ON TABLE SET PAGE NOLEAD ON TABLE SET STYLE * TYPE=DATA, COLUMN=RCOST, CLASS=style1, $ ENDSTYLE END etc. etc. etc. -HTMLFORM BEGIN <html><head><title>Some title</title> <link rel="STYLESHEET" type="text/css" href="URL for your css file"> </head><body> !IBI.FIL.REP1; etc. etc. etc. </body></html> Obviously you would have more code around this but it gives you the idea. T
| |||||||||
|
| Gold member |
Yes, but I'd like to group all of the data I mentioned above by PR. Wouldn't something like that list all the orders, then all the items, then all the contracts, items, so on, much like a compound report would? WebFOCUS 7.6.3 | Solaris 10 | Excel, PDF | |||
|
| Virtuoso |
Are you asking how to write the code for the WebFOCUS JOINs to join the four tables (a technical question), or are you asking someone to tell you how the four tables are related (a business question)? WebFOCUS 7.7.05 | |||
|
| Expert |
How about an accordian report? 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 | |||
|
| Expert |
It could do, depending upon how you wrote the report(s). As Ginny suggests, an accordian report might be what you need, but I suspect that your real question is about data retrieval and perhaps not display? e.g How do I get the data all at once? For that you need to have specific data relationship awareness and perhaps use a multi verb process preceded by MATCH processing? Only specific knowledge of your data relationships would allow you to move forward on this(?) so if I were you I'd start with an ERD and work forward. T
| |||||||||
|
| Silver Member |
Tony, I think that you are right on the money. The question appears to me data extraction related. An ERD would certainly help us understand if "we" should be attempting to execute one piece of SQL with a "four way" JOIN or whether it might be more effecient to execute one or more queries to extract the data into HOLD files before populating the reports. The other concern that I have is just how much data and how often is all this data is required ON THE SAME report at the same time. Might this be an overkill. It is more than likely to be IO intensive. I sense (and possibly wrongly) that the author is trying to create a "dashboard" effect. Hey just a few of my thoughts. regards Steve Simon WebFOCUS 7.7.01 Windows, TSO 7.0 | |||
|
| Gold member |
No, I'm asking about the joins. I know how the four tables are related (the PR number key).
Yes, yes! This is what I want to do. As for the others questions, I don't see how I can get all the data into one file to be used by the report. The issue is that I don't see how I can use a traditional SQL join, or a focus join for that matter -- that is, I start with all my purchase orders, then join all the items, then join all the actions to that -- the joins would be gigantic, much too big because there are thousands and thousands of purchases in my database, each with anywhere from 1-200 items, anywhere from 1-20 orders, etc.
You would be correct. This report also uses an HTML form where I can search for types of purchases, buyer names, item names, etc. I need some way to have multiple purchases show up in the report (which isn't the hard part), and then be able to view all of the orders, items, contract data (charges) and all of the buyer actions without using drilldowns. Any examples are appreciated... Thanks. WebFOCUS 7.6.3 | Solaris 10 | Excel, PDF | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |