 |
|||||
|
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
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. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] Dynamic FML hierarchy | Login/Join |
| Platinum Member |
I'm new to FML and I have two questions: 1. Is there a way to dynamically create an FML hierarchy from a HOLD file? 2. How do I print the entire hierarchy without having to specify the tags? ie, I want to print the entire CENTGL using the GET/WITH CHILDREN ALL, but I don't want to specify the 1000 account. Is there a way to do this? Do I have to have a top-most level parent defined?This message has been edited. Last edited by: Kerry, Thanks. Mark WF 7.6 Windows | ||
|
| Virtuoso |
1) Depends on the hold file. What field do you have in it? 2) If you have the master for the FML created you can get all the accounts. Did you try what you suggested?
| |||||||
|
| Expert |
What I've done in the past to handle this is to TABLE the file getting all Children without parents, and build the FOR statements, HOLD it and INCLUDE it in the FML request. e.g. APP PATH IBINCCEN
TABLE FILE CENTGL
PRINT GL_ACCOUNT
COMPUTE
H_TEXT/A20 = ' WITH CHILDREN ALL' ;
WHERE GL_ACCOUNT_PARENT EQ ' '
ON TABLE HOLD AS HLD_FOR FORMAT ALPHA
END
-RUN
TABLE FILE CENTGL
PRINT
GL_ACCOUNT_TYPE
GL_ROLLUP_OP
GL_ACCOUNT_LEVEL
FOR GL_ACCOUNT
-INCLUDE HLD_FOR
ON TABLE PCHOLD FORMAT DHTML
ENDWaz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Platinum Member |
Waz, pretty cool. I think that would work nicely. I'll try something along those lines. Thanks. Mark WF 7.6 Windows | |||
|
| Platinum Member |
OK, after looking at this, I don't think FML is what I really need. Ultimately, what I'm trying to do is:
TABLE FILE LEDGER
SUM
AMOUNT
BY ACCOUNT
BY YEAR ROWS 1985 OVER 1986
END
(Notice, no FML...ROWS/OVER are legit in tables w/o hierarchies) This works fine, but what I need is to add a final report total that shows the same thing:
TOTAL: 1985
1986
1987
Is that doable? Thanks. Mark WF 7.6 Windows | |||
|
| Virtuoso |
Doable yes. Just depends on how you want it. Two reports, one detail, one summary, one right after the other (or a compound report with NOBREAK) would do it. You could also define a field for each year like TOT1985/D16.2=IF YEAR EQ '1985' THEN AMOUNT ELSE 0; TOT1986/D16.2=IF YEAR EQ '1986' THEN AMOUNT ELSE 0; etc. Then you could embed those field values in a table footing: ON TABLE SUBFOOT "TOTAL: <5><TOT.TOT1985" "<5><TOT.TOT1986 " etc. I was trying to come up with a multi-verb request, but you want the subtotals to ignore your first sort value and a multi-verb set requires all previous sort values to exist in subsequent verb phrases so that won't really work. Someone else might have a better idea - I'm sure there are several ways to do it. 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 | |||
|
| Expert |
Here's a psuedo-code version of something I did for one of my developers that might work for you. It involves, as Darin said, doing a summary and detail and a little sleight of hand, then MOREing them together.
SET ASNAMES=ON
DEFINE FILE LEDGER
TOTACCT/Ann='TOTAL';
SORTFLD/I2=2;
END
TABLE FILE LEDGER
SUM
AMOUNT
BY SORTFLD
BY TOTACCT AS ACCOUNT
BY YEAR
ON TABLE HOLD AS TOTACCT FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
DEFINE FILE LEDGER
SORTFLD/I2=1;
END
TABLE FILE LEDGER
SUM
AMOUNT
BY SORTFLD NOPRINT
BY ACCOUNT
BY YEAR
MORE
FILE TOTACCT
ENDNote that the TOTACCT field above must have the same format as the 'real' ACCOUNT field. Any you might have to hold the detail one more time. Ginny --------------------------------- Prod: WF 7.7.01 Dev: WF 7.6.9-11 Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google | |||
|
| Platinum Member |
Both really good options, I'll test them and let you know which was best. Thanks! Thanks. Mark WF 7.6 Windows | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |