I have a bills of material report that accepts a quantity for how many of a part they want to build.

The idea of a bills of marterial report, for those unaware, is that it tells you what is required to build the part that they enter as a amper value. The main part entered is the first level, the next level are the parts required to build that first part, if those parts have another level, then those are the parts required to build them and so forth and so on.

The quantities in the tables are what are needed to build 1 part based on the parent/child relationship, but the engineers need the reports to show them how many of each part they need if they want to build more then 1.

So lets say the report to make 'part1' looks something like this.

Level Part Qty needed
1 part1 1
2 part2 3
3 part3 2
3 part4 .5
4 part5 3
4 part6 4
3 part7 .25
2 part8 3
2 part9 2
3 part10 6

If they enter into an amper variable a quantity of 10, then the report needs to calculate each row to show how many of the other parts are needed to make part1, the problem is that you can't just multiply each row by 10, since each levels quantity is based on the level before it.

So the output should be this. (I also show the incorrect results we are currently getting)

Level Part Qty needed 10 Qty (Correct) 10 Qty (Incorrect if just multiplied by entered qty)
1 part1 1 10 10
2 part2 3 30 30
3 part3 2 60 60
3 part4 .5 15 5
4 part5 3 45 30
4 part6 4 60 40
3 part7 .25 7.5 2.5
2 part8 3 30 30
2 part9 2 20 20
3 part10 6 120 60

I was thinking maybe an amper array holding the last levels values before the current level would work, but I don't know how to manipulate an amper value while a report is running. The idea is that I can start popping off the values of the array as I make my way back down levels since I will still need to know what value was to generate level 3 even though after I moved up to 4 for awhile and then back to 3.

These levels can get up there as well, we have some 20+ levels.

I hope this is not too confusing. It is hard to explain in text. I will answer any questions you have in hopes to come up with a solution.

Thanks.

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

A couple of items - First, you can't manipulate the &vars while the report is running. Setting the &var values requires Dialogue Manager which is procedural - processed in the order encountered - so all of that is completed before the fex is run and they can't be changed in the middle of running the fex. You could do some looping to run the procedure multiple times, changing the values in between each run.

Second, while I can see the relationship between the items/levels, you don't really specify how you are creating that relationship in your reads. Is this a recursive join or multiple passes or something of the sort? It seems that if the parent/child relationship is set up properly a child knows its parent and since you have already calculated how many of the parent part are required, you can simply multiply the qty needed by the parent's qty needed.

So part1, since it is the top level requires 10. Part 2 knows part1 is its parent, so multiply 3 by 10. Part3 knows that part2 is its parent so multiply 2 by 30. Part 4 knows that part2 is its parent so multiply .5 by 30, and so on.

So if you can explain how you're determining that parent-child relationship, we can come up with something. If you're question is how to determine that relationship, then we need to see the file layout.

I am creating the parent child relationship though a sql statement which uses a couple of connect by's and padding the levels with html tabs to create an indented look, which I then put into a focus temp and replace with other tab types depending on the output they choose (excel, pdf, etc). All of this works and looks great when the report is run, but even using the statement I built (with a lot of research and stumbling along) I am unsure how to make the 'quantity per' field multiply properly.

This is the sql we use for the report. I removed a lot of the fields that do not relate to this issue.

