I have a report like this --

TABLE FILE SUMMARIES
SUM MIN.TOTAL_TESTS AS 'Total Tests'
    MIN.FAILS AS 'Fails'
    COMPUTE PASS_PCT/D8.1% = (1 - MIN.FAILS/MIN.TOTAL_TESTS) * 100; AS 'Pass %'
BY CREATEDBY
SUM CNT.PASS_FAIL/I9CS AS ''
BY CREATEDBY AS 'Tester'
ACROSS NAME AS ''
ACROSS ID1 AS ''
. . . 

I want to put a vertical line down the length of the report between each of the top-level across entries, i.e., between each NAME entry in the report. I've managed to put a vertical line between the actual across values in the header in two or three different ways, but I can't get that vertical line to apply to the data fields between each first-level across value.

Can someone help me out? I don't see this specific example in the help files, and haven't found one that works in the forums.

J.

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

Is it something like that you're looking for ?

TABLE FILE CAR
SUM  CAR.BODY.SEATS
BY  CAR.COMP.CAR
SUM  CAR.BODY.DEALER_COST NOPRINT
     CAR.BODY.DEALER_COST
BY  CAR.COMP.CAR
ACROSS LOWEST CAR.ORIGIN.COUNTRY
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     PAGESIZE='Letter',
     LEFTMARGIN=0.250000,
     RIGHTMARGIN=0.250000,
     TOPMARGIN=0.250000,
     BOTTOMMARGIN=0.250000,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
     PAGECOLOR='WHITE',
$
TYPE=REPORT,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=DATA,
     BORDER-TOP-COLOR='LIME',
     BORDER-BOTTOM-COLOR='LIME',
     BORDER-LEFT-COLOR='LIME',
     BORDER-RIGHT-COLOR='LIME',
$
TYPE=TITLE,
     BORDER-TOP-COLOR='GRAY',
     BORDER-BOTTOM-COLOR='GRAY',
     BORDER-LEFT-COLOR='GRAY',
     BORDER-RIGHT-COLOR='GRAY',
$
TYPE=ACROSSVALUE,
     BORDER-TOP-COLOR='RED',
     BORDER-BOTTOM-COLOR='RED',
     BORDER-LEFT-COLOR='RED',
     BORDER-RIGHT-COLOR='RED',
$
TYPE=ACROSSTITLE,
     BORDER-TOP-COLOR='BLUE',
     BORDER-BOTTOM-COLOR='BLUE',
     BORDER-LEFT-COLOR='BLUE',
     BORDER-RIGHT-COLOR='BLUE',
$
ENDSTYLE
END
-RUN

No, that's a single Across. Imagine if there was a higher-level Across named "Continent" on the report. Your report would show two continents, Asia and Europe.

I'd like a vertical line to appear between the last column in Asia (Japan) and the first column in Europe (England) for the length of the report. I don't want separators between data values in countries that are on the same Continent.

TABLE FILE CAR
  SUM 
    DEALER_COST
    RETAIL_COST
  ACROSS SEATS AS ''
  ACROSS COUNTRY AS ''
  BY CAR
  WHERE COUNTRY NE 'ENGLAND';
  ON TABLE SET LINES 99999
  ON TABLE SET PAGE NOPAGE
  ON TABLE SET HTMLCSS ON

  ON TABLE SET STYLE *
    TYPE=REPORT     ,WRAP=OFF    ,COLOR=RGB(100 100 100)        ,$
    TYPE=REPORT     ,UNITS=PTS   ,FONT='VERDANA', SIZE=8        ,$
    TYPE=REPORT     ,BORDER=1    ,BORDER-COLOR=RGB(210 210 210) ,$
    TYPE=HEADING    ,SIZE=10                                    ,$
    TYPE=TITLE      ,STYLE=BOLD  ,BACKCOLOR=RGB(230 230 230)    ,$
    TYPE=ACROSSTITLE,STYLE=BOLD  ,BACKCOLOR=RGB(230 230 230)    ,$
    TYPE=ACROSSTITLE,JUSTIFY=CENTER                             ,$
    TYPE=ACROSS     ,JUSTIFY=CENTER                             ,$
    TYPE=SUBTOTAL   ,STYLE=BOLD  ,BACKCOLOR=RGB(210 210 210)    ,$
    TYPE=SUBTOTAL   ,TOPGAP=3    ,BOTTOMGAP=3                   ,$
    TYPE=DATA       ,TOPGAP=3    ,BOTTOMGAP=3                   ,$
    TYPE=DATA       ,BACKCOLOR=(WHITE RGB(245 245 245))         ,$

    TYPE=ACROSSVALUE,COLUMN=N4,BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, $
    TYPE=TITLE,      COLUMN=N4,BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, $
    TYPE=DATA,       COLUMN=N4,BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, $
    
    TYPE=ACROSSVALUE,COLUMN=N10,BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, $
    TYPE=TITLE,      COLUMN=N10,BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, $
    TYPE=DATA,       COLUMN=N10,BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, $

  ENDSTYLE
