I have a procedure that drills to another using FML account numbers.
In some rows, the accounts are defined and separated with "OR", in others, it's a range separated by "TO". I'm trying to find something i can use in a where clause that works for both expressions. I'm using an FMLLIST to break accounts in an ADD ALL row, but it doesn't do the same in the "TO" range rows.
Some rows have a mix of ORs and TOs which exausted my little FOCUS code knowledge.

Here is sample of the rows i'm drilling on:

===========================
COMPUTE LOOKUP/A2 = 'LU'; AS 'LU'
COMPUTE C_FMLFOR/A18 = FMLFOR('A18'); NOPRINT
COMPUTE C_DDFLAG/A1 = IF C_FMLFOR EQ MISSING THEN '' ELSE 'A'; NOPRINT
COMPUTE C_FMLLIST/A500 = FMLLIST('A500'); NOPRINT
COMPUTE C_FMLCAP/A50 = FMLCAP('A50'); NOPRINT
FOR
'ACCTTREE.SEG01.CHILD'
' 4101' TO ' 4201 9 99 999' AS CAPTION LABEL R1 NOPRINT OVER
"Sales" LABEL R2 OVER
' 4101 1' ADD ALL AS 'Sales - Deli' LABEL R3 WHEN EXISTS OVER
' 4101 2' ADD ALL AS 'Sales - Bakery' LABEL R4 WHEN EXISTS OVER
' 4101 3' ADD ALL AS 'Sales - Grocery' LABEL R5 WHEN EXISTS OVER
' 4101 6' TO ' 4101 89999' AS 'Sales - Seafood/Meat' LABEL R8 WHEN EXISTS OVER
BAR AS '-' OVER
"Gross Profit" LABEL R14 OVER
' 4101 1' TO ' 4101 1 800' OR ' 4501 1 1' OR ' 5101 1' TO ' 5101 1 800' OR ' 5101 1 1' TO ' 5101 1 1 800' AS 'Gross Profit - Deli' LABEL R15 WHEN EXISTS OVER

============================


The Where statement in the Drill To procedure:

WHERE ( ACCTTREE.SEG01.CHILD EQ &ACCOUNT);



Hope this makes sense.

This message has been edited. Last edited by: Kerry,

Try: WHERE (ACCTTREE.SEG01.CHILD EQ FMLFOR) OR
( C_FMLLIST CONTAINS ACCTTREE.SEG01.CHILD)

I should have added my drill down code:


TYPE=DATA,
COLUMN=N18,
TARGET='_blank',
JUSTIFY=RIGHT,
FOCEXEC=app/income_report_summary1( \
ACCOUNT=N21 \
CAPTION=N22 \
YEAR=&YEAR \
PERIOD=&PERIOD \
),
$

This is what each function sends for the ACCOUNT variable:

FMLLIST:
' 4101 6' TO ' 4101 89999'

FMLFOR (only the first account):
4101 6

This simple FML fex illustrates how FMLLIST does not live up to what it promises, "FMLLIST returns a string containing the complete tag list for each row in an FML request":

TABLE FILE LEDGER
SUM 
AMOUNT

COMPUTE LIST1/A36 = FMLLIST('A4096V');

FOR ACCOUNT
'1000' TO '1060' OR '1099' AS 'PART 1' OVER
'1100' TO '1299' AS 'PART 2'

END

I would think you need to break down and just do some hard coding here. Since you have to manually code a drill-through anyway on each of the FOR items, why don't you do something like this:

 
TYPE=DATA,
COLUMN=N18,
TARGET='_blank',
JUSTIFY=RIGHT,
FOCEXEC=app/income_report_summary1( \
MAX_ACCOUNT='4101 6' \
MIN_ACCOUNT='4101 89999' \
CAPTION=N22 \
YEAR=&YEAR \
PERIOD=&PERIOD \
),
$
 

Then in the fex where you pass these parameters, do a WHERE ACCOUNT <= &MAX_ACCOUNT AND ACCOUNT >= &MIN_ACCOUNT