Focal Point Banner


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
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     FML hierarchy with account detail

Read-Only Read-Only Topic
Go
Search
Notify
Tools
FML hierarchy with account detail
 Login/Join
 
Platinum Member
posted
Is there a way to include the account detail in FML output that is then sorted/subtotaled at the summary levels in the hierarchy?

Thanks,
Bethany


Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
 
Posts: 188 | Registered: April 14, 2005Report This Post
Virtuoso
posted Hide Post
Bethany

I'm not quiet sure about your question. would you be so kind to try to explain it with an example?




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Master
posted Hide Post
Beth

Think you want something like this.

FOR
SPR_SUB_PROJECT_ID
'&SUB_PROJECT_ID' WITH CHILDREN ADD AS CAPTION LABEL R1

The ADD does the trick...



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Platinum Member
posted Hide Post
Here's an example of what I'm trying to do.

If you have a hierarchy like this:

child     descriptin   parent
3000      expenses     1000
   3100   salary       3000
   3200   supplies     3000
   3300   travel       3000
4000      revenue      1000
   4100   internet     4000
   4200   mail order   4000  


And then you have account codes that are tied to the various children like this.

account   description    child
30000     executive      3100
30001     administrative 3100
30002     support        3100
30003     computer       3200
30004     paper          3200
30005     local travel   3300
30006     out of state   3300
40000     company site   4100
40001     other site     4100  
40002     mail order     4200


Then the output would have:
  
Expenses
    Salary
       30001 Executive          $1,400,000
       30002 Administrative      1,000,000
       30003 Support             2,000,000
    Total Salary                $4,400,000

    Supplies
       30004 Computer           $   50,000
       30005 Paper                   5,000
    Total Supplies              $   55,000
    
    Travel
       30006 Local Travel       $    7,500
       30007 Out of State           20,000
    Total Travel                $   27,500

Total Expenses                  $4,482,500

Revenue
    Internet                    
       40000 Company site       $5,000,000
       40001 Other site          2,000,000
    Total Internet              $7,000,000
    Mail Order
       40002 Mail Order         $1,500,000
    Total Mail Order            $1,500,000

Total Revenue                   $8,500,000



I hope this helps explain my question.

Thanks,
Bethany


Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
 
Posts: 188 | Registered: April 14, 2005Report This Post
Virtuoso
posted Hide Post
Bethany,

What you probably want to do is handle all of this with the FML syntax like:
'3100' GET CHILDREN ALL AS CAPTION LABEL R1 OVER
'3100' ADD ALL AS CAPTION LABEL R2 OVER
'3200' GET CHILDREN ALL AS CAPTION LABEL R1 OVER
'3200' ADD ALL AS CAPTION LABEL R2 OVER
'3300' GET CHILDREN ALL AS CAPTION LABEL R1 OVER
'3300' ADD ALL AS CAPTION LABEL R2 OVER
'3000' ADD ALL AS CAPTION LABEL R5


You can use BY and SUBTOTAL in combination with FML, but it gets comfusing sometimes because your accounts are all at the same level, but the "rollup" accounts only exist within the hierarchy. You could do some magic with defines and account ranges, but that's basically what the FML hierarchy functionality is giving you.


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
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
Bethany

Darin posted a code that does a lot of what you want.
If you do it that way, you will get your report but without the account codes, and without the labels.

I used Darin's code but added some extra.

TEXT "expenses" LABEL T1 OVER
TEXT "Salary" LABEL T2 OVER
'3100' GET CHILDREN ALL AS CAPTION LABEL R1 OVER
'3100' ADD ALL AS CAPTION LABEL R2 OVER
BAR "-" OVER
TEXT " " OVER
TEXT "Supplies" OVER
'3200' GET CHILDREN ALL AS CAPTION LABEL R1 OVER
'3200' ADD ALL AS CAPTION LABEL R2 OVER
'3300' GET CHILDREN ALL AS CAPTION LABEL R1 OVER
'3300' ADD ALL AS CAPTION LABEL R2 OVER
'3000' ADD ALL AS CAPTION LABEL R5


There is more but I do not all know it by hart, I will take a look tomorrow and see what I can bring up more.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Platinum Member
posted Hide Post
I haven't had a chance to look try your solutions yet, but I was wondering if I could merge the account detail into the account hierarchy by making "account" the child and "child" the parent in the hierarchy table.