END

The column-notation is now hard-coded, like N4 and N10. If you remove the 'WHERE COUNTRY ...' line
these N-notations are not in sync anymore with the desired output.
I haven't found out yet how to address that.
Anyone ??

Try this:

  
-* File acrossvline.fex
-SET &COUNTRY_LIST='ENGLAND'',''W GERMANY'',''ITALY';
TABLE FILE CAR
SUM MAX.SEATS
COMPUTE LL/I4=IF MAX.SEATS EQ LAST MAX.SEATS THEN LL+1 ELSE 1;
BY SEATS
BY COUNTRY
  WHERE NOT COUNTRY IN ('&COUNTRY_LIST'); 
ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
SUM MAX.LL
BY SEATS
ON TABLE HOLD AS NN
END
-RUN
-SET &NN=&LINES - 1;
-*
TABLE FILE CAR
  SUM 
    DEALER_COST
    RETAIL_COST
  ACROSS SEATS AS ''
  ACROSS COUNTRY AS ''
  BY CAR
  WHERE NOT COUNTRY IN ('&COUNTRY_LIST'); 
  ON TABLE SET LINES 99999
  ON TABLE SET PAGE NOPAGE
  ON TABLE SET HTMLCSS ON
-*
  ON TABLE SET STYLE *
    TYPE=REPORT     ,WRAP=OFF    ,COLOR=RGB(100 100 100)        ,$
    TYPE=REPORT     ,UNITS=PTS   ,FONT='VERDANA', SIZE=8        ,$
    TYPE=REPORT     ,BORDER=1    ,BORDER-COLOR=RGB(210 210 210) ,$
    TYPE=HEADING    ,SIZE=10                                    ,$
    TYPE=TITLE      ,STYLE=BOLD  ,BACKCOLOR=RGB(230 230 230)    ,$
    TYPE=ACROSSTITLE,STYLE=BOLD  ,BACKCOLOR=RGB(230 230 230)    ,$
    TYPE=ACROSSTITLE,JUSTIFY=CENTER                             ,$
    TYPE=ACROSS     ,JUSTIFY=CENTER                             ,$
    TYPE=SUBTOTAL   ,STYLE=BOLD  ,BACKCOLOR=RGB(210 210 210)    ,$
    TYPE=SUBTOTAL   ,TOPGAP=3    ,BOTTOMGAP=3                   ,$
    TYPE=DATA       ,TOPGAP=3    ,BOTTOMGAP=3                   ,$
    TYPE=DATA       ,BACKCOLOR=(WHITE RGB(245 245 245))         ,$
-SET &COL=2;
-REPEAT #NN FOR &I FROM 1 TO &NN;
-READFILE NN
-SET &COL=&COL+&LL*2;
-TYPE &COL
    TYPE=ACROSSVALUE,COLUMN=N&COL,BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, $
    TYPE=TITLE,      COLUMN=N&COL,BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, $
    TYPE=DATA,       COLUMN=N&COL,BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, $
-#NN
-CLOSE NN
-*
  ENDSTYLE
END

Change the contents of the variable &COUNTRY_LIST to verify for different values of COUNTRY.

This message has been edited. Last edited by: Danny-SRL,

