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     Need help with FML Hierarchy.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Need help with FML Hierarchy.
 Login/Join
 
Member
posted
I am trying to use the parent/child option of the FML report to create an Indented Bills of Material report and running into a problem.

It almost recreates the indented parent/child relationship correctly, but pulls in ALL children that exist even if the parent of that level is not related to the original parent.

For example.

This is what is happening....
Part_Num Parent_Part
0002 0001
..0003 0002
..0003 0016
..0003 0022

This is what I want to happen....
Part_Num Parent_Part
0002 0001
..0003 0002

Not sure if this makes sense, but the idea is that there are 3 records of part 0003, with a different parent related to each and webfocus is returning all the records, where I only want the one returned that has a parent of prior level that called for it.

I would think this the basic idea of how this is supposed to work, no clue why its returning this way. I will provide any more info you need to hopefully resolve this.

We are on version 7.6.2.

Thanks,
Chet


WebFocus 7.6.2, MRE through SharePoint, Win XP/2k3.
 
Posts: 22 | Registered: May 30, 2007Report This Post
Virtuoso
posted Hide Post
Looks like the relationship is not properly defined ( now i sound like a psychiatrist.) It appears that you are showing that part 0003 has three different parents. We would have to look at your hierarchy table and report code to be able determine the problem.


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
Virtuoso
posted Hide Post
About the only FML I do is using Across, but looks as if your parent on the report may be a child in a join structure?


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Member
posted Hide Post
Its a single table I am pulling from. I have setup the table like so...

FIELDNAME=PARENT_PART_NUMBER, ALIAS=PARENT_PART_NUMBER, USAGE=A25, ACTUAL=A25V,
REFERENCE=COMPONENT_PART_NO, PROPERTY=PARENT_OF, $

FIELDNAME=COMPONENT_PART_NO, ALIAS=COMPONENT_PART_NO, USAGE=A25, ACTUAL=A25V, $

FIELDNAME=COMPONENT_PART_DESCRIPT, ALIAS=COMPONENT_PART_DESCRIPT, USAGE=A65V, ACTUAL=A65V,
REFERENCE=COMPONENT_PART_NO, PROPERTY=CAPTION, $


Report Code....

DEFINE FILE DW_BILL_OF_MATERIALS_FIN
CURRENT_TIME/HHISa=HGETC(8, 'HHISa');
ASSEMBLY_QTY_ENTERED/D20.2=&Assem_Qty;
Assem_Qty_Calced/D20.2=DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.QUANTITY_PER * ASSEMBLY_QTY_ENTERED;
END
TABLE FILE DW_BILL_OF_MATERIALS_FIN
SUM
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.ITEM_NUMBER'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.COMPONENT_PART_NO'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.PARENT_PART_NUMBER'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.BUSINESS_UNIT'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.SUBSTITUTE_PART'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.SOURCE_CODE'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.UNIT_OF_MEASURE'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.DELIVER_TO_OPR_SEQ'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.QTY_PER_TYPE'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.Assem_Qty_Calced'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.SCRAP_RATE'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.EFF_START_DATE'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.EFF_CLOSE_DATE'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.COMPONENT_PART_NO'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.PARENT_PART_NUMBER'
FOR
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.COMPONENT_PART_NO'
'&PART_NUM' GET CHILDREN ALL AS CAPTION LABEL R1
HEADING
"Indented Bills of Material"
"Parent Part No: <+0>&PART_NUM"
"Job: <+0>&Job_Num"
"Assembly Qty: <+0>&Assem_Qty"
FOOTING
""
WHERE DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.REPORT_ENTITY EQ '&REPORT_ENTITY.(FIND REPORT_ENTITY IN DW_BILL_OF_MATERIALS_FIN).REPORT_ENTITY.';
WHERE DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.JOB_NUMBER EQ '&JOB_NUMBER';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET FORMULTIPLE ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET BLANKINDENT ON
ON TABLE SET HTMLCSS ON
*more style code after this*

I allow them to enter the starting point of the BoM with a part_num variable. Which seems to work great, because even if I replace this with a hardcoded value it still gives the incorrect results.


WebFocus 7.6.2, MRE through SharePoint, Win XP/2k3.
 
Posts: 22 | Registered: May 30, 2007Report This Post
Virtuoso
posted Hide Post
As I mentioned, it does not appear that your data exists in a completely valid hierarchy (i.e. children can only have a single parent.) According to your post, part 0003 has parents 0002, 0016, and 0022.

If you do the following, what is the result?

SET BLANKINDENT=ON
TABLE FILE DW_BILL_OF_MATERIALS_FIN
SUM PARENT_PART_NUMBER
FOR COMPONENT_PART_NO
0001 WITH CHILDREN ALL
END


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
Member
posted Hide Post
Well bummer. Yea a part can have multiple parents since a part, say a screw of some type, can be used in multiple assemblies builds.

Anyone know another way to handle this then?


WebFocus 7.6.2, MRE through SharePoint, Win XP/2k3.
 
Posts: 22 | Registered: May 30, 2007Report This Post
Member
posted Hide Post
Actually, I have figured it out using SQL instead. Thanks for all the help.


WebFocus 7.6.2, MRE through SharePoint, Win XP/2k3.
 
Posts: 22 | Registered: May 30, 2007Report This Post
Guru
posted Hide Post
Hi Chet,

Do you mind sharing your solution? I need to drill down to the lower levels of bill of materials.

Thanks,

Hua

P.S. Is it true WebFocus can not handle a child have multiple parents?


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Member
posted Hide Post
Yes, It seems to be true from what I have researched, but you can use sql to build a report from, which is what the end result for me ended up being.

Here is an example of the sql and the hold file i put it in to use...

SET EMPTYREPORT = ON
ENGINE SQLORA SET DEFAULT_CONNECTION datawh
SQL SQLORA PREPARE SQLOUT FOR
select replace(substr(lpad(' ',2*level)||component_part_no,1,35), ' ', '&|nbsp&|nbsp&|nbsp&|nbsp') as "component_part", parent_part_number, level + 1 as "level1" , item_number, parent_part_description, deliver_to_opr_seq, component_part_descript, unit_of_measure, source_code, planning_code, qty_per_type, quantity_per * &qnty as "Quantity_Per" , substitute_part, product_code, scrap_rate_reference, report_entity, status_flag, job_number from (select distinct(component_part_no), parent_part_number, level, item_number, parent_part_description, deliver_to_opr_seq, component_part_descript, unit_of_measure, source_code, planning_code, qty_per_type, quantity_per, substitute_part, product_code, scrap_rate_reference, report_entity, status_flag, job_number from dw_bill_of_materials where job_number = '&job_num' and report_entity = '&REPORT_ENTITY' connect by parent_part_number = prior component_part_no start with parent_part_number = '&part_num') connect by prior component_part_no = parent_part_number start with parent_part_number = '&part_num' ORDER SIBLINGS BY &part_or_itemnum;
END

TABLE FILE SQLOUT
PRINT
REPORT_ENTITY
JOB_NUMBER
component_part
PARENT_PART_NUMBER
level1
ITEM_NUMBER
PARENT_PART_DESCRIPTION
DELIVER_TO_OPR_SEQ
COMPONENT_PART_DESCRIPT
UNIT_OF_MEASURE
PLANNING_CODE
SOURCE_CODE
QTY_PER_TYPE
Quantity_Per
SUBSTITUTE_PART
PRODUCT_CODE
SCRAP_RATE_REFERENCE
STATUS_FLAG
HEADING
" "
" "
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS BOM_TEMP
END

I can try to explain it to you if you need more help, but the main part of it is the
"connect by parent_part_number = prior component_part_no start with parent_part_number = '&part_num'"

and

"ORDER SIBLINGS BY &part_or_itemnum"

If I remember right I did an inner select to reduce on the parent part they enter then outer to add the "indention" and correct ordering, either by part number or there own item ordering method, which we allow them to choose, thus the amper var.

Also, I had to setup ways to handle the output. Since HTML only handle the html tab code,
COMPUTE ReplaceTab/A700V = IF '&WFFMT' EQ 'EXL2K' THEN STRREP(840, component_part, 5, '&|nbsp', 1, SP, 840, 'A150V') ELSE STRREP(840, component_part, 5, '&|nbsp', 1, ' ', 840, 'A150V'); NOPRINT

COMPUTE new_component_part/A700V = IF '&WFFMT' EQ 'HTML' OR '&WFFMT' EQ 'AHTML' THEN component_part ELSE ReplaceTab;

Sorry this is so long. =p


WebFocus 7.6.2, MRE through SharePoint, Win XP/2k3.
 
Posts: 22 | Registered: May 30, 2007Report This Post
Guru
posted Hide Post
Thanks Chet.

I will try to translate it into my own file/fields. I am not quite familiar with SQL, but I presume that the nested select clause will drill down to all levels of the indented materials.
When I get stuck, I'm sure I'll get back to you.

Thanks again

Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Virtuoso
posted Hide Post
quote:
P.S. Is it true WebFocus can not handle a child have multiple parents?


Only when using FML functionality. FML was designed to be a hierarchy where a child can only have a single parent.

If you want to go the JOIN route, a child can have multiple parents, but you have to define the relationships yourself.


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
Guru
posted Hide Post
Thank you for your confirmation.

It is unfortunate that FML has this limitation. We have the Bill of Materials, Financial internal/external hierachies where a child can have 2(or more) sets of grandparents.


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Virtuoso
posted Hide Post
Hua

How would that result in the report?

Can you show an example of data?
If this is in the General Ledger system, would that give the same result in two different rows?




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Guru
posted Hide Post
I am more an IT person, can not explain why the accountants want to present the same information in certain ways. There might be a better way to set up our GL hierarchies.
But currently, we have multiple hierarchies in the file, one hierarchy represents the grouping for external financial reports, and internally, the same lower level accounts are re-arranged to rollup very differently than the external one. So for example, we have 4 different hierachies in the file, before FML, we will ask the users to select a hierachy, then we will extract the all the lower levels of accounts within the given hierarchy to produce the appropriate reports.
A hierachy in our file defines a subset of all the accounts in the system.
Can we archieve our needs with single hierachy?


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Virtuoso
posted Hide Post
Hua