SQL SQLORA PREPARE SQLOUT FOR
select replace(substr(lpad(' ',2*level)||component_part_no,1,35), ' ', '&|nbsp&|nbsp&|nbsp&|nbsp') as "component_part", component_part_no as "com_part", parent_part_number, level + 1 as "level1" , item_number, qty_per_type, quantity_per * &qnty as "Quantity_Per" from (select distinct(component_part_no), parent_part_number, level, item_number, qty_per_type, quantity_per 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

Not sure if this helps.

Assuming you have a master for your BOM like this

FILENAME=BOM , SUFFIX=FIX , $
SEGMENT=BOM, SEGTYPE=S0, $
FIELDNAME=LEVEL, ALIAS=E01, USAGE=I2, ACTUAL=A2, $
FIELDNAME=PART, ALIAS=E02, USAGE=A6, ACTUAL=A6 ,$
FIELDNAME=QTY, ALIAS=E03, USAGE=D20.2, ACTUAL=A20, $

for a data file like this

 1part1 1
 2part2 3
 3part3 2
 3part4 .5
 4part5 3
 4part6 4
 3part7 .25
 2part8 3
 2part9 2
 3part106

Happy New Year

 
-SET &QTY=10;
TABLE FILE BOM
BY HIGHEST LEVEL
ON TABLE SAVE
END
-RUN
-READ SAVE &MAXLVL.A2.
TABLEF FILE BOM
PRINT
 LEVEL
 PART
 QTY
 COMPUTE LQTY1/D20.2  = IF LEVEL EQ 1  THEN QTY*&QTY   ELSE LAST LQTY1;  NOPRINT
-SET &LVL=1;
-REPEAT ENDREPEAT1 &MAXLVL TIMES
-SET &LSTLVL= &LVL;
-SET &LVL= &LVL + 1;
 COMPUTE LQTY&LVL.EVAL/D20.2 = IF LEVEL EQ &LVL.EVAL THEN QTY * LQTY&LSTLVL.EVAL ELSE LAST LQTY&LVL.EVAL; NOPRINT
-ENDREPEAT1
 COMPUTE NUMOFF/D20.2 = IF LEVEL EQ 1  THEN LQTY1  ELSE
-SET &LVL=1;
-REPEAT ENDREPEAT2 &MAXLVL TIMES
-SET &LVL= &LVL + 1;
                        IF LEVEL EQ &LVL.EVAL  THEN LQTY&LVL.EVAL  ELSE
-ENDREPEAT2
                         0;
END

Hi JG,

Thanks for the reply. So you ARE setting amper vars within a report being run (from what I can tell). The last 3-4 lines of your example got out of whack so please let me know if the code I post below is not correct at that part.

I tried to get it to work, but LEVEL is not a field that actually exist. It is being created by the SQL statement as it figures out the parent/child relationships. It sees it as a D20.2. I tried defining the field into something like I2, but now I am having field type comparison errors.

1
0 NUMBER OF RECORDS IN TABLE= 2134 LINES= 2134
0
0 NUMBER OF RECORDS IN TABLE= 2134 LINES= 4
ALPHANUMERIC RECORD NAMED SAVE
0 FIELDNAME ALIAS FORMAT LENGTH
LEVEL I2 2
LEVEL I2 2
TOTAL 4
0 ERROR AT OR NEAR LINE 54 IN PROCEDURE multilevel_bom1FOC
(FOC281) ALPHA ARGUMENTS IN PLACE WHERE NUMERIC ARE CALLED FOR
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

This is what the code looks like...

SET EMPTYREPORT = ON
-SET &QTY=10;
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", component_part_no as "com_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 as "Quantity_Per" , substitute_part, product_code, scrap_rate_reference, report_entity, status_flag, job_number, trace_code, eff_start_date, eff_close_date 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, trace_code, eff_start_date, eff_close_date 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
com_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
TRACE_CODE
EFF_START_DATE
EFF_CLOSE_DATE
HEADING
" "
" "
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS BOM_TEMP
END

DEFINE FILE BOM_TEMP
LEVEL/I2=level1;
END

TABLE FILE BOM_TEMP
BY HIGHEST LEVEL
ON TABLE SAVE
END
-RUN
-READ SAVE &MAXLVL.I2.

TABLEF FILE BOM_TEMP
PRINT
LEVEL
com_part
QTY
COMPUTE LQTY1/D20.2 = IF LEVEL EQ 1 THEN QTY*&QTY ELSE LAST LQTY1; NOPRINT
-SET &LVL=1;
-REPEAT ENDREPEAT1 &MAXLVL TIMES
-SET &LSTLVL= &LVL;
-SET &LVL= &LVL + 1;
COMPUTE LQTY&LVL.EVAL/D20.2 = IF LEVEL EQ &LVL.EVAL THEN QTY * LQTY&LSTLVL.EVAL ELSE LAST LQTY&LVL.EVAL; NOPRINT
-ENDREPEAT1
COMPUTE NUMOFF/D20.2 = IF LEVEL EQ 1 THEN LQTY1 ELSE
-SET &LVL=1;
-REPEAT ENDREPEAT2 &MAXLVL TIMES
-SET &LVL= &LVL + 1;
IF LEVEL EQ &LVL.EVAL THEN LQTY&LVL.EVAL ELSE
-ENDREPEAT2
0;
END

Chet, It's not using the amper variables when running the focexec it's using them to dynamically
generate the required code.

I suspect that QTY_PER_TYPE in your data file which is being picked up as QTY by WebFOCUS (unique trucation)
is alpha and not numeric. You will either have to convert it to numeric in WebFOCUS or cast it in the SQL request.