To All,

I have researched the archives and found much interesting information on conditional drill-downs. I could not find answers to some questions about drilling on GRANDTOTAL values.

Example from Car:

TABLE FILE CAR
SUM CNT.STANDARD
ACROSS CAR
BY COUNTRY
WHERE CAR EQ 'AUDI' OR 'DATSUN' OR 'JAGUAR' OR 'JENSEN'
ON TABLE SUMMARIZE
ON TABLE SET STYLE *
TYPE=GRANDTOTAL, COLUMN=N2, WHEN=N2 GT 2,FOCEXEC=dd(carname=CAR),$
TYPE=GRANDTOTAL, COLUMN=N3, WHEN=N3 GT 2,FOCEXEC=dd(carname=CAR),$
TYPE=GRANDTOTAL, COLUMN=N4, WHEN=N4 GT 2,FOCEXEC=dd(carname=CAR),$
TYPE=GRANDTOTAL, COLUMN=N5, WHEN=N5 GT 2,FOCEXEC=dd(carname=CAR),$
END

This creates the following output:
___________________________________________
CAR
AUDI DATSUN JAGUAR JENSEN
COUNTRY
ENGLAND 0 0 5 4
JAPAN 0 2 0 0
W GERMANY 3 0 0 0
TOTAL 3 2 5 4
___________________________________________

(The formatting controls do not show up, but only the number 3 in the TOTAL row is underlined.) Notice that only the Audi column shows an active drill-down. (I suspect the value being used in the WHEN expression is taken from the W GERMANY row and not the TOTAL row.)

Several questions.

1. How can I make the drill down conditional on the value in the TOTAL row?
2. I want to make the drill down active when the TOTAL value is GT 2 and LT 5. How can this be done given that the WHEN statement will not accept AND/OR?
3. Is there a way to do the conditional drill-down in #2 without reference to each column? (In other words, how can I handle the situation in which it is not known how many columns will be returned from the ACROSS action?)

Thanks in advance for the help.

This message has been edited. Last edited by: Dennis Smid,

This might work for your first and third points:

TABLE FILE CAR
SUM CNT.STANDARD
ACROSS CAR
BY COUNTRY
WHERE CAR EQ 'AUDI' OR 'DATSUN' OR 'JAGUAR' OR 'JENSEN'
ON TABLE SUMMARIZE
ON TABLE SET STYLE *
TYPE=GRANDTOTAL, ACROSSCOLUMN=CNT.STANDARD, WHEN=N2 GT 2,FOCEXEC=DD(CARNAME=CAR),$
END

Dennis,

Your program has only TWO fields: COUNTRY (N1) and CNT.STANDARD (N2). What happens is that the styling for fields N3, N4 and N5 are ignored.

However, instead of using the N notation, you can use the C notation which identifies the COLUMNS in the output. As follows:

  
TABLE FILE CAR
SUM CNT.STANDARD
ACROSS CAR
BY COUNTRY
WHERE CAR EQ 'AUDI' OR 'DATSUN' OR 'JAGUAR' OR 'JENSEN'
ON TABLE SUMMARIZE
ON TABLE SET STYLE *
TYPE=GRANDTOTAL, COLUMN=C1, WHEN=C1 GT 2,FOCEXEC=dd(carname=CAR),$
TYPE=GRANDTOTAL, COLUMN=C2, WHEN=C2 GT 2,FOCEXEC=dd(carname=CAR),$
TYPE=GRANDTOTAL, COLUMN=C3, WHEN=C3 GT 2,FOCEXEC=dd(carname=CAR),$
TYPE=GRANDTOTAL, COLUMN=C4, WHEN=C4 GT 2,FOCEXEC=dd(carname=CAR),$
END

This should do the trick.

Danny/Francis,

Both suggestions were helpful. Do either of you have an idea on my second question?

Dennis

To do a compound condition, you have to set a flag in a DEFINE or COMPUTE. In your case, you would have to use a COMPUTE at at grand total level so that you can test the value and set the flag. Then you can test the flag as a condition in your stylesheet.

Dennis,

This should do the trick:

  
DEFINE FILE CAR
DUM/A1=' ';
END

