[CLOSED] Unable to create Chart in GUI on Temporary Hold files
Hi All,
I am facing an issue while creating Chart using GUI in WF 8.2.0.1 app-studio ,
I have several hold files and then combining all the hold files into one using Universal concatenation. If I am going to create a report on final hold file it allow me to create the Report using GUI but if i want to create the Chart it breaks and show the error , "description can not founds" .
Below is my code ,
-DEFAULT &BU_CU='CU';
-DEFAULT &BU_FILTER = '_FOC_NULL'
-DEFAULT &CU_FILTER = '_FOC_NULL'
-DEFAULT &CU_FILTER = '_FOC_NULL'
-DEFAULT &FIS_YEAR = 2017;
-DEFAULT &FIS_MONTH = 'June'
SET HOLDLIST = EXPLICIT
-***********************************************************************************************
-** CALCULATING KTG REVENUE AND COGS.
-***********************************************************************************************
DEFINE FILE CLS_KTG_FIN_AGGR_REV_COGS
COGS/D20.2=IF( CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_NAV_GL_ACCOUNTS.POST_TYPE EQ 'COGS' ) THEN CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_AGGR_REV_COGS.AMOUNT ELSE 0.0;
REVENUE/D20.2=IF( CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_NAV_GL_ACCOUNTS.POST_TYPE EQ 'REVENUE' ) THEN CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_AGGR_REV_COGS.AMOUNT ELSE 0.0;
END
TABLE FILE CLS_KTG_FIN_AGGR_REV_COGS
SUM
CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_AGGR_REV_COGS.REVENUE AS 'REVENUE'
CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_AGGR_REV_COGS.COGS AS 'COGS'
BY &BU_CU AS ' SORT'
WHERE ( CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_AGGR_REV_COGS.KTG_FISCALMONTH EQ &FIS_MONTH.(FIND CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_AGGR_REV_COGS.KTG_FISCALMONTH IN CLS_KTG_FIN_AGGR_REV_COGS|FORMAT=A10V).FIS_MONTH:.QUOTEDSTRING ) AND ( CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_AGGR_REV_COGS.KTG_FISCALYEAR EQ &FIS_YEAR.(FIND CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_AGGR_REV_COGS.KTG_FISCALYEAR IN CLS_KTG_FIN_AGGR_REV_COGS|FORMAT=I11).FIS_YEAR:. );
WHERE ( CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_AGGR_REV_COGS.GL_ACCOUNT NE '39009' OR '40009' ) AND ( CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_AGGR_REV_COGS.BU EQ &BU_FILTER.(OR(FIND CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_AGGR_REV_COGS.BU IN CLS_KTG_FIN_AGGR_REV_COGS|FORMAT=A255V)).BU:. ) AND ( CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_AGGR_REV_COGS.CU EQ &CU_FILTER.(OR(FIND CLS_KTG_FIN_AGGR_REV_COGS.KTG_FIN_AGGR_REV_COGS.CU IN CLS_KTG_FIN_AGGR_REV_COGS|FORMAT=A255V)).CU:. );
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS ktg_rev_cogs
END
-RUN
-***********************************************************************************************
-** CALCULATING OPX MFG FOR KTG .
-***********************************************************************************************
TABLE FILE KTG_FIN_MFG_OPX
SUM
KTG_FIN_MFG_OPX.KTG_FIN_MFG_OPX.AMOUNT/D20.2 AS 'COGS'
COMPUTE REVENUE/D20.2 = 0; AS 'REVENUE'
BY &BU_CU AS ' SORT'
WHERE ( KTG_FIN_MFG_OPX.KTG_FIN_MFG_OPX.KTG_MONTH EQ &FIS_MONTH.(FIND KTG_FIN_MFG_OPX.KTG_FIN_MFG_OPX.KTG_MONTH IN KTG_FIN_MFG_OPX|FORMAT=A10V).FIS_MONTH:.QUOTEDSTRING ) AND ( KTG_FIN_MFG_OPX.KTG_FIN_MFG_OPX.KTG_FISCALYEAR EQ &FIS_YEAR.(FIND KTG_FIN_MFG_OPX.KTG_FIN_MFG_OPX.KTG_FISCALYEAR IN KTG_FIN_MFG_OPX|FORMAT=I11).FIS_YEAR:. ) AND ( KTG_FIN_MFG_OPX.KTG_FIN_MFG_OPX.BU EQ &BU_FILTER.(FIND KTG_FIN_MFG_OPX.KTG_FIN_MFG_OPX.BU IN KTG_FIN_MFG_OPX|FORMAT=A255V).BU_FILTER:.QUOTEDSTRING ) AND ( KTG_FIN_MFG_OPX.KTG_FIN_MFG_OPX.CU EQ &CU_FILTER.(FIND KTG_FIN_MFG_OPX.KTG_FIN_MFG_OPX.CU IN KTG_FIN_MFG_OPX|FORMAT=A255V).CU_FILTER:.QUOTEDSTRING );
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS ktg_rev_opx
ON TABLE SET STYLE *
$
ENDSTYLE
END
-RUN
-***********************************************************************************************
-** CALCULATING OPX MFG FOR KTG AND COGS REVENUE.
-***********************************************************************************************
TABLE FILE KTG_REV_COGS
SUM
KTG_REV_COGS.KTG_REV_.REVENUE
KTG_REV_COGS.KTG_REV_.COGS
BY SORT
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS ktg_rev_cost
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/enwarm.sty,
$
ENDSTYLE
MORE
FILE KTG_REV_OPX
END
-RUN
-***********************************************************************************************
-** CALCULATING ICOS FOR BS .
-***********************************************************************************************
TABLE FILE BS_FIN_ICOS
SUM
BS_FIN_ICOS.BS_FIN_ICOS.AMOUNT/D20.2 AS 'COGS'
COMPUTE REVENUE/D20.2 = 0; AS 'REVENUE'
BY &BU_CU AS 'SORT'
WHERE ( BS_FIN_ICOS.BS_FIN_ICOS.BS_MONTH EQ &FIS_MONTH.(FIND BS_FIN_ICOS.BS_FIN_ICOS.BS_MONTH IN BS_FIN_ICOS|FORMAT=A10V).FIS_MONTH:.QUOTEDSTRING ) AND ( BS_FIN_ICOS.BS_FIN_ICOS.BS_FISCALYEAR EQ &FIS_YEAR.(FIND BS_FIN_ICOS.BS_FIN_ICOS.BS_FISCALYEAR IN BS_FIN_ICOS|FORMAT=I11).FIS_YEAR:. ) AND ( BS_FIN_ICOS.BS_FIN_ICOS.BU EQ &BU_FILTER.(OR(FIND BS_FIN_ICOS.BS_FIN_ICOS.BU IN BS_FIN_ICOS|FORMAT=A255V)).BU_FILTER:. ) AND ( BS_FIN_ICOS.BS_FIN_ICOS.CU EQ &CU_FILTER.(FIND BS_FIN_ICOS.BS_FIN_ICOS.CU IN BS_FIN_ICOS|FORMAT=A255V).CU_FILTER:.QUOTEDSTRING );
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS bs_rev_icos
ON TABLE SET STYLE *
$
ENDSTYLE
END
-RUN
-***********************************************************************************************
-** CALCULATING BS REVENUE AND COGS.
-***********************************************************************************************
TABLE FILE BS_FIN_AGGR_REV_COGS
SUM
BS_FIN_AGGR_REV_COGS.BS_FIN_AGGR_REV_COGS.RECOG_REV/D20.2 AS 'REVENUE'
BS_FIN_AGGR_REV_COGS.BS_FIN_AGGR_REV_COGS.USAGE/D20.2 AS 'COGS'
BY &BU_CU AS ' SORT'
WHERE ( BS_FIN_AGGR_REV_COGS.BS_FIN_AGGR_REV_COGS.BS_MONTH EQ &FIS_MONTH.(FIND BS_FIN_AGGR_REV_COGS.BS_FIN_AGGR_REV_COGS.BS_MONTH IN BS_FIN_AGGR_REV_COGS|FORMAT=A10V).FIS_MONTH:.QUOTEDSTRING ) AND ( BS_FIN_AGGR_REV_COGS.BS_FIN_AGGR_REV_COGS.BS_FISCALYEAR EQ &FIS_YEAR.(FIND BS_FIN_AGGR_REV_COGS.BS_FIN_AGGR_REV_COGS.BS_FISCALYEAR IN BS_FIN_AGGR_REV_COGS|FORMAT=I4).FIS_YEAR:. ) AND ( BS_FIN_AGGR_REV_COGS.BS_FIN_AGGR_REV_COGS.BU EQ &BU_FILTER.(OR(FIND BS_FIN_AGGR_REV_COGS.BS_FIN_AGGR_REV_COGS.BU IN BS_FIN_AGGR_REV_COGS|FORMAT=A255V)).BU_FILTER:. ) AND ( BS_FIN_AGGR_REV_COGS.BS_FIN_AGGR_REV_COGS.CU EQ &CU_FILTER.(FIND BS_FIN_AGGR_REV_COGS.BS_FIN_AGGR_REV_COGS.CU IN BS_FIN_AGGR_REV_COGS|FORMAT=A255V).CU_FILTER:.QUOTEDSTRING );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS bs_rev_cogs
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/enwarm.sty,
$
ENDSTYLE
END
-RUN
-***********************************************************************************************
-** CALCULATING ICOS FOR BS AND COGS REVENUE.
-***********************************************************************************************
TABLE FILE BS_REV_COGS
SUM
BS_REV_COGS.BS_REV_C.COGS
BS_REV_COGS.BS_REV_C.REVENUE
BY SORT
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS bs_rev_cost
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/enwarm.sty,
$
ENDSTYLE
MORE
FILE BS_REV_ICOS
END
-***********************************************************************************************
-** ADDING BOTH FILES(All KTG+BS ) DATA AND HOLDING IT INTO ONE.
-***********************************************************************************************
TABLE FILE KTG_REV_COST
SUM
KTG_REV_COST.KTG_REV_.REVENUE
KTG_REV_COST.KTG_REV_.COGS
COMPUTE PROFIT/D20.2 = KTG_REV_COST.KTG_REV_.REVENUE - KTG_REV_COST.KTG_REV_.COGS;
COMPUTE PCT_PROFIT/D12.2% = PROFIT/ KTG_REV_COST.KTG_REV_.REVENUE*100;
BY SORT
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS ktg_bs_rev_prof
MORE
FILE BS_REV_COST
END
-RUN
GRAPH FILE ktg_bs_rev_prof
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET EMBEDHEADING ON
END
I think that the available file drop list have a limited display list or even a bug (already been discussed in other post). If you pay attention, "ktg_bs_rev_prof" is not the only file that is not part of the list
I suggest to do the following only once to force the file to exist physically and then remove it to have it exist only in memory.
TABLE FILE KTG_REV_COST
SUM
KTG_REV_COST.KTG_REV_.REVENUE
KTG_REV_COST.KTG_REV_.COGS
COMPUTE PROFIT/D20.2 = KTG_REV_COST.KTG_REV_.REVENUE - KTG_REV_COST.KTG_REV_.COGS;
COMPUTE PCT_PROFIT/D12.2% = PROFIT/ KTG_REV_COST.KTG_REV_.REVENUE*100;
BY SORT
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS baseapp/ktg_bs_rev_prof
MORE
FILE BS_REV_COST
END
-RUN
GRAPH FILE ktg_bs_rev_prof
...
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET EMBEDHEADING ON
END
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
November 03, 2017, 09:55 AM
Chaudhary
quote:
Originally posted by MartinY: I think that the available file drop list have a limited display list or even a bug (already been discussed in other post). If you pay attention, "ktg_bs_rev_prof" is not the only file that is not part of the list
I suggest to do the following only once to force the file to exist physically and then remove it to have it exist only in memory.
TABLE FILE KTG_REV_COST
SUM
KTG_REV_COST.KTG_REV_.REVENUE
KTG_REV_COST.KTG_REV_.COGS
COMPUTE PROFIT/D20.2 = KTG_REV_COST.KTG_REV_.REVENUE - KTG_REV_COST.KTG_REV_.COGS;
COMPUTE PCT_PROFIT/D12.2% = PROFIT/ KTG_REV_COST.KTG_REV_.REVENUE*100;
BY SORT
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS baseapp/ktg_bs_rev_prof
MORE
FILE BS_REV_COST
END
-RUN
GRAPH FILE ktg_bs_rev_prof
...
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET EMBEDHEADING ON
END
ktg_bs_rev_prof was available(by mistake I uploaded wrong image ) , I applied your solution and it works Thanks MartinY . Can we go with permanent hold file concept(hold as 'baseapp/holdfilename') and there will not be a chance of data overwriting for any user request, while multiple users are hitting the same report at the same time .
I would not go there because of the reason you mentioned : data overlap with multi-user run. It's always better to have temporary files (in between two steps) in memory. Otherwise you can use "foccache/" instead of "baseapp/" as per my sample.
foccache is a temporary folder where each user will have its own tempfolder on execution and the data remain available for a period of time according to your client/RS setting. But in a sense, providing foccache is almost similar to use memory.
Physically holding a file is (most of the time) a good development practice only : not a production one.
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
November 03, 2017, 10:38 AM
Chaudhary
[/QUOTE]
Otherwise you can use "foccache/" instead of "baseapp/" as per my sample. [/QUOTE]
If i am using "foccache/" instead of "baseapp/" as per your sample, it causes same issue , Chart not opens in GUI same error "File Description not found" . Thank-you very much for your support .