You can not do that. DM does not run as part of the fex in that way.
your only real option is to write the variables out to a flat file with a key composed of a row number and the variable name and join to it.
February 17, 2009, 05:18 PM
Kal
Thanks JG for your reply! I was hoping it to be my last resort, because of some complications I have with other requirements.. But, I'll start on that and see where it goes.
Also posting up a CAR sample on my issue to make sure that I am explaining it correctly.. Would you please check it out once!
I am not using DM as part of the table logic & the Variable Names are coming up correctly, but I was just not able to get it parsed to its value. Thanks for looking it up!
-* File CAR_Parsing_indexedparms_to_NumericField.fex
-****Assign Indexed Parms
-SET &ECHO = ALL;
-SET &MAXRECS=14;
-REPEAT LOOP FOR &CNTR FROM 1 TO &MAXRECS;
-SET &ACNTR = IF &CNTR LT 10 THEN '0' | &CNTR ELSE &CNTR ;
-*-SET &Updatefld.&ACNTR=&Estimated.&ACNTR;
-SET &Var.&ACNTR=140000;
-LOOP
-****Update Field
TABLE FILE CAR
SUM
COMPUTE ROWNUM/I2 = ROWNUM+ 1; NOPRINT
COMPUTE AROWNUM/A2 = EDIT(ROWNUM); NOPRINT
-*COMPUTE Updated/D12.2 = &Updatefld.&ACNTR ;
COMPUTE VarName/A20 = '&' | 'Var' | AROWNUM ;
COMPUTE VarName2/A20 = EDIT(VarName,'''9999999''') ;
COMPUTE Fld/D12.2 = EDIT(VarName2) ;
BY COUNTRY NOPRINT
END
-EXIT
I really don't understand what you're trying to do, but this may help:
-* File CAR_Parsing_indexedparms_to_NumericField.fex
-****Assign Indexed Parms
-SET &ECHO = ALL;
-SET &MAXRECS=14;
-REPEAT LOOP FOR &CNTR FROM 1 TO &MAXRECS;
-SET &ACNTR = IF &CNTR LT 10 THEN '0' | &CNTR ELSE &CNTR ;
-*-SET &Updatefld.&ACNTR=&Estimated.&ACNTR;
-SET &Var.&ACNTR=140000;
-LOOP
-****Update Field
TABLE FILE CAR
SUM
COMPUTE ROWNUM/I2 = ROWNUM + 1; NOPRINT
COMPUTE Fld/D12.2 =
IF ROWNUM EQ 1 THEN &Var01 ELSE
IF ROWNUM EQ 2 THEN &Var02 ELSE
IF ROWNUM EQ 3 THEN &Var03 ELSE
IF ROWNUM EQ 4 THEN &Var04 ELSE
IF ROWNUM EQ 5 THEN &Var05 ELSE
IF ROWNUM EQ 6 THEN &Var06 ELSE
IF ROWNUM EQ 7 THEN &Var07 ELSE
IF ROWNUM EQ 8 THEN &Var08 ELSE
IF ROWNUM EQ 9 THEN &Var09 ELSE
IF ROWNUM EQ 10 THEN &Var10 ELSE
IF ROWNUM EQ 11 THEN &Var11 ELSE
IF ROWNUM EQ 12 THEN &Var12 ELSE
IF ROWNUM EQ 13 THEN &Var13 ELSE
IF ROWNUM EQ 14 THEN &Var14 ELSE 0;
BY COUNTRY
END
-EXIT
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
February 17, 2009, 07:39 PM
Kal
Thanks much Francis! This helps for a good workaround.
User edits the proposed values in an 'editable' Report column & clicks for an Estimation. The fex passes all the column values to the same logic, in a different fex, to update the user values as a new column (with few more Computed cols & Subtotals.)
The Editable column repeats itself for a second scenario of estimated inputs from the user.
&MAXREC is passed from the first Report. So with this way, I have a limitation of no of rows & will have to repeat the same compute for the next estimate. With this & JG's join idea, I think it gets me going for now..
Wondering if something like this could be done in any better way. Pls Suggest!
-* File CAR_Parsing_indexedparms_to_NumericField.fex
-****Assign Indexed Parms
-SET &ECHO = ON;
-SET &MAXRECS=14;
-REPEAT LOOP FOR &CNTR FROM 1 TO &MAXRECS;
-*-SET &Updatefld.&ACNTR=&Estimated.&ACNTR;
-SET &Var.&CNTR=140000;
-LOOP
-****Update Field
TABLE FILE CAR
SUM
COMPUTE ROWNUM/I2 = ROWNUM + 1; NOPRINT
COMPUTE Fld/D12.2 =
-REPEAT END_LOOP2 FOR &CNTR FROM 1 TO &MAXRECS;
IF ROWNUM EQ &CNTR THEN &Var&CNTR.EVAL ELSE
-END_LOOP2
0;
BY COUNTRY
END
-EXIT
Use a Dialogue Manager REPEAT loop in the COMPUTE statement.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
February 18, 2009, 12:00 PM
j.gross
This lends itself to a DECODE, which may be more robust.
I recall moderately deep nests of IF THEN ELSE can cause problems.
February 18, 2009, 01:37 PM
Kal
Thanks Francis for the refined logic, I adopted it to my code & made it to work!
quote:
Originally posted by j.gross: This lends itself to a DECODE, which may be more robust. I recall moderately deep nests of IF THEN ELSE can cause problems.
Might be related, But only the part of my computed Columns which are done from this loop doesn't 'Recompute' on Subtotal & Percentage. I still have to troubleshoot further to confirm the cause. Thanks very much for your assistance!
-* setup &Var values -SET &MAXRECS=14; -REPEAT LOOP1 FOR &CNTR FROM 1 TO &MAXRECS; -SET &Var.&CNTR=100*&CNTR+&CNTR; -LOOP1
TABLE FILE CAR
SUM
COMPUTE ROWNUM/I4 = ROWNUM + 1;
COMPUTE Fld/D12.2 =
DECODE ROWNUM (
-REPEAT LOOP2 FOR &CNTR FROM 1 TO &MAXRECS;
&CNTR &Var.&CNTR
-LOOP2
ELSE 0);
BY COUNTRY
BY CAR
BY MODEL
END
Is that what you tried?
February 18, 2009, 05:36 PM
Kal
Thanks j.grass, I tried it now and it did work for me on the indexed parms. But my new Compute Totals issue is still there..
failing to get the Totals, just for the Computes from this logic.
I am able to reproduce it with CAR for my complete logic:
-* File baseapp/car_estimate.fex
-DEFAULTH &EST_FLG=0;
-DEFAULTH &RCOST1=0;
TABLE FILE CAR
SUM DCOST/D12.2CM
RCOST/D12.2CM
COMPUTE COSTDIFF/D12.2CM = RCOST - DCOST ;
COMPUTE PERCENTDIFF/D6.1% = COSTDIFF/DCOST * 100 ;
COMPUTE ROWNUM/I4 = ROWNUM + 1; NOPRINT
-*******
-IF &EST_FLG EQ 0 THEN GOTO ESTFLG0 ELSE GOTO ESTFLG;
-ESTFLG
COMPUTE NEW_RCOST/D12.2 =
DECODE ROWNUM (
-REPEAT LOOP2 FOR &CNTR FROM 1 TO &MAXRECS;
&CNTR &RCOST1.&CNTR
-LOOP2
ELSE 0);
COMPUTE NEW_DIFF/D12.2CM = NEW_RCOST - DCOST ;
COMPUTE NEW_PRCNT/D6.1% = NEW_DIFF/DCOST *100 ;
-ESTFLG0
COMPUTE RCOST1/A60 = '<INPUT TYPE=TEXT NAME=RCOST1 VALUE='
|| FTOA(RCOST,'(F7)','A7') || '>'; AS '<INPUT TYPE=SUBMIT VALUE=Estimate> for,New RCOST:';
-************************
BY COUNTRY
BY CAR
BY MODEL
-*ON COUNTRY SUBTOTAL RECOMPUTE
ON TABLE SUMMARIZE
WHERE COUNTRY EQ 'ENGLAND' OR 'FRANCE' ;
ON TABLE HOLD AS FORM1 FORMAT HTMTABLE
ON TABLE SET STYLE *
TYPE=REPORT,GRID=OFF,FONT='ARIAL',SIZE=9,$
TYPE=DATA, BACKCOLOR=( 'WHITE' RGB(224 224 224) ), SIZE=7,$
TYPE=TITLE, SIZE=7, STYLE=BOLD,$
TYPE=GRANDTOTAL, SIZE=7, BACKCOLOR=RGB(210 210 210), STYLE=BOLD,$
ENDSTYLE
END
-*********************************************************************
-SET &MAXRECS = &LINES;
-SET &EST_FLG=&EST_FLG+1;
-HTMLFORM BEGIN
<HTML>
<BODY>
<FORM NAME=FORM ACTION='/ibi_apps/WFServlet' METHOD=POST>
<input type=hidden name=IBIF_ex value=baseapp/car_estimate>
<input type=hidden name=MAXRECS value=&MAXRECS>
<input type=hidden name=EST_FLG value=&EST_FLG>
!IBI.FIL.FORM1;
</FORM>
</BODY></HTML>
-HTMLFORM END
Could anyone pls suggest what I am doing wrong here?
I still couldn't get this figured out.. Can I get anyone to review my last posted code on CAR and possibly suggest what i am doing wrong here please!
So far, With Francis/J.g's help I am able to capture the user changes to a Reported Column & repopulate the changed values.
But I am still not able to get the Column-TOTAL calculated for those loaded values.
Although, the computes on the columns derived from the loaded values(which i checked them to be numeric)are coming out well, Column-totals are somehow coming out as 0 for those using these values. Please Help! Any ideas appreciated!
The reason NEW_RCOST gets zero on the RECOMPUTE or SUMMARIZE line is that the DECODE ROWNUM (...) formula is used to obtain the summary value -- with ROWNUM = sum of the invisible ROWNUM column.
You need to make the submitted values available to the Table request as a real field, rather than a compute. Easiest way is to HOLD your data (including this compute, but excluding the computed ratios), then report off the HOLD.
-* File baseapp/car_estimate.fex
-DEFAULTH &EST_FLG=0;
-DEFAULTH &RETAIL_COST1=0;
TABLE FILE CAR
SUM DEALER_COST
RETAIL_COST
COMPUTE ROWNUM/I4 = ROWNUM + 1;
-*******
-IF &EST_FLG EQ 0 THEN GOTO ESTFLG0 ELSE GOTO ESTFLG;
-ESTFLG
-SET &NOPRINT=' ';
COMPUTE NEW_RETAIL_COST/D12.2CM= DECODE ROWNUM (
-REPEAT LOOP2 FOR &CNTR FROM 1 TO &MAXRECS;
&CNTR &RETAIL_COST1.&CNTR
-LOOP2
ELSE 0);
-GOTO ESTFLG1
-ESTFLG0
-SET &NOPRINT='NOPRINT';
COMPUTE NEW_RETAIL_COST/D12.2CM=;
-ESTFLG1
BY COUNTRY
BY CAR
BY MODEL
WHERE COUNTRY EQ 'ENGLAND' OR 'FRANCE' ;
ON TABLE HOLD
END
-*********************************************************************
TABLE FILE HOLD
SUM DEALER_COST/D12.2CM
RETAIL_COST/D12.2CM
COMPUTE COSTDIFF/D12.2CM = RETAIL_COST - DEALER_COST ;
COMPUTE PERCENTDIFF/D6.1% = COSTDIFF/DEALER_COST * 100 ;
NEW_RETAIL_COST &NOPRINT
COMPUTE NEW_DIFF/D12.2CM = NEW_RETAIL_COST - DEALER_COST ; &NOPRINT
COMPUTE NEW_PRCNT/D6.1% = NEW_DIFF/DEALER_COST *100 ; &NOPRINT
COMPUTE RETAIL_COST1/A60 = '<INPUT TYPE=TEXT NAME=RETAIL_COST1 VALUE='
|| FTOA(RETAIL_COST,'(F7)','A7') || '>'; AS '<INPUT TYPE=SUBMIT VALUE=Estimate> for,New RETAIL_COST:';
-************************
BY COUNTRY
BY CAR
BY MODEL
ON TABLE SUMMARIZE
WHERE COUNTRY EQ 'ENGLAND' OR 'FRANCE' ;
ON TABLE HOLD AS FORM1 FORMAT HTMTABLE
ON TABLE SET STYLE *
TYPE=REPORT,GRID=OFF,FONT='ARIAL',SIZE=9,$
TYPE=DATA, BACKCOLOR=( 'WHITE' RGB(224 224 224) ), SIZE=7,$
TYPE=TITLE, SIZE=7, STYLE=BOLD,$
TYPE=GRANDTOTAL, SIZE=7, BACKCOLOR=RGB(210 210 210), STYLE=BOLD,$
ENDSTYLE
END
-*********************************************************************
-SET &MAXRECS = &LINES;
-SET &EST_FLG=&EST_FLG+1;
-HTMLFORM BEGIN
<HTML>
<BODY>
<FORM NAME=FORM ACTION='/ibi_apps/WFServlet' METHOD=GET>
<input type=hidden name=IBIF_ex value=baseapp/car_estimate>
<input type=hidden name=MAXRECS value=&MAXRECS>
<input type=hidden name=EST_FLG value=&EST_FLG>
!IBI.FIL.FORM1;
</FORM>
</BODY></HTML>
-HTMLFORM END
February 26, 2009, 05:40 PM
Kal
Awesome! Thanks j.gross for your valuable insight on Foc internals & solving my issue.
Tom - Thank you very much for your reference. It certainly openned up my possibilities for an enhanced version.