[CASE OPENED] BY HIERARCHY HOLD and (FOC32660)

I'm suspecting that you may not have included any "real" field from the Master files in your TABLE FILE request.

Share your code, it will help us helping you.

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

Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

Expert

posted

It looks like the WITHIN attribute that you most likely have in your GL account table Master is not carried over to the HOLD file. I cannot find syntax that would include the WITHIN in the HOLD file.

I think you can create a new Master that matches your HOLD Master, but with the WITHIN attributes added to the appropriate columns, then use FILEDEF to point the HOLD file to the new Master. The master can be located in an app folder or a different folder that you can prepend to the app path.

Just a thought.

I think we all have (or I do) carolap.mas in the app folder ibisamp. I would test using this table as an example:

TABLE FILE CAROLAP
SUM
SALES
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS CAROLAP_HOLD1
END

FILEDEF CAROLAP_HOLD1_NEW_MASTER DISK carolap_hold1.ftm

TABLE FILE CAROLAP_HOLD1_NEW_MASTER
SUM SALES
...
END

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

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Gold member

posted

 SET ASNAMES = ON
SET ACROSSPRT = COMPRESS
SET HOLDATTR = ON
SET HOLDSTAT = ON
-DEFAULTH &Property_AD_DM_Prop_BU_Unit_Property = '10 Hanover Square - 12200'
-DEFAULTH &Date_Calendar_Month_YYYYMM = 201508
DEFINE FILE DM
INC/A5=IF EDIT( GL_Account_Finance_Tree_name, '9') EQ '1' OR '2' OR '3' OR '4' OR '5' OR '6' OR '7' OR '8' OR '9' OR '0' THEN 'EX' ELSE 'INC';
END
TABLE FILE DM
SUM
     Ledger_Amount
     COMPUTE Actual_MTD/D12.2 = IF Date_Date_Calculation EQ 'MTD' THEN Ledger_Amount ELSE 0;
     COMPUTE Actual_MTD_LY/D12.2 = IF Date_Date_Calculation EQ 'MTD LY' THEN Ledger_Amount ELSE 0;
     COMPUTE Actual_QTD/D12.2 = IF Date_Date_Calculation EQ 'QTD' THEN Ledger_Amount ELSE 0;
     COMPUTE Actual_QTD_LY/D12.2 = IF Date_Date_Calculation EQ 'QTD LY' THEN Ledger_Amount ELSE 0;
     COMPUTE ORDER/I5 = 1 + LAST ORDER;
BY  GL_Account_Finance_Tree_name HIERARCHY
BY  Date_Calendar_Month
ON TABLE SUBHEAD
"HOLD THIS RPT COMPONENT AND BUILD LEFT HALF OFF THIS. MAY HAVE TO JOIN TO THE RIGHT SIDE TO MAINTAIN GL STRUCTURE."
"Alpha hold: LY1"
WHERE Date_Date_Calculation EQ 'MTD' OR 'MTD LY' OR 'QTD' OR 'QTD LY';
WHERE Date_Calendar_Month_YYYYMM EQ &Date_Calendar_Month_YYYYMM.(FIND DM.DM.Date_Calendar_Month_YYYYMM IN DM).Date_Calendar_Month_YYYYMM:.;
WHERE Property_AD_DM_Prop_BU_Unit_Property EQ '&Property_AD_DM_Prop_BU_Unit_Property.Property_AD_DM_Prop_BU_Unit_Property:.';
WHERE INC EQ 'INC';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS LY1 FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/WFC/Repository/Branding/UDRgreen.sty,
$
TYPE=REPORT,
     LINES-PER-PAGE=UNLIMITED,
     ARGRAPHENGINE=JSCHART,
$
ENDSTYLE
END
TABLE FILE LY1
SUM
     Actual_MTD
     Actual_MTD_LY
     COMPUTE MTD_Var/D12.2B = Actual_MTD - Actual_MTD_LY;
     COMPUTE MTD_VarPct/D12.2% = 100 * ( ( Actual_MTD - Actual_MTD_LY ) / Actual_MTD );
     Actual_QTD
     Actual_QTD_LY
     COMPUTE QTD_Var/D12.2B = Actual_QTD - Actual_QTD_LY;
     COMPUTE QTD_VarPct/D12.2% = 100 * ( ( Actual_QTD - Actual_QTD_LY ) / Actual_QTD );
BY  E01
ON TABLE SUBHEAD
"Alpha hold: LY2"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS LY2 FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
-*Above this point is for the Left hand side of the report; Actual vs Actual LY by MTD and QTD.
-*Below this point is for the Right hand side of the report; Acual vs Budget by MTD, QTD.
-*Because the HOLD file doesn't preserve Hierarchy Structure, Left outter join to the Report below.
JOIN
DM.DM.GL_Account_Finance_Tree_name IN dm TO MULTIPLE LY2.LY2.E01 IN LY2 TAG J2
AS J2
END
TABLE FILE DM
SUM
     Ledger_Amount/D12.2B AS 'Actual'
     Budget_Amount/D12.2B AS 'Budget'
     COMPUTE Actual_MTD/D12.2 = IF Date_Date_Calculation EQ 'MTD' THEN Ledger_Amount ELSE 0;
     COMPUTE Actual_QTD/D12.2 = IF Date_Date_Calculation EQ 'QTD' THEN Ledger_Amount ELSE 0;
     COMPUTE Budget_MTD/D12.2 = IF Date_Date_Calculation EQ 'MTD' THEN Budget_Amount ELSE 0;
     COMPUTE Budget_QTD/D12.2 = IF Date_Date_Calculation EQ 'QTD' THEN Budget_Amount ELSE 0;
     COMPUTE Variance_MTD/D12.2B = Actual_MTD - Budget_MTD;
     COMPUTE Variance_MTD_Pct/D12.2% = 100 * ( ( Actual_MTD - Budget_MTD ) / Budget_MTD );
     COMPUTE Variance_QTD/D12.2B = Actual_QTD - Budget_QTD;
     COMPUTE Variance_QTD_Pct/D12.2% = 100 * ( ( Actual_QTD - Budget_QTD ) / Budget_QTD );
BY  GL_Account_Finance_Tree_name HIERARCHY
BY  Date_Calendar_Month NOPRINT
BY  LOWEST Date_Date_Calculation
ON TABLE SUBHEAD
"<Property_AD_DM_Prop_BU_Unit_Property "
"<Date_Calendar_Month_YYYYMM "
"Run:<+0> &DATE"
"Alpha hold: AVB"
WHERE Date_Date_Calculation EQ 'MTD' OR 'QTD';
WHERE Date_Calendar_Month_YYYYMM EQ &Date_Calendar_Month_YYYYMM.(FIND DM.DM.Date_Calendar_Month_YYYYMM IN DM).Date_Calendar_Month_YYYYMM:.;
WHERE Property_AD_DM_Prop_BU_Unit_Property EQ '&Property_AD_DM_Prop_BU_Unit_Property.Property_AD_DM_Prop_BU_Unit_Property:.';
WHERE INC EQ 'INC';
WHERE Budget_Scenario_Budget_Type EQ 'Budgets';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS AVB FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

_____
WF 8.1.04
Win 7// Windows Server 2012 R2
SASS OLAP Cube

Posts: 92 | Registered: July 31, 2015

Ignored post by Nicholas Spyrison posted

Gold member

posted

Would it help for me to paste the GL_Account_Finance_Tree WITHIN code from the master file?

I'll see if I can get your carolap example working.

Thanks to both.

_____
WF 8.1.04
Win 7// Windows Server 2012 R2
SASS OLAP Cube

Posts: 92 | Registered: July 31, 2015

Ignored post by Nicholas Spyrison posted

Expert

posted

I think you should create a new master for hold file LY2 and add the WITHIN attributes as found in GL_Account_Finance_Tree. Maybe there are "PROPERTY=PARENT_OF and REFERENCE=hierarchyfld" attributes...

Interestingly, I cannot find any information on the "HIERARCHY" keyword in the 'Creating Reports With WebFOCUS Language' documentation, only in the 'Creating Reporting Applications With Developer Studio > Hierarchical Reporting > Sorting Hierarchical Data' doc.

