In FML report, on a particular column, I need to perform drilldown on all the cells of that column, except on one cell. How can I achieve it?
Is their a way I can say that perform drilldown on all the values of a column except on a particular label

In the below line, it performs drilldown on a particular cell
TYPE=DATA, COLUMN=ABC, LABEL=ST, JAVASCRIPT=drillDown(......), WHEN ABC GT 0, $

Similarly, is their a way to say LABEL not equal to ST, such that the drilldown is not performed on that label, and is performed on all the remaining cells...

Thanks

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

In general, when conditional styling (which includes drill-down reports) is based on composite criteria, it is usually advisable to create an additional field (NOPRINT) to evaluate for the conditions you need and use that one field to base your conditional styling on. Although I use this technique particularly on matrix reports, I don't see why it could not be used with FML.

TABLE FILE blah
SUM blah
    ...
COMPUTE DRILL_IND/A1 = IF (ABC GT 0) AND (LABEL NE 'ST') THEN 'Y' ELSE 'N'; NOPRINT
    ...
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=ABC, JAVASCRIPT=drillDown(......), WHEN=DRILL_IND EQ 'Y', $
...
END

In the DRILL_IND field definition, I presume you would replace LABEL by a reference to your FOR field.

Thanks for the quick response. I tried it before itself... but the keyword 'NE' for the label is not recognised

COMPUTE DRILL_IND/A1 = IF (ABC GT 0) AND (LABEL NE 'ST') THEN 'Y' ELSE 'N'; NOPRINT

I don't think you can specify a LABEL in the WHEN clause.

This may help - you must mention the FOR column in a NOPRINT for the WHEN in the stylesheet line to work:

-* File fml01.fex

SET NODATA=0

TABLE FILE CAR
SUM
RETAIL_COST AS 'Retail'
DEALER_COST AS 'Dealer'
BODYTYPE NOPRINT

BY COUNTRY PAGE-BREAK

FOR BODYTYPE
'CONVERTIBLE'       AS 'Convertible'  LABEL R1 OVER
'COUPE'             AS 'Coupe'        LABEL R2 OVER
'HARDTOP'           AS 'Hardtop'      LABEL R3 OVER
'ROADSTER'          AS 'Roadster'     LABEL R4 OVER
'SEDAN'             AS 'Sedan'        LABEL R5 OVER
'SUV'               AS 'Suv'          LABEL R6 OVER
'VAN'               AS 'Van'          LABEL R7 OVER

RECAP R_TOTAL = R1 + R2 + R3 + R4+ R5 + R6 + R7; AS 'Total'

ON TABLE SET STYLE *
TYPE=DATA, COLUMN=DEALER_COST, FOCEXEC=DUMMY, WHEN=BODYTYPE NE 'HARDTOP', $
END

I think this is the only way to use labels and control where a drill-down occurs:

-* File fml01.fex

SET NODATA=0

TABLE FILE CAR
SUM
RETAIL_COST AS 'Retail'
DEALER_COST AS 'Dealer'
BODYTYPE NOPRINT

BY COUNTRY PAGE-BREAK

FOR BODYTYPE
'CONVERTIBLE'       AS 'Convertible'  LABEL R1 OVER
'COUPE'             AS 'Coupe'        LABEL R2 OVER
'HARDTOP'           AS 'Hardtop'      LABEL R3 OVER
'ROADSTER'          AS 'Roadster'     LABEL R4 OVER
'SEDAN'             AS 'Sedan'        LABEL R5 OVER
'SUV'               AS 'Suv'          LABEL R6 OVER
'VAN'               AS 'Van'          LABEL R7 OVER

RECAP R_TOTAL = R1 + R2 + R3 + R4+ R5 + R6 + R7; AS 'Total'

ON TABLE SET STYLE *
TYPE=DATA, COLUMN=DEALER_COST, LABEL=R1, FOCEXEC=DUMMY, $
TYPE=DATA, COLUMN=DEALER_COST, LABEL=R2, FOCEXEC=DUMMY, $
TYPE=DATA, COLUMN=DEALER_COST, LABEL=R3, FOCEXEC=DUMMY, $
TYPE=DATA, COLUMN=DEALER_COST, LABEL=R4, FOCEXEC=DUMMY, $
TYPE=DATA, COLUMN=DEALER_COST, LABEL=R6, FOCEXEC=DUMMY, $
TYPE=DATA, COLUMN=DEALER_COST, LABEL=R7, FOCEXEC=DUMMY, $
END

quote:

In the DRILL_IND field definition, I presume you would replace LABEL by a reference to your FOR field.

msam, it didn't seem like you took this statement into consideration. In the COMPUTE line, you must use the name of your FOR field (in addition to ABC) in order to determine whether or not a drill-down link is to be produced.

Mixing Francis' example with the technique I described to implement composite WHEN criteria, this is what you could probably do in order to have a drill-down on *any* vehicle whose dealer cost is greater than 0, except for sedans which should never have a drill-down regardless of cost.

SET NODATA=0

TABLE FILE CAR
SUM
RETAIL_COST AS 'Retail'
DEALER_COST AS 'Dealer'
BODYTYPE NOPRINT
COMPUTE DRILL_IND/A1 = IF DEALER_COST GT 0 AND BODYTYPE NE 'SEDAN' THEN 'Y' ELSE 'N'; NOPRINT

BY COUNTRY PAGE-BREAK

FOR BODYTYPE
'CONVERTIBLE'       AS 'Convertible'  LABEL R1 OVER
'COUPE'             AS 'Coupe'        LABEL R2 OVER
'HARDTOP'           AS 'Hardtop'      LABEL R3 OVER
'ROADSTER'          AS 'Roadster'     LABEL R4 OVER
'SEDAN'             AS 'Sedan'        LABEL R5 OVER
'SUV'               AS 'Suv'          LABEL R6 OVER
'VAN'               AS 'Van'          LABEL R7 OVER

RECAP R_TOTAL = R1 + R2 + R3 + R4+ R5 + R6 + R7; AS 'Total'

ON TABLE SET STYLE *
TYPE=DATA, COLUMN=DEALER_COST, FOCEXEC=DUMMY, WHEN=DRILL_IND EQ 'Y', $
END

Is that similar to what you needed?

Thanks... this is what I needed.