Like this:
  
  child     description     parent
   3000      expenses       1000
   3100      salary         3000
   3200      supplies       3000
   3300      travel         3000
   4000      revenue        1000
   4100      internet       4000
   4200      mail order     4000  
   30000     executive      3100
   30001     administrative 3100
   30002     support        3100
   30003     computer       3200
   30004     paper          3200
   30005     local travel   3300
   30006     out of state   3300
   40000     company site   4100
   40001     other site     4100  
   40002     mail order     4200



Does this make sense?
Bethany


Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
 
Posts: 188 | Registered: April 14, 2005Report This Post
Member
posted Hide Post
I have just been working on a request that puts accounts within customized groups that have very little to do with how our account hiearchy is set up.

You can do define magic as Darin mentioned. FML may have worked but I did it with defines. I haven't worked with the FML much and I just went "define crazy".


Attached is the code I used to customize the groupings and sub totals for the report I am working on.



DEFINE FILE UNC_ORACLE_ID_FUND_ORG
ACCT_REV_EXP/A20=IF ACCOUNT_TYPE_LEVEL_1 LT '60' THEN '5' ELSE '7';
ACCT_REV_EXP_DESC/A30=IF ACCOUNT_TYPE_LEVEL_1 LT '60' THEN 'REVENUES' ELSE 'EXPENDITURES/TRANSFERS ';
EXPGRP/A2=IF ACCOUNT_LEVEL_2 EQ '51000' THEN '5A'
ELSE IF ACCOUNT_LEVEL_2 EQ '51100' THEN '5B'
ELSE IF ACCOUNT_LEVEL_2 FROM '51200' TO '51599' THEN '5C'
ELSE IF ACCOUNT_LEVEL_2 EQ '53010' THEN '5D'
ELSE IF ACCOUNT_LEVEL_2 EQ '51900' THEN '5E'
ELSE IF ACCOUNT_LEVEL_2 EQ '51600' THEN '5F'
ELSE IF ACCOUNT_LEVEL_2 EQ '51800' THEN '5G'
ELSE IF ACCOUNT_TYPE_LEVEL_2 EQ '52' OR '53' OR '54' AND ACCOUNT_LEVEL_2 NE '53010' THEN '5H'
ELSE IF ACCOUNT_LEVEL_4 EQ '56110' THEN '5I'
ELSE IF ACCOUNT_LEVEL_2 EQ '56000' AND ACCOUNT_LEVEL_4 NE '56110' THEN '5J'
ELSE IF ACCOUNT_LEVEL_1 EQ '581' AND ACCOUNT_LEVEL_2 NE '58530' THEN '5K'
ELSE IF ACCOUNT_LEVEL_1 EQ '57900' THEN '5L'
ELSE IF ACCOUNT_TYPE_LEVEL_2 EQ '55' OR '57' OR '58' AND ACCOUNT_LEVEL_1 NE '57900' OR ACCOUNT_LEVEL_1 EQ '57900' OR ACCOUNT_LEVEL_2 EQ '56600' OR '56700' OR '56800' THEN '5M'
ELSE IF ACCOUNT_LEVEL_2 EQ '61000' THEN '61'
ELSE IF ACCOUNT_LEVEL_2 EQ '62000' THEN '62'
ELSE IF ACCOUNT_LEVEL_2 EQ '63000' THEN '63'
ELSE IF ACCOUNT_LEVEL_2 EQ '64000' OR '65000' OR '66000' THEN '64'
ELSE IF ACCOUNT_LEVEL_1 EQ '680' THEN '68'
ELSE IF ACCOUNT_TYPE_LEVEL_2 EQ '71' THEN '71'
ELSE IF ACCOUNT_TYPE_LEVEL_1 EQ '70' AND ACCOUNT_TYPE_LEVEL_2 NE '77' OR '78' OR '79' AND ACCOUNT_LEVEL_1 NE '726' AND ACCOUNT_LEVEL_2 NE '72750' THEN '72'
ELSE IF ACCOUNT_TYPE_LEVEL_2 EQ '78' THEN '73'
ELSE IF ACCOUNT_LEVEL_2 EQ '72750' THEN '74'
ELSE IF ACCOUNT_LEVEL_1 EQ '726' THEN '75'
ELSE IF ACCOUNT_TYPE_LEVEL_2 EQ '77' THEN '77'
ELSE IF ACCOUNT_TYPE_LEVEL_2 EQ '79' THEN '79'
ELSE IF ACCOUNT_TYPE_LEVEL_2 EQ '91' THEN '91'
ELSE '80';
EXPGRP_LD/A39=IF EXPGRP EQ '5A' THEN 'Resident Tuition'
ELSE IF EXPGRP EQ '5B' THEN 'Non-Resident Tuition'
ELSE IF EXPGRP EQ '5C' THEN 'Other Tuition'
ELSE IF EXPGRP EQ '5D' THEN 'Fee For Service (COF)'
ELSE IF EXPGRP EQ '5E' THEN 'Long Bill Appropriated Fees'
ELSE IF EXPGRP EQ '5F' THEN 'Scholarship Allowance'
ELSE IF EXPGRP EQ '5G' THEN 'Student Fees'
ELSE IF EXPGRP EQ '5H' THEN 'Grants and Contracts'
ELSE IF EXPGRP EQ '5I' THEN 'Room and Board'
ELSE IF EXPGRP EQ '5J' THEN 'Other Auxiliary Services Revenue'
ELSE IF EXPGRP EQ '5K' THEN 'Investment Income'
ELSE IF EXPGRP EQ '5L' THEN 'Other Internal Income'
ELSE IF EXPGRP EQ '5M' THEN 'Other Revenue'
ELSE IF EXPGRP EQ '61' THEN 'Faculty Salaries'
ELSE IF EXPGRP EQ '62' THEN 'Exempt Salaries'
ELSE IF EXPGRP EQ '63' THEN 'Classified Salaries'
ELSE IF EXPGRP EQ '64' THEN 'Other Salaries'
ELSE IF EXPGRP EQ '68' THEN 'Fringe Benefits'
ELSE IF EXPGRP EQ '71' THEN 'Cost of Sales'
ELSE IF EXPGRP EQ '72' THEN 'Other Current Expense'
ELSE IF EXPGRP EQ '73' THEN 'Scholarships'
ELSE IF EXPGRP EQ '74' THEN 'Utilities'
ELSE IF EXPGRP EQ '75' THEN 'Risk Management'
ELSE IF EXPGRP EQ '77' THEN 'Travel'
ELSE IF EXPGRP EQ '79' THEN 'Capital'
ELSE IF EXPGRP EQ '80' THEN 'Other'
ELSE IF EXPGRP EQ '91' THEN 'Transfers';
END
TABLE FILE UNC_ORACLE_ID_FUND_ORG
SUM
BASE_BDGT
BY ACCT_REV_EXP NOPRINT
BY ACCT_REV_EXP_DESC NOPRINT
BY ACCOUNT_TYPE_LEVEL_1 NOPRINT
BY ACCOUNT_TYPE_DESC_1
BY EXPGRP
BY EXPGRP_LD
ACROSS FUND1 NOPRINT
ACROSS-TOTAL
ACROSS FUND1_LD AS ''
ACROSS FUND2 NOPRINT
ACROSS FUND2_LD AS ''
ON ACCT_REV_EXP_DESC SUBTOTAL AS '*TOTAL'
ON ACCOUNT_TYPE_DESC_1 SUBTOTAL AS '*TOTAL'


WebFOCUS 7.6.4
Windows XP against an Oracle Database mainly SCT Banner Higher Education ODS and EDW
Various output formats


Guy Brenckle
Budget Analyst
University of Northern Colorado
 
Posts: 25 | Location: Greeley Colorado | Registered: October 08, 2007Report This Post
Virtuoso
posted Hide Post
quote:
Does this make sense?


I think it does Bethany.

See if you can find something on the formulas FMLLIST('Ann') or FMLCAP('Ann') It might help you in some drill through cases.
If you build the report as mentioned above you will not see the accountcodes.

and for Guy....

I would do it the way you did.....if there was not FML. FML is invented for this.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     FML hierarchy with account detail

Copyright © 1996-2020 Information Builders