A bit of generalization:

  
-* File acrossvline.fex
-SET &COUNTRY_LIST='FRANCE'',''JAPAN';
-SET &FIELDS_LIST='SALES DEALER_COST RETAIL_COST';
-SET &FIELDS0=3;
-SET &ACROSS1='SEATS';
-SET &ACROSS2='COUNTRY';
-SET &BY_LIST='CAR BY BODYTYPE';
-SET &BY0=2;
TABLE FILE CAR
SUM MAX.&ACROSS1
COMPUTE LL/I4=IF MAX.&ACROSS1 EQ LAST MAX.&ACROSS1 THEN LL+1 ELSE 1;
BY &ACROSS1
BY &ACROSS2
  WHERE NOT COUNTRY IN ('&COUNTRY_LIST');
ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
SUM MAX.LL
BY &ACROSS1
ON TABLE HOLD AS NN
END
-RUN
-SET &NN=&LINES - 1;
-*
TABLE FILE CAR
  SUM
    &FIELDS_LIST
  ACROSS &ACROSS1 AS ''
  ACROSS &ACROSS2 AS ''
  BY &BY_LIST
  WHERE NOT COUNTRY IN ('&COUNTRY_LIST');
  ON TABLE SET LINES 99999
  ON TABLE SET PAGE NOPAGE
  ON TABLE SET HTMLCSS ON
-*
  ON TABLE SET STYLE *
    TYPE=REPORT     ,WRAP=OFF    ,COLOR=RGB(100 100 100)        ,$
    TYPE=REPORT     ,UNITS=PTS   ,FONT='VERDANA', SIZE=8        ,$
    TYPE=REPORT     ,BORDER=1    ,BORDER-COLOR=RGB(210 210 210) ,$
    TYPE=HEADING    ,SIZE=10                                    ,$
    TYPE=TITLE      ,STYLE=BOLD  ,BACKCOLOR=RGB(230 230 230)    ,$
    TYPE=ACROSSTITLE,STYLE=BOLD  ,BACKCOLOR=RGB(230 230 230)    ,$
    TYPE=ACROSSTITLE,JUSTIFY=CENTER                             ,$
    TYPE=ACROSS     ,JUSTIFY=CENTER                             ,$
    TYPE=SUBTOTAL   ,STYLE=BOLD  ,BACKCOLOR=RGB(210 210 210)    ,$
    TYPE=SUBTOTAL   ,TOPGAP=3    ,BOTTOMGAP=3                   ,$
    TYPE=DATA       ,TOPGAP=3    ,BOTTOMGAP=3                   ,$
    TYPE=DATA       ,BACKCOLOR=(WHITE RGB(245 245 245))         ,$
-SET &COL=&BY0+1;
-REPEAT #NN FOR &I FROM 1 TO &NN;
-READFILE NN
-SET &COL=&COL+&LL*&FIELDS0;
-TYPE &COL
    TYPE=ACROSSVALUE,COLUMN=N&COL,BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, $
    TYPE=TITLE,      COLUMN=N&COL,BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, $
    TYPE=DATA,       COLUMN=N&COL,BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, $
-#NN
-CLOSE NN
-*
  ENDSTYLE
END

Yeah, this doesn't look like it's going to be pretty. The columns included change from run to run, and I have to leave the software behind for others to maintain when I'm finished. It looks like FOCUS just isn't designed to do what I'm requesting. It appears I'll need another way to separate my categories from each other.

Thanks for the help guys, I was hoping someone knew something deep down in stylesheet lore that I didn't. It appears that's not the case.

J.

John --

Before you surrender, try this:

TABLE FILE SUMMARIES

SUM MIN.TOTAL_TESTS AS 'Total Tests'
    MIN.FAILS AS 'Fails'
    COMPUTE PASS_PCT/D8.1% = (1 - MIN.FAILS/MIN.TOTAL_TESTS) * 100; AS 'Pass %'
BY CREATEDBY

COUNT PASS_FAIL AS ''
        NAME NOPRINT
        COMPUTE SEQ/I2 = IF (NAME EQ LAST NAME) THEN (LAST SEQ) + 1 ELSE 1;  NOPRINT
BY CREATEDBY AS 'Tester'
ACROSS NAME AS ''
ACROSS ID1 AS ''
. . .
        TYPE=DATA, COLUMN=PASS_FILE(*),
        BORDER-LEFT=1, BORDER-LEFT-COLOR=NAVY,
          WHEN= SEQ EQ 1,
        $

That should draw a left border on the left-most PASS_FAIL-count cell within each NAME.

Note that ACROSS is treated like BY in creating the internal matrix. Here, BY CREATEDBY is the primary sort, since it governs the first verb, while NAME and ID1 are secondary. The first "row" of the internal matrix's second-level "segment" within each CREATEDBY and NAME value pair gets Mark=1, all the others >1.

