hi,

i have a subtotal on a report, and i use drill a down menu on it. However, it highlights everything, even the "0" values and the word "TOTAL", how do i specify, to only drill down on values that are GT 0?

Here's an example:

TABLE FILE CAR
SUM
SALES
BY COUNTRY
BY CAR
ON COUNTRY SUBTOTAL
ACROSS SEATS
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLESHEET *
TYPE=REPORT, GRID=OFF,
FONT='ARIAL', SIZE=8, $
TYPE=SUBTOTAL, ACROSSCOLUMN=SALES, FOCEXEC=SS, WHEN=SALES GT 0, $
ENDSTYLE
END

I use an ACROSS to be able to have some zeros in the subtotals.

The key is to specify the column name in the drilldown stylesheet entry (in my case, ACROSSCOLUMN=SALES) and to add the WHEN (WHEN=SALES GT 0) clause.

PS. I like your name, MadamZuZu.

HI
Thanks

it almost worked

however, i use ON TABLE SUBTOTAL, and in the stylesheet i'm using "GRANDTOTAL" instead of "SUBTOTAL", it seems to be working with SUBTOTAL, but not with GRANDTOTAL.

why is that?

Seems to be working for me:

TABLE FILE CAR
SUM
SALES
BY COUNTRY
BY CAR
ACROSS SEATS COLUMNS 2 AND 3 AND 4 AND 5
ON COUNTRY SUBTOTAL AS 'Sub Total'
ON TABLE SUBTOTAL AS 'Total'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLESHEET *
TYPE=REPORT, GRID=OFF,
FONT='ARIAL', SIZE=8, $
TYPE=GRANDTOTAL, ACROSSCOLUMN=SALES, FOCEXEC=SS, WHEN=SALES GT 0, $
ENDSTYLE
END

gosh, sorry to waste yoru time.

i'm also using SUM CNT.MYFIELD...
so in the sylesheet i used CNT.MYFIELD as well, is that correct? could that be the problem?

i played with the car table file for a little bit, and


this works:

TABLE FILE CAR
SUM CNT.SALES
BY COUNTRY
BY CAR
ON COUNTRY SUBTOTAL
ACROSS SEATS
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLESHEET *
TYPE=REPORT, GRID=OFF,
FONT='ARIAL', SIZE=8, $
TYPE=SUBTOTAL, ACROSSCOLUMN=CNT.SALES, FOCEXEC=SS, WHEN=CNT.SALES GT 0, $
ENDSTYLE
END

this doesn't

TABLE FILE CAR
SUM CNT.SALES
BY COUNTRY
BY CAR
ON COUNTRY SUBTOTAL
ACROSS SEATS
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLESHEET *
TYPE=REPORT, GRID=OFF,
FONT='ARIAL', SIZE=8, $
TYPE=GRANDTOTAL, ACROSSCOLUMN=CNT.SALES, FOCEXEC=SS, WHEN=CNT.SALES GT 0, $
ENDSTYLE
END

This works just fine. The drilldown is on the non-zero columns of the grand total line.

TABLE FILE CAR
SUM CNT.SALES
BY COUNTRY
BY CAR
ON COUNTRY SUBTOTAL
ACROSS SEATS COLUMNS 2 AND 3 AND 4 AND 5
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLESHEET *
TYPE=REPORT, GRID=OFF,
FONT='ARIAL', SIZE=8, $
TYPE=GRANDTOTAL, ACROSSCOLUMN=CNT.SALES, FOCEXEC=SS, WHEN=CNT.SALES GT 0, $
ENDSTYLE
END

When you say "this doesn't (work)", what do you mean?

"this doesn't work" - nothing is highlighted.

but i just got it to work (with Mickey's help)

had to make a dummy counter field to reference, instead of the actualy "sales" field itself.

Thanks

Must have something to do with the version, we're at 5.3.3 - what is yours?

7.1.3

here is what we did:

TABLE FILE CAR
SUM CNT.SALES NOPRINT
COMPUTE DUMMY/I5=CNT.SALES;

BY COUNTRY
BY CAR
ON COUNTRY SUBTOTAL
ACROSS SEATS
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLESHEET *
TYPE=REPORT, GRID=OFF,
FONT='ARIAL', SIZE=8, $
TYPE=GRANDTOTAL, ACROSSCOLUMN=DUMMY, FOCEXEC=SS, WHEN=DUMMY GT 5, $
ENDSTYLE
END