I am designing a financial report based on a chart of accounts using FML in Developer Studio. The chart of accounts is located in the �LEDGERTABLE� and transactions are in the �LEDGERTRANS� table.

The problem is whenever I run the report based on a date range; accounts without an entry in the �LEDGERTRANS� table are not showing up on the report! However I need all accounts to be displayed on the report irrespective of if there is an entry or not.

Please provide any ideas on how to resolve this issue. Listed below is the code I am using:


-DEFAULTS &LDATE1='6/1/2005';
-DEFAULTS &LDATE2='6/30/2005';

SET ALL=ON
SET NODATA = ' '
SET MULTIPATH = SIMPLE

JOIN
LEDGERTABLE.LEDGERTABLE.ACCOUNTNUM IN ledgertable TO ALL
LEDGERTRANS.LEDGERTRANS.ACCOUNTNUM IN ledgertrans AS J0
END

DEFINE FILE LEDGERTABLE
TRDATE/MDYY=HDATE(TRANSDATE , 'MDYY');
END

TABLE FILE LEDGERTABLE
SUM
'LEDGERTABLE.ACCOUNTNAME'
'LEDGERTRANS.AMOUNTCUR'
FOR
LEDGERTRANS.ACCOUNTNUM
'101000' AS '101000' LABEL R1 OVER
'102000' AS '102000' LABEL R2 OVER
'102200' AS '102200' LABEL R3 OVER
'102100' AS '102100' LABEL R4 OVER
'102500' AS '102500' LABEL R5 OVER
'102700' AS '102700' LABEL R6 OVER
BAR AS '-' OVER
RECAP R10=R3+R4+R5+R6+R7+R8;
AS 'TOTAL SALES ' OVER
'110000' AS 110100' LABEL R11 OVER
'110100' AS '110100' LABEL R12 OVER
'110200' AS '110200' LABEL R13 OVER
'110500' AS '110500' LABEL R14 OVER
'111000' AS '111000' LABEL R15 OVER
'112000' AS '112000' LABEL R16 OVER
'113000' AS '113000' LABEL R17 OVER
'113500' AS '113500' LABEL R18 OVER
'114000' AS '114000' LABEL R19
WHERE (TRDATE GE '&LDATE1') AND (TRDATE LE '&LDATE2' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
ENDSTYLE
END

Maybe try SET ALL=PASS

Ken

Roger,

does not

FOR
LEDGERTABLE.ACCOUNTNUM

work?

H�kan

Hi Guys,

I have applied the suggestions "SET ALL=PASS" and "FOR LEDGERTABLE.ACCOUNTNUM" also removed the date range filter and the report returns all accounts...thank you.

However when i reapply the date range filter, accounts without an entry in the �LEDGERTRANS� table are still not showing up on the report! Please provide any suggestions how to resolve the date range issue.

Here's where I'm confused. Generally, if you name an account (in your case) within a FOR (fieldname), it will appear, even if no data exists within the table at all.

I ran an FML request from the ledger.mas db in ibisamp - something like this:

TABLE FILE LEDGER
SUM AMOUNT
FOR ACCOUNT
'1010' AS '1010' LABEL R1 OVER
'1015' AS '1015' LABEL R2 OVER
'1020' AS '1020' LABEL R2
END

This query returned three rows, one each for 1010, 1015 and 1020, even though 1015 does not exist in ledger.foc. The amount associated with 1015 is missing.

So from what you've posted, the only thing coming to mind right now...and keep in mind I'm only on my first cup of caffeine, is that maybe it's something to do with the join.

Maybe what you can do is to issue the join, extract the data into a hold file using the where clauses and then run the FML request from the HOLD file.

Could that do the trick?

Ken

Ken,

I have managed to resolve the issue by extracting the data using two hold files, 1 hold file for the chart of accounts and another for the transactions, then running the FML request from the HOLD files.

Thank you.

Roger

Hi Roger

You might want to check the syntax of HDATE - I was sort of thinking that it only worked with YYMD as a format?

Check it out:

http://techsupport.informationbuilders.com/ibase/master...t_520/datetime14.htm

-Toby

Roger,

I take it that TRDATE is in the LEDGERTRANS table? If so the whole reason that the date predicate was suppressing the data was that for those rows the TRDATE wasn't in the range, in fact it was NULL. So test for that as well -

WHERE (TRDATE GE '&LDATE1' AND TRDATE LE '&LDATE2') OR TRDATE IS MISSING ;

Or similar depending upon your date format e.g. A6MDY, MDY, MDYY, etc.

When Focus reads child segments obtained from the join, and ALL = PASS is in effect, then there is a difference between when there are no "child" records matching the join key, and when there are some child records but they each fail a screening condition.

In the former case, Focus recognizes a short-path condition, and will print the parent; but the latter case is not considered a short path, and (since the combined record fails the screening condition) the parent record is not printed.

If the lookup file is a relational table, there is documentation that states that ALL=PASS is inoperative.