Perhaps another approach would be to try and create the report without HOLD.

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

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Gold member

posted

Can't get the FILEDEF master file to work with CAROLAP. I currently have:

TABLE FILE IBISAMP/CAROLAP
SUM
     SALES
BY  COUNTRY
BY  CAR
BY  MODEL
ON TABLE NOTOTAL
ON TABLE HOLD AS CARHOLD
ON TABLE SET STYLE *
$
ENDSTYLE
END
FILEDEF MASTER DISK carhold.ftm
TABLE FILE MASTER
SUM
     SALES
BY  CAR
ON TABLE NOTOTAL
END

there is something in 'WebFOCUS Release 8.1 Version 05 > Reporting Language > Creating Reports With WebFOCUS Language > Creating Financial Reports With Financial Modeling Language (FML)', but not sure where FML comes into play/differs.

With OLAP not able to do multiple verb requests and ROLLUP_BY_VISUALTOTALS, I can't imagine anyway to do this without using a HOLD file.

_____
WF 8.1.04
Win 7// Windows Server 2012 R2
SASS OLAP Cube

Posts: 92 | Registered: July 31, 2015

Ignored post by Nicholas Spyrison posted

Gold member

posted

And where/how do I implement the Master file? does the filedef create a master file on the server?

_____
WF 8.1.04
Win 7// Windows Server 2012 R2
SASS OLAP Cube

Posts: 92 | Registered: July 31, 2015

Ignored post by Nicholas Spyrison posted

Expert

posted

Nicholas, MASTER is a reserved word for the location of the master files. What you need to do is use FILEDEF to assign the new master to the hold file:

FILEDEF new_master DISK carhold.ftm

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

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Expert

posted

You have to capture the master once. Then create a new master from the generated master, include the HIERARCHY stuff.

Use

APP HOLD BASEAPP

one time so that the master for the hold file is created somewhere that can be retrieved. Then rename this to blah_blah_hold_blah_blah and add the HIERARCHY info.

I really don't know if this will work, but it may be worth a try.

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

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Gold member

posted

quote:
FILEDEF new_master DISK carhold.ftm

ON TABLE HOLD AS CARHOLD
ON TABLE SET STYLE *
$
ENDSTYLE
END
FILEDEF new_master DISK carhold.ftm
TABLE FILE NEW_MASTER

isn't working. Do I need to create the master file with

APP HOLD BASEAPP

first?

How do I use

APP HOLD BASEAPP

?
does it take the place of

ON TABLE HOLD AS CARHOLD

_____
WF 8.1.04
Win 7// Windows Server 2012 R2
SASS OLAP Cube

Posts: 92 | Registered: July 31, 2015

Ignored post by Nicholas Spyrison posted

Expert

posted

APP HOLD BASEAPP

is a command that redirects temporary HOLD files and their Masters to a physical app folder. I suggested using the command so that you can capture the HOLD file created by

ON TABLE HOLD AS CARHOLD

The reason for capturing the Master is so that you can create a new Master that includes the WITHIN attributes. The APP HOLD command is only necessary one time - to capture the Master of the HOLD file. After you create the new Master, The APP HOLD command is no longer required in your code. If you put the newly created Master (it should have a name different than the HOLD file) in an app folder and use FILEDEF to point the new Master to the HOLD file, then you can TABLE FILE the new Master which will read the HOLD file, and which will provide the WITHIN attributes, hopefully enabling the Hierarchy you lost in the original HOLD file Master.

This method is pure speculation - I don't have a clue if WITHIN works in a HOLD file. This method is commonly used (at least by me) to capture the Master (and/or data) of a HOLD file.

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

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Gold member

posted

ok thanks. Thinging back on your suggestion of not using hold files; if I can get ROLLUP_BY_VISUALTOTALS ON to work I might bypass this mess.

I will go back to attempting the ROLLUP_BY_VISUALTOTALS method again before i get into the new master file.

_____
WF 8.1.04
Win 7// Windows Server 2012 R2
SASS OLAP Cube

Posts: 92 | Registered: July 31, 2015

Ignored post by Nicholas Spyrison posted