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...
ThanksThis message has been edited. Last edited by: Kerry,
July 26, 2010, 05:14 PM
njsden
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
July 27, 2010, 09:34 AM
Francis Mariani
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
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
July 27, 2010, 09:45 AM
Francis Mariani
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
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
July 27, 2010, 09:47 AM
njsden
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