I have a requirement to build a P&L statement in WF. Naturally, I have been developing it using FML since that language is designed specifically for accounting-type reports. I have a problem, though, in that my users want to see items appear on the report that are on different levels from each other. Here is what I have:

Fact table that lists a $ amount and an accounting period date in the form of a yym format. Each fact also has a numeric pointer to one of four dimensions.

The only dimension I am really concerned about for this report is our GL Account dimension. Here is the .mas code for that synonym to give you an idea of the architecture:

 FILENAME=GLACCT_DIM, SUFFIX=DB2     , $
  SEGMENT=GLACCT_DIM, SEGTYPE=S0, $
    FIELDNAME=GL_ACCT_DIM_ID, ALIAS=GL_ACCT_DIM_ID, USAGE=I11, ACTUAL=I4, FIELDTYPE=R, $
    FIELDNAME=GL_ACCT_CLS, ALIAS=GL_ACCT_CLS, USAGE=A40V, ACTUAL=A40V, $
    FIELDNAME=GL_ACCT_TYP, ALIAS=GL_ACCT_TYP, USAGE=A40V, ACTUAL=A40V, $
    FIELDNAME=GL_CNSLDTN_ACCT, ALIAS=GL_CNSLDTN_ACCT, USAGE=A40V, ACTUAL=A40V,  $
    FIELDNAME=GL_ACCT_NM, ALIAS=GL_ACCT_NM, USAGE=A40V, ACTUAL=A40V, $
    FIELDNAME=GL_TRVL_CAT, ALIAS=GL_TRVL_CAT, USAGE=A40V, ACTUAL=A40V, $
 

The report the users want to see will sum to the CNSLDTN_ACCT level for certain cases, but also sum to the ACCT_NM level on the same report for other cases. I am totally baffled how to combine these two unlike things into one report. What I have tried is to create two separate FML reports, one that has a FOR on the ACCT_NM field and the second report has a FOR on the CNSLDTN_ACCT field. It appears that they both work independently of each other, but I dont know how to get them both to appear on a third report to bring them together. I have tried MATCH and JOINS, but there are issues with each that doesn't totally solve the problem. Does anyone have experience with something like this?

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

I would create two hold files, summing to the level you want - create a field in each hold file with the same name equal to the field in question. Then use the two hold files in your FRL report. Something like this:

TABLE FILE LACCT_DIM
SUM 
DOLLARFIELD1
DOLLARFIELD2
DOLLARFIELD3
BY GL_CNSLDTN_ACCT as 'REP_LEVEL'
WHERE certain-case-1
ON TABLE HOLD AS H001
END

TABLE FILE LACCT_DIM
SUM 
DOLLARFIELD1
DOLLARFIELD2
DOLLARFIELD3
BY GL_ACCT_NM as 'REP_LEVEL'
WHERE certain-case-2
ON TABLE HOLD AS H002
END

TABLE FILE H001
PRINT *
ON TABLE HOLD AS H003
MORE FILE H002
END

TABLE FILE H003
frl report
END

I'll try that. Thanks for the idea.

quote:

TABLE FILE H001
PRINT *
ON TABLE HOLD AS H003
MORE FILE H002
END
[/code]

I get a FOC002 error that says the word MORE is not recognized. Any idea?

Try:

TABLE FILE H001
PRINT *
ON TABLE HOLD AS H003
MORE
FILE H002
END

Sorry!

It's

MORE
FILE H002

And this must be right before the END statement

That seems to have done it. Thanks a bunch, guys.