We have more or less the same challenge...

I have set up a hierarchy database that has an extra key field for the hierarchy ID.

We have build one report and based upon the hierarchy it creates the total report.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
Hint: use Views.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Guru
posted Hide Post
Pre-defined views? Our temporary solution is to use one view per hierachy. Ideally, I don't want to create new views every time a new hierarchy is added to the file, although it does not happen very often.


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Virtuoso
posted Hide Post
WHERE-based join?


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
We do it the same way as Frank has suggested. The extra field allows me to select only the hierarchy records that pertain to the hierarchy selected by the user. So for a given hierarchy, there is only a single parent. This also requires that the child record exist in the file for EACH hierarchy that may be used.


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
Guru
posted Hide Post
It it nice to hear this can be done. We have the names for the hierachies too.
I am very new to FML, there could be other reasons why our case does not work. Our consultants have sugguested using views to overcome it. I will have to write my own FML reports soon.


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Member
posted Hide Post
Hey Hua,

Not sure where you stand on this at the moment, but I will give a little more information about how we did it through sql and use smaller statements that maybe you might be able to work with. Knowing what fields you use in the tables to create the relationships would help as well. I bring this up because you mentioned a Bills of Material and the topic keeps reverting to FML, which ended up being not what we could use for this.

At the most basic level we use Component_Part_No (child part) and Parent_Part_No (parent part) in a table to show relation.

What you can do at this point is a sql statement like so:

select distinct(component_part_no), parent_part_number from dwadm1.dw_bill_of_materials connect by parent_part_number = prior component_part_no start with parent_part_number = '0001';

If you have a similar child, parent field structure like ours, this should return back only the components parts/parents that roll up into my entered "top" parent part number, '0001'.

(Sorry about the spacing on these paste...)
COMPONENT_PART_NO PARENT_PART_NUMBER
---------------- -------------------------
9336083 0001
25327-2 31251
25327 31251
25328-2 31251
25328-1 31251
25327-6 31251
12525107-1 0001
12525043 0001
12524925 0001
25327-5 31251
8597489 0001
12525045 12525043
12524976 0001
31251RM 31251
25328-3 31251
12525044 12525043
12525032-1 0001
100334-1 0001
31251 9336083
25327-1 31251
29341 31251
25327-3 31251
12526678 0001
12525016 0001
30168 31251
25327-7 31251
X12525043 12525043
25328-4 31251
25328 31251
25327-4 31251
12524882 0001

31 rows selected

Now, to prove that this is working like we think, lets use the same statement with one of the child parts from 0001, that is also a parent with children. We see from the list that part '12525043' is a child of 0001 but also will be a parent in the BoM structure.

select distinct(component_part_no), parent_part_number from dwadm1.dw_bill_of_materials connect by parent_part_number = prior component_part_no start with parent_part_number = '12525043';

COMPONENT_PART_NO PARENT_PART_NUMBER
------------------------- -------------------------
12525045 12525043
12525044 12525043
X12525043 12525043

3 rows selected


So now using the same SQL statement, I only getting back the parts that are a child of this part.

The key to all this is that at any time, one child part could have multiple lines with different parents. This sql eliminates that issue.

This is basic reduction of the parts I use in the inner select before I start creating the "indented" look with the outer select. If you try this and it works for you, I would be glad to continue walking through the rest of the process.

The end result for us on all this is that we have an Indented BoM that our users can view through all the webfocus output options and it is VERY fast, which is always a big deal with reports.

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


WebFocus 7.6.2, MRE through SharePoint, Win XP/2k3.
 
Posts: 22 | Registered: May 30, 2007Report This Post
Member
posted Hide Post
I forgot to add that we use zero views to help us limit/structure this before using the sql. In case you were curious.


WebFocus 7.6.2, MRE through SharePoint, Win XP/2k3.
 
Posts: 22 | Registered: May 30, 2007Report This Post
Guru
posted Hide Post
Chet,

My feet are on two boats! I could drown myself, iterally, drown with all the information flooded in. But I truely appreciate everyone's help.

We got the Webfocus not long ago. So we have to write quite a few financial reports as the priority. And I have another project to monitor the daily cost changes in row materials or purchasing parts that affect any tractors models we make, before the cost rollup(done once a week). The more we talk about FML, the more I feel this is not applicable to the BOM. FML seems like another excel spread sheet allows you to do any row/column manipulations anywhere you want. I wish there is a stand alone command or function such as WITH ALL CHILDREN...

You already gave me a good starting point. I need to get my head straight as how to present the information, as you know for sure, each tractor has thousands of parts, some parts appears in various assembly parts...
Because I don't know WF enough, I feel like program everything in RPG, and do the final reporting in WF.

Thanks.

Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report 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     Need help with FML Hierarchy.

Copyright © 1996-2020 Information Builders