Admittedly, it only covers the Data cells, not their titles, but it's a start.

That will be next. I have done something similar with the WHEN where I'm calling out each first-level ACROSS value by name and changing the shading of the data background. It's striped both horizontally and vertically, with two complementary colors of brown crossed with white.

Yes, I know, this is fraught with aesthetic peril.

But the WHEN clause in the style sheet would not work with LAST and I could not get a blank field to come in between each first-level across. I considered a define like you show here but I hadn't twigged on how to make it work with a COUNT instead of a SUM.

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

John,
I admit I am a bit elliptic when I propose a solution...
In my "general" solution look at the &variables at the beginning:

quote:

-SET &COUNTRY_LIST='FRANCE'',''JAPAN';
-SET &FIELDS_LIST='SALES DEALER_COST RETAIL_COST';
-SET &FIELDS0=3;
-SET &ACROSS1='SEATS';
-SET &ACROSS2='COUNTRY';
-SET &BY_LIST='CAR BY BODYTYPE';
-SET &BY0=2;

&COUNTRY_LIST contains the values of COUNTRY which you want to omit
&FIELDS_LIST contains the fields that are summed in the final TABLE command
&FIELDS0 is the number of the fields
&BY_LIST contains your sort fields
&BY0 is the number of sort fields
&ACROSS1 and &ACROSS2 are the 2 horizintal sort fields

If you change the values of the &variables consistently (e.g. if you have 2 fields to sum don't forget to enter 2 in &FIELDS0) then the rest flows evenly.

If you need a bit more comments, I'll gladly provide.

See:
Vline

Performing COMPUTEs on the ACROSS values in the final report request are problematic because the BY sort is performed before the ACROSS sorts. I had to run a preliminary request to a HOLD file in order to sort the data correctly for the COMPUTE needed to identify values for red border color. After that step everything seemed to fall into place until I added the WHEN parameter to the ACROSSVALUE clause for SEATS in the StyleSheet. Adding a WHEN parameter to an ACROSSVALUE seems to kill all of the styling for the affected values in the output (a bug I suspect). Maybe this is fixed in 8.0?

SET HOLDLIST = PRINTONLY
SET ASNAMES  = ON
-*
TABLE FILE CAR
 SUM COMPUTE RED_LINE/A1 = IF (COUNTRY   NE LAST COUNTRY)   THEN 'Y' ELSE
                           IF (MIN.SEATS EQ LAST MIN.SEATS) THEN (LAST RED_LINE) ELSE 'N';
 BY COUNTRY
 BY SEATS
 SUM
  SALES
  DEALER_COST
  RETAIL_COST
 BY COUNTRY
 BY SEATS
 BY CAR
 ON TABLE HOLD AS CARHOLD
END
-*
TABLE FILE CARHOLD
 SUM
  SALES
  DEALER_COST
  RETAIL_COST
 BY CAR
 ACROSS COUNTRY   AS ''
 ACROSS SEATS     AS ''
 ACROSS RED_LINE  NOPRINT
 ON TABLE SET HTMLCSS ON
 ON TABLE SET PAGE NOPAGE
 ON TABLE SET ACROSSLINE OFF
 ON TABLE SET STYLE *
-INCLUDE BASEAPP/ENDEFLT.STY
  TYPE=REPORT     , WRAP=OFF, $
  TYPE=ACROSSVALUE, ACROSS=COUNTRY , BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, $
  TYPE=ACROSSVALUE, ACROSS=SEATS   , BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, WHEN=RED_LINE EQ 'Y', $
  TYPE=TITLE      , COLUMN=SALES(*), BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, WHEN=RED_LINE EQ 'Y', $
  TYPE=DATA       , COLUMN=SALES(*), BORDER-LEFT=3,BORDER-LEFT-COLOR=RED, WHEN=RED_LINE EQ 'Y', $
 ENDSTYLE
END

John,
Don't give up. Look at the power of Dialog manager:

The first TABLE request numbers the occurences of the &ACROSS2 field per &ACROSS1 field.
The second TABLE request finds the MAX of such occurences.
The third TABLE request outputs the report. The output of the second TABLE is used to style the vertical bar of the appropriate column.