TABLE FILE CAR
SUM CNT.STANDARD
COMPUTE D/I5=IF CNT.STANDARD GT 2 AND CNT.STANDARD LT 5 THEN 1 ELSE 0; NOPRINT
ACROSS CAR
BY DUM NOPRINT
BY COUNTRY
WHERE CAR EQ 'AUDI' OR 'DATSUN' OR 'JAGUAR' OR 'JENSEN'

ON DUM SUMMARIZE
ON TABLE NOTOTAL
ON TABLE SET STYLE *
TYPE=SUBTOTAL, COLUMN=C1, WHEN=C2 EQ 1,FOCEXEC=dd(carname=CAR),$
TYPE=SUBTOTAL, COLUMN=C3, WHEN=C4 EQ 1,FOCEXEC=dd(carname=CAR),$
TYPE=SUBTOTAL, COLUMN=C5, WHEN=C6 EQ 1,FOCEXEC=dd(carname=CAR),$
TYPE=SUBTOTAL, COLUMN=C7, WHEN=C8 EQ 1,FOCEXEC=dd(carname=CAR),$
END

Or, using the Francis method:

  
TABLE FILE CAR
SUM CNT.STANDARD
COMPUTE D/I5=IF CNT.STANDARD GT 2 AND CNT.STANDARD LT 5 THEN 1 ELSE 0; NOPRINT
ACROSS CAR
BY COUNTRY
WHERE CAR EQ 'AUDI' OR 'DATSUN' OR 'JAGUAR' OR 'JENSEN'
ON TABLE SUMMARIZE
ON TABLE SET STYLE *
TYPE=GRANDTOTAL, ACROSSCOLUMN=CNT.STANDARD, WHEN=N3 EQ 1,FOCEXEC=DD(CARNAME=CAR),$
END

Thanks again.

I see how this works for an across report. However, I have not been able to figure out an equivalent approach for a straight table request using a print verb. (I have tried all manners of computes in a multiverb approach, but to no avail.) There must be another trick. See the following example which yields the same result as the matrix given earlier. How can I set the drill-down links in the GRANDTOTAL row to be active for total values greater than 2 and less then 5?

Dennis

TABLE FILE CAR
SUM CNT.STANDARD
ACROSS CAR
BY COUNTRY
WHERE CAR EQ 'AUDI' OR 'DATSUN' OR 'JAGUAR' OR 'JENSEN'
ON TABLE HOLD
END
-*
TABLE FILE HOLD
PRINT E02 AS 'AUDI'
E03 AS 'DATSUN'
E04 AS 'JAGUAR'
E05 AS 'JENSEN'
BY COUNTRY
ON TABLE SUMMARIZE
ON TABLE SET STYLE *
TYPE=GRANDTOTAL, ???????????, FOCEXEC=DD(CARNAME=CAR),$
END

Dennis,
Just a slight variation on a previous solution:

  
TABLE FILE HOLD
PRINT 
E02 AS 'AUDI'
COMPUTE D2/I5=IF E02 GT 2 AND E02 LT 5 THEN 1 ELSE 0; NOPRINT
E03 AS 'DATSUN'
COMPUTE D3/I5=IF E03 GT 2 AND E03 LT 5 THEN 1 ELSE 0; NOPRINT 
E04 AS 'JAGUAR'
COMPUTE D4/I5=IF E04 GT 2 AND E04 LT 5 THEN 1 ELSE 0;  NOPRINT
E05 AS 'JENSEN'
COMPUTE D5/I5=IF E05 GT 2 AND E05 LT 5 THEN 1 ELSE 0;  NOPRINT

BY COUNTRY
ON TABLE SUMMARIZE
ON TABLE SET STYLE *
TYPE=GRANDTOTAL, COLUMN=E02, WHEN=D2 EQ 1,FOCEXEC=dd(carname=CAR),$
TYPE=GRANDTOTAL, COLUMN=E03, WHEN=D3 EQ 1,FOCEXEC=dd(carname=CAR),$
TYPE=GRANDTOTAL, COLUMN=E04, WHEN=D4 EQ 1,FOCEXEC=dd(carname=CAR),$
TYPE=GRANDTOTAL, COLUMN=E05, WHEN=D5 EQ 1,FOCEXEC=dd(carname=CAR),$
END

Thanks Daniel. That did it. (I forgot that 'summarize' will recalculate the value of a computed variable ... that was the 'trick' I needed.)