To all,

Here is another question about drill-downs on the GRANDTOTAL row. I am trying to pass the cell value of the total row to the drill-down fex. See following, which seems like the logical approach:

TABLE FILE CAR
SUM CNT.CAR
BY MODEL
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE SUMMARIZE
ON TABLE SET STYLE *
TYPE=GRANDTOTAL, COLUMN=N2,FOCEXEC=dd(cellvalue=N2),$
END

However, the value that gets passed to the dd fex is taken from the last row of the matrix instead of total row. How do I pass the cell value from the total row?

Thanks again in advance.

Dennis

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

Dennis,

If I need to use TOTAL lines, I create the ROW for that line, and, stay away from trying to using SUB-TOTAL, SUBTOTAL, GRANDTOTAL, etc. when I have to drill on those values. I realize this is just CAR, and, your data is probably much more complex, but, if you understand this concept, it "may" be easier for you in the long run.

Here's an example using your example. Granted, it IS more coding, and, a different philosophy, but...

  
SET ASNAMES=ON
TABLE FILE CAR
SUM 
    CNT.CAR AS 'XCNT'
  BY MODEL
WHERE COUNTRY EQ 'ENGLAND';
  ON TABLE HOLD AS H1_DET
END
-RUN
DEFINE FILE H1_DET
  XMODEL/A24 = 'Total';
END
TABLE FILE H1_DET
SUM
    XCNT
  BY XMODEL AS 'MODEL'
  ON TABLE HOLD AS H1_SUM
END
-RUN
TABLE FILE H1_DET
PRINT
  XCNT 
 BY MODEL
    ON TABLE HOLD AS H1_ALL
MORE
FILE H1_SUM
END
-RUN
DEFINE FILE H1_ALL
  XSORT/I2 = DECODE MODEL('Total' 99 ELSE 1);
END
TABLE FILE H1_ALL
PRINT
    XCNT
      COMPUTE DRILL_TOT/A1 = IF MODEL EQ 'Total' THEN 'Y' ELSE 'N'; NOPRINT
  BY XSORT NOPRINT
  BY MODEL
-*ON TABLE SUMMARIZE
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=N3,FOCEXEC=dd(cellvalue=N3), WHEN=DRILL_TOT EQ 'Y',$
END
-EXIT

HTH

Tom

This message has been edited. Last edited by: Tom Flynn,

Tom,
You can use the McGuyver technique to produce your H1_ALL file with just one pass on the data.

Danny,

He has 12 posts, baby-steps...

Tom,

That's why I addressed it to you...

To Danny/Tom,

I understand Tom's suggestion, and I had contemplated doing something similar. However, this seems to be a workaround to a bug: after all why should a GRANDTOTAL data type reference a cell from the body of the report? Does IBI concur this is a bug, or is there another solution?

Dennis

Dennis,

Here's Noreen's excellent article on Macguyver:

The MacGuyver Technique

These are the two processes I use most often; depends on the audience..

Good Luck!

Tom

To all,

I raised a ticket with IBI. Apparently there was an earlier ticket raised about a year ago on exactly the same topic - 40242529. IBI's resolution was a multi-verb approach. As I see it, this is another workaround to add to the list. I still think there is a bug.

Dennis