Hi all,
I am relatively new to this feature, but what I see so far is exactly what I need to build my Profit & Loss Statement. I am just a little confused on how I should go about defining my metadata to accept the Parent/Child correctly.

Here is an example of my file/data:
GLTRAN_FCT - Base table that stores transaction amounts
Structure:

  FILENAME=GLTRAN_FCT, SUFFIX=DB2     , $
  SEGMENT=GLTRAN_FCT, SEGTYPE=S0, $
    FIELDNAME=GLTRANFCT_ID, ALIAS=GLTRANFCT_ID, USAGE=I11, ACTUAL=I4, FIELDTYPE=R, $
    FIELDNAME=GL_ACCT_DIM_ID, ALIAS=GL_ACCT_DIM_ID, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=ORG_DIM_ID, ALIAS=ORG_DIM_ID, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=DY_DIM_ID, ALIAS=DY_DIM_ID, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=PRD_DIM_ID, ALIAS=PRD_DIM_ID, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=AccountingPeriod, ALIAS=ACCTG_PER_YRMTH, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=Description, ALIAS=TRAN_DESC, USAGE=A80V, ACTUAL=A80V,
      MISSING=ON, $
    FIELDNAME=TransactionAmount, ALIAS=TRAN_AMT, USAGE=P12.2, ACTUAL=P6, $

All of the fields named %_Dim_ID are Foreign keys that match to other files defined in the metadata, but they don't have any meaning for this problem.

I also have a view that is used for reporting which tells me the Section(Revenue, Expenses, etc), a Header(Mortgage Insurance Products, Credit Union Products, etc) and the textual name (GL_ACCT_TITLE) of a specific account that matches to the GL_ACCT_DIM_ID.

Structure of view:

 FILENAME=v50incstatctl, SUFFIX=DB2     , $
  SEGMENT=V50INCSTATCTL, SEGTYPE=S0, $
    FIELDNAME=SECTION, ALIAS=SECTION, USAGE=A8V, ACTUAL=A8V, $
    FIELDNAME=HDRSEQ, ALIAS=HDRSEQ, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=HDR, ALIAS=HDR, USAGE=A47V, ACTUAL=A47V,
      REFERENCE=GL_ACCT_DIM_ID, PROPERTY=PARENT_OF,  $
    FIELDNAME=LINSEQ, ALIAS=LINSEQ, USAGE=P7, ACTUAL=P8,
      MISSING=ON, $
    FIELDNAME=ACT_BUD, ALIAS=ACT_BUD, USAGE=A40V, ACTUAL=A40V, $
    FIELDNAME=ACCT_TYP, ALIAS=ACCT_TYP, USAGE=A1V, ACTUAL=A1V, $
    FIELDNAME=ACCT_TITLE, ALIAS=ACCT_TITLE, USAGE=A40V, ACTUAL=A40V,
      REFERENCE=GL_ACCT_DIM_ID, PROPERTY=CAPTION,  $
    FIELDNAME=GL_ACCT_DIM_ID, ALIAS=GL_ACCT_DIM_ID, USAGE=I11, ACTUAL=I4,
      WITHIN='*Dimension1',
      PROPERTY=UID,  $
 DIMENSION=Dimension1, CAPTION='Dimension1', $
  HIERARCHY=Dimension1, CAPTION='Dimension1', HRY_DIMENSION=Dimension1, HRY_STRUCTURE=RECURSIVE, $ 

You can see where I've tried to define the Parent/Child, but I am getting improper results when I try to use this.

The data in the view looks something like this:

REVENUE	5 COST OF SALES	4980	ACTUAL	C	AGENT COMMISSIONS	         5818
REVENUE	5 COST OF SALES	4972	ACTUAL	C	ALLOCATED INTERCMPY INCOME	5759
REVENUE	5 COST OF SALES	4972	ACTUAL	C	ALLOCATED INTERCMPY INCOME	5760
REVENUE	5 COST OF SALES	4972	ACTUAL	C	ALLOCATED INTERCMPY INCOME	5761
REVENUE	5 COST OF SALES	4972	ACTUAL	C	ALLOCATED INTERCMPY INCOME	5762
EXPENSES	10 OVERHEAD/ADMIN	9021	BUDGET	D	BRANCH LN LOSS RES	         7440
EXPENSES	10 OVERHEAD/ADMIN	9013	BUDGET	D	PROFIT SHARING	         7439
EXPENSES	11 TAXES	         9970	ACTUAL	C	FIT	                  6035
EXPENSES	11 TAXES	         9970	ACTUAL	C	DEFERRED TAX EXPENSE	6036  

So when I run my report, I want to see:

Revenue
  Cost of Sales
        Agent Commissions    &1,200
        Allocated Inter...   $2,564
      Total Cost of Sales    $9,999
    -------------------------------
Expenses 
   Overhead/Admin
      BRANCH LN LOSS RES     $1,000
      PROFIT SHARING         $9,000

When I use the setup that I have right now, I put the "FOR" on my HDR which is my parent. I would expect, when I run the report, the Acct Titles would all appear for each HDR I place in the FML report, correct? I am just testing here, but I TAG my Cost of Sales and Overhead/Admin, but instead, I get something that looks like this:

   BRANCH LN LOSS RES            Overhead/Admin     $1,000
                                 Cost of Sales           .
   PROFIT SHARING                Overhead/Admin     $9,000
                                 Cost of Sales           .
   Agent Commissions             Overhead/Admin          .                             
                                 Cost of Sales      $1,200
   Allocated Inter...            Overhead/Admin          . 
                                 Cost of Sales      $1,000

This message has been edited. Last edited by: T.Peters,

I want to add that since this is defined in a VIEW, I am open to changing how everything is organized if it will make things easier.

Have you reviewed the following article?
It might help.

http://www.informationbuilders.../04_parentchild.html

Efrem, I hadn't read that article specifically, but I've been reading all the documentation provided in the Help on Dev Studio about creating FML reports and it's pretty similar to what you posted.

It SEEMS as if what I have coded should work, and maybe I'm just not building the report right. The GL_ACCT_DIM_ID is completely unique in the view, and I set that as the hierarchy field with a parent of HDR and caption as ACCT_TITLE.

Hi, the child field needs to be indexed and the field formats for the parent and child must be identical and each child can only have one parent. Your formats do not match nor did you index the child field. I personally have always used a FOCUS file to hold the data for a hierarchy.
[quote]FIELDNAME=GL_ACCOUNT, ALIAS=GLACCT, FORMAT=A7,
TITLE='Ledger,Account', FIELDTYPE=I, $
FIELDNAME=GL_ACCOUNT_PARENT, ALIAS=GLPAR, FORMAT=A7,
TITLE=Parent,
PROPERTY=PARENT_OF, REFERENCE=GL_ACCOUNT, $
quote]

Hope this helps.

Kathy, thank you, that is exactly what I need.

The report seems to come out right as far as displaying the parents/children, but I'm a little unsure of how to use the RECAP feature. When I go to the MATRIX tab in Report Painter, I TAG the Parent field that I want, but I want the ability to store the total of all the ACCT_TITLEs in one of the Labels. I did a RECAP R1, but this gives me:

 
REVENUE  <--Section 
 MORTGAGE INSURANCE PRODUCTS <--HDR  
   MANAGEMENT FEE             $1,200
                              $1,200
   TRACKING FEE INCOME        $2,000
                              $2,000            

I was hoping to get the total of ALL the Acct Titles, not a total for each one.

This message has been edited. Last edited by: T.Peters,

Hi,
Reference this in the search and it will give you what you want. Basically, you need to include the word ADD in your hierarchy stmt.

Syntax: How to Create One Summary Row for an FML Hierarchy
TABLE FILE filename
SUM....
FOR hierarchyfld
parentvalue ADD [n|ALL] [AS CAPTION|'text'] [LABEL label]
.
.
.
END

Hope this helps.