1 / 41

FOR and the FML Syntax

FOR and the FML Syntax. Noreen Redden Information Builders FOCUS Users of New England. Financial Reporting Agenda. General Accounting Concepts Why FML? Requirements for Parent-Child Reporting Financial Report Painter Preparing the Data Examples Questions.

amil
Download Presentation

FOR and the FML Syntax

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. FOR and the FML Syntax Noreen Redden Information Builders FOCUS Users of New England

  2. Financial ReportingAgenda • General Accounting Concepts • Why FML? • Requirements for Parent-Child Reporting • Financial Report Painter • Preparing the Data • Examples • Questions

  3. Financial ReportingGeneral Accounting Concepts • Creating specific types of reports (eg. Income Statement, Balance Sheet) • The rows on Financial reports are made up of General Ledger Accounts with measurement values • (eg. Actual, Plan, Forecast) • General Ledger contains Account transactions • (eg. Debit/Credit entries) • Chart of Accounts is the G/L Account hierarchy

  4. Financial ReportingGeneral Accounting Concepts • Total of Debits must equal Total of Credits Transactions for buying a building: G/L AccountDescriptionDebitCredit 10005 Building 1,000,000 10001 Cash 200,000 20008 Mortgage 800,000

  5. Financial ReportingGeneral Accounting Concepts Chart of Accounts Assets Current Assets Cash Long Term Assets Building Liabilities Long Term Liabilities Mortgage Payable

  6. Financial ReportingGeneral Accounting Concepts • The signs of the numbers may be negative for certain types of accounts • Revenue • Liability • Stockholder’s Equity • However, they need to be presented as non-negative numbers on the report

  7. Financial ReportingWhat’s the difference •  In Typical Reporting: •  Report Lines appear in Sort Order •  Lines Appear Only for values retrieved from File •  Free Text can only be inserted on Sort Breaks •  Inter-Row calculations can only be performed at Sort Breaks (RECAP)

  8. What is Financial Reporting?What’s the difference •  In Financial Reporting: • Specific Report Lines can be placed anywhere on the page – regardless of sort sequence •  Report Lines can appear in report regardless on whether there is any Amount in the General Ledger • Free Text can be inserted on any line of the page •  Inter-Row calculations can performed simply • Complex formatting

  9. Financial ReportingRequirements for Parent-Child Reporting • Graphical representation of the Parent/Child hierarchy • Each element contains a pointer to the next element.

  10. Financial ReportingRequirements for Parent-Child Reporting (cont’d) • Hierarchy Requirements • Account Code • Account Parent • Account Description • Usually requires an additional process to create this structure • New structure is then joined to the data to be reported upon

  11. Financial ReportingMaster File Keywords • REFERENCE=fieldname • Where fieldname identifies FIELDNAME within the Master File that contains the Child value. • PROPERTY=PARENT_OF • Marks the fieldname as containing the Parent value of the field identified in the REFERENCE attribute • PROPERTY=CAPTION • Marks the field as containing the Description value of the field identified in the REFERENCE attribute

  12. Financial ReportingParent-Child Rules • Each parent must be stored as a child • A child may have only one parent • Top level must point to a blank parent • Child and parent fields must have the same format • If you qualify the field names in the references, you MUST use those referenced field names in the request

  13. Financial ReportingHierarchy Examples Chart of Accounts ACCOUNTACCOUNT PARENTACCOUNT CAPTION 1000 Profit Before Tax 2000 1000 Gross Margin 2100 2000 Sales Revenue 2200 2100 Retail Sales 2210 2200 Retail – Television Organization Chart Employee IDManager IDTitle 001 CEO 002 001 President 003 002 VP Sales 004 003 Dir East Coast Sales 005 003 Dir West Coast Sales 006 004 NE Sales Manager

  14. Financial ReportingThe Painter • Starting a Financial Report • In the Report Painter: • Add the fields you want to include in the report, including the field that you intend to designate as your FOR field • The FOR field is the field that contains the Child account value • You can include BY and/or Across sort fields • Headings, footings,calculated values, and images are usable • Select the field that you want to use as the FOR field, and click the For button on the Report Painter Columns toolbar • Report can only contain one FOR field • Click the Matrix tab at the bottom of the window • The Financial Report Painter's Design matrix opens

  15. Financial ReportingThe Painter Highlight the column, click the For button then the Matrix tab

  16. Financial ReportingThe Painter This view shows an expanded hierarchy and a section of the report

  17. Financial ReportingChildren/Consolidate Options You open the tag dialog by right-clicking on a row and selecting row properties

  18. Financial ReportingChildren/Consolidate Options (cont’d) • With Children • Shows a total for the groups parent at the start of each grouping • Get Children • Shows the figures for children of that entry. Additional groups would appear as above. • Consolidate • Instructs WebFOCUS to calculate a total for each of the parent entries or the specific parent.

  19. Financial ReportingAdding Rows Together You can also create dynamic hierarchies using FML. In this example, we create a report showing totals by the existing hierarchy and a proposed hierarchy.

  20. Financial ReportingRECAP You open the recap dialog by either right-clicking on a row or clicking on the tool bar

  21. Financial ReportingRECAP (cont’d) You can also create recaps by clicking on a cell, then, while holding down the shift key, you can drag a reference to that cell elsewhere. Note, that by default, a “+” is used. You can change that to whatever you need it to be.

  22. Financial ReportingPreparing the data Source – Level Based Hierarchy Output – Parent/Child Description Lev 1 Lev 2 Lev 3 Lev 4 Lev 5 Description Parent Child Net Profit 1000 ____ ____ ____ ____ Net Profit 1000 1000 Gross Margin 1000 2000 ____ ____ ____ Gross Margin 2000 Sales Revenue 1000 2000 2100 ____ ____ Operating Expenses 3000 1000 Retail Sales 1000 2000 2100 2200 ____ 2100 Sales Revenue 2000 Retail TV Sales 1000 2000 2100 2200 2210 3100 3000 Selling Expenses Retail Stereo Sales 1000 2000 2100 2200 2220 2100 Retail Sales 2200 Mail Order Sales 1000 2000 2100 2300 ____ 2100 Mail Order Sales 2300 Mail Order TV Sales 1000 2000 2100 2300 2310 Advertising 3100 3110 Mail Order St Sales 1000 2000 2100 2300 2320 Retail TV Sales 2210 2200 Operating Expenses 1000 3000 ____ ____ ____ Retail Stereo Sales 2220 2200 Selling Expenses 1000 3000 3100 ____ ____ 2300 Mail Order TV Sales 2310 Advertising 1000 3000 3100 3110 ____ 2300 Mail Order St Sales 2320 2nd Pass 4th Pass 1st Pass 3rd Pass 5th Pass

  23. Financial ReportingPreparing the data (cont’d) FILENAME=ACCTLVL, SUFFIX=FOC , $ SEGMENT=ACCTLVL,SEGTYPE=S1, $ FIELDNAME=FOCLIST , ALIAS=E01, USAGE=I5 , $ FIELDNAME=ACCT_LVL1, ALIAS=E02, USAGE=A4, $ FIELDNAME=ACCT_LVL2, ALIAS=E03, USAGE=A4, $ FIELDNAME=ACCT_LVL3, ALIAS=E04, USAGE=A4, $ FIELDNAME=ACCT_LVL4, ALIAS=E05, USAGE=A4, $ FIELDNAME=ACCT_LVL5, ALIAS=E06, USAGE=A4, $ FIELDNAME=ACCT_LVL6, ALIAS=E07, USAGE=A4, $ FIELDNAME=DESCR, ALIAS=E08, USAGE=A44,$ FILENAME=ACCTPC , SUFFIX=FOC , $ SEGMENT=ACCTPC, SEGTYPE=S1, $ FIELDNAME=DESCRIPTION, ALIAS=E01, USAGE=A44, REFERENCE=CHILD_ACCOUNT, PROPERTY=CAPTION,$ FIELDNAME=PARENT_ACCOUNT, ALIAS=E02, USAGE=A4 , REFERENCE=CHILD_ACCOUNT, PROPERTY=PARENT_OF,$ FIELDNAME=CHILD_ACCOUNT, ALIAS=E03, USAGE=A4, $

  24. TABLE FILE filenamePRINT/SUMFOR hierarchyfld parentvalue GET/WITH CHILD(REN) (n) ALL AS CAPTION/’text’ LABEL label … hierarchyfld the hierarchy field name or, field name from the host file(JOIN)parentvalue the parent value for which the children are to be retrieved Financial ReportingSyntax

  25. parentvalue GET/WITH CHILD(REN) (n) ALL AS CAPTION/’text’ LABEL label GET CHILDREN displays the hierarchy starts with the first child of the parent value does NOT include the parent in the display … WITH CHILDREN displays the hierarchy starts with the first child of the parent value DOES include the parent in the display  (n) ALL Integer value from 1 to 99 How many levels of the hierarchy to display Warning message if 999 levels are exceeded ALL is a synonym for up to 99 levels Levels of the hierarchy are indented at each level Financial ReportingSyntax

  26. parentvalue GET/WITH CHILD(REN) (n) ALL AS CAPTION/’text’ LABEL label CAPTION The caption value to display Taken from the CAPTION field in the Master File Description‘text’ A text string which will be used for the Row Title Does NOT use the CAPTION field, if usedLABEL Regular FML syntax – assigns an explicit row label Financial ReportingSyntax

  27. PIN REPORTSTO TITLE FULLNAME --- --------- ----- -------- 000000010 000000040 MARKETING EXECUTIVE VALINO, DANIEL 000000020 000000340 INDUSTRIAL MARKETER BELLA, MICHAEL 000000030 000000050 SALES MANAGER CASSANOVA, LOIS 000000040 000000200 MARKETING DIRECTOR ADAMS, RUTH 000000050 000000200 EXECUTIVE MANAGER ADDAMS, PETER 000000060 000000200 MARKETING DIRECTOR PATEL, DORINA 000000070 PRESIDENT SANCHEZ, EVELYN 000000080 000000340 SENIOR SALES EXEC. SO, PAMELA 000000090 000000250 EMPLOYEE COORDINATOR PULASKI, MARIANNE 000000100 000000370 SUPERVISOR OF AP/AR ANDERSON, TIM 000000110 000000160 PRODUCT DISTRIBUTOR RUSSO, ANTHONY 000000120 000000230 CORPORATE CONSULTANT WANG, KATE 000000130 000000340 MANAGER CVEK, MARCUS 000000140 000000350 MANAGER WHITE, VERONICA 000000150 000000120 SENIOR CONSULTANT WHITE, KARL Financial ReportingExamples Hierarchy sample data

  28. FILENAME=ORGCHART, SUFFIX=FOC , $ SEGMENT=SEG01, SEGTYPE=S1, $ FIELDNAME=PIN, ALIAS=E01, USAGE=A9, FIELDTYPE=I, $ FIELDNAME=REPORTSTO, ALIAS=E02, USAGE=A9, PROPERTY=PARENT_OF,REFERENCE=PIN,$ FIELDNAME=TITLE, ALIAS=E03, USAGE=A20,$ FIELDNAME=FULLNAME, ALIAS=E04, USAGE=A50, PROPERTY=CAPTION,REFERENCE=PIN,$ Financial ReportingExamples Master File Description (Synonym) – USEORG.MAS

  29. JOIN PIN IN USEORG TO PIN IN EMPDATA AS J1TABLE FILE USEORGCOUNT ENTRIES SUM.SALARYFOR 'USEORG.SEG01.PIN''000000070' ON TABLE SET FORMULTIPLE ONON TABLE NOTOTALON TABLE SET BLANKINDENT ON Financial ReportingExamples COUNT SALARY ----- ------ 000000070 1 $83,000.00

  30. JOIN PIN IN USEORG TO PIN IN EMPDATA AS J1TABLE FILE USEORGCOUNT ENTRIES SUM.SALARYFOR 'USEORG.SEG01.PIN''000000070' OVER'000000080' INDENT 1 Financial ReportingExamples COUNT SALARY ----- ------ 000000070 1 $83,000.00 000000080 1 $43,400.00

  31. JOIN PIN IN USEORG TO PIN IN EMPDATA AS J1TABLE FILE USEORGCOUNT ENTRIES SUM.SALARYFOR 'USEORG.SEG01.PIN' '000000070' WITH CHILDREN Financial ReportingExamples COUNT SALARY ----- ------ 000000070 1 $83,000.00 000000200 1 $115,000.00 000000230 1 $80,500.00

  32. JOIN PIN IN USEORG TO PIN IN EMPDATA AS J1TABLE FILE USEORGCOUNT ENTRIES SUM.SALARYFOR 'USEORG.SEG01.PIN' '000000070' GET CHILDREN Financial ReportingExamples COUNT SALARY ----- ------ 000000200 1 $115,000.00 000000230 1 $80,500.00

  33. JOIN PIN IN USEORG TO PIN IN EMPDATA AS J1TABLE FILE USEORGCOUNT ENTRIES SUM.SALARYFOR 'USEORG.SEG01.PIN' '000000070' WITH CHILDREN 2 Financial ReportingExamples COUNT SALARY ----- ------ 000000070 1 $83,000.00 000000200 1 $115,000.00 000000040 1 $62,500.00 000000050 1 $54,100.00 000000060 1 $55,500.00 000000160 1 $62,500.00 000000270 1 $42,900.00 000000340 1 $58,800.00 000000230 1 $80,500.00 000000120 1 $49,500.00 000000170 1 $30,800.00 000000300 1 $79,000.00

  34. JOIN PIN IN USEORG TO PIN IN EMPDATA AS J1TABLE FILE USEORGCOUNT ENTRIES SUM.SALARYFOR 'USEORG.SEG01.PIN' '000000070' WITH CHILDREN ALL Financial ReportingExamples COUNT SALARY ----- ------ 000000070 1 $83,000.00 000000200 1 $115,000.00 000000040 1 $62,500.00 000000010 1 $55,500.00 000000310 1 $52,000.00 000000050 1 $54,100.00 000000030 1 $70,000.00 000000190 1 $39,000.00 000000210 1 $43,600.00 000000330 1 $30,500.00 000000360 1 $43,000.00 000000060 1 $55,500.00 000000320 1 $35,200.00 000000160 1 $62,500.00 000000110 1 $19,300.00 000000350 1 $54,100.00 000000140 1 $62,500.00

  35. JOIN PIN IN USEORG TO PIN IN EMPDATA AS J1TABLE FILE USEORGCOUNT ENTRIES SUM.SALARYFOR 'USEORG.SEG01.PIN' '000000070' WITH CHILDREN ALL ADD Financial ReportingExamples COUNT SALARY ----- ------ 000000070 37 $1,892,900.00 000000200 26 $1,346,600.00 000000040 3 $170,000.00 000000010 1 $55,500.00 000000310 1 $52,000.00 000000050 6 $280,200.00 000000030 1 $70,000.00 000000190 1 $39,000.00 000000210 1 $43,600.00 000000330 1 $30,500.00 000000360 1 $43,000.00 000000060 2 $90,700.00 000000320 1 $35,200.00 000000160 4 $198,400.00 000000110 1 $19,300.00 000000350 2 $116,600.00 000000140 1 $62,500.00

  36. JOIN PIN IN USEORG TO PIN IN EMPDATA AS J1TABLE FILE USEORGCOUNT ENTRIES SUM.SALARYFOR 'USEORG.SEG01.PIN' '000000070’ ADD Financial ReportingExamples COUNT SALARY ----- ------ 000000400 37 $1,892,900.00

  37. '000000070' WITH CHILDREN ALL Financial ReportingExamples COUNT SALARY ----- ------ 000000070 1 $83,000.00 000000200 1 $115,000.00 000000040 1 $62,500.00 000000010 1 $55,500.00 000000310 1 $52,000.00 000000050 1 $54,100.00 000000030 1 $70,000.00 000000190 1 $39,000.00 000000210 1 $43,600.00 000000330 1 $30,500.00 000000360 1 $43,000.00 000000060 1 $55,500.00 000000320 1 $35,200.00 000000160 1 $62,500.00 000000110 1 $19,300.00 000000350 1 $54,100.00 000000140 1 $62,500.00 COUNT SALARY ----- ------ 000000400 37 $1,892,900.00 '000000070' ADD

  38. JOIN PIN IN USEORG TO PIN IN EMPDATA AS J1TABLE FILE USEORGCOUNT ENTRIES SUM.SALARYFOR 'USEORG.SEG01.PIN' '000000070' WITH CHILDREN ALL OVER '000000070' ADD ON TABLE SET FORMULTIPLE ON Financial ReportingExamples COUNT SALARY ----- ------ 000000070 1 $83,000.00 000000200 1 $115,000.00 000000040 1 $62,500.00 000000010 1 $55,500.00 000000310 1 $52,000.00 000000050 1 $54,100.00 000000030 1 $70,000.00 000000190 1 $39,000.00 . . . 000000100 1 $32,400.00 000000400 1 $26,400.00 000000400 37 $1,892,900.00

  39. JOIN PIN IN USEORG TO PIN IN EMPDATA AS J1TABLE FILE USEORGCOUNT ENTRIES SUM.SALARYFOR 'USEORG.SEG01.PIN' '000000070' WITH CHILDREN ALL AS CAPTION OVER '000000070' ADD AS ’*TOTAL’ ON TABLE SET FORMULTIPLE ON Financial ReportingExamples COUNT SALARY ----- ------ SANCHEZ, EVELYN 1 $83,000.00 LASTRA, KAREN 1 $115,000.00 ADAMS, RUTH 1 $62,500.00 VALINO, DANIEL 1 $55,500.00 LIEBER, JEFF 1 $52,000.00 ADDAMS, PETER 1 $54,100.00 CASSANOVA, LOIS 1 $70,000.00 MEDINA, MARK 1 $39,000.00. . . WANG, JOHN 1 $62,500.00 ANDERSON, TIM 1 $32,400.00 LOPEZ, ANNE 1 $26,400.00 *TOTAL 37 $1,892,900.00

  40. SET CNOTATION=EXPLICITTABLE FILE GGSALESSUM UNITSACROSS DATECOMPUTE QTRCHG/I8=0; AS 'Quarter Change'FOR REGION'Midwest' LABEL MW OVERRECAP MW(5)=MW(4) - MW(1); OVER'West' LABEL W OVERRECAP W(5)=W(4) - W(1); OVERRECAP COMBTOT/I8=MW + W ; AS 'Total'WHERE DATE GE 19970901END Financial ReportingExamples Date 1997/09/01 1997/10/01 1997/11/01 1997/12/01 Quarter Change ------------------------------------------------------------------------- Midwest 40684 39563 35466 44813 4129 West 35030 41930 37298 37294 2264 Total 75714 81493 72764 82107 6393

  41. Questions Thanks for Coming

More Related