Is there a way to exclude a column from displaying when using ACROSS for only one of the across categories.

Specifically, in the following example, can the RETAIL_COST column be excluded for France only, but included for the other countries:

 
TABLE FILE CAR
SUM
SALES
RETAIL_COST
BY SEATS
ACROSS COUNTRY AS ''
WHERE COUNTRY IN ('ENGLAND', 'FRANCE', 'JAPAN')

ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON

ON TABLE SET NODATA 0

ON TABLE SET STYLESHEET *
TYPE=REPORT, BORDER=1, BORDER-COLOR=GREY, FONT='ARIAL', SIZE=10, $
ENDSTYLE
END 

Thanks,
Sean

I think it can be done, if you do not mind having a more or less empty column.

like

DEFINE FILE CAR
RETCOST/D12.2S=IF COUNTRY EQ 'FRANCE' THEN 0 ELSE RETAIL_COST;
END

Now you get only zero for France, and the zero is not displayed.

Frank,

Thanks for the response, however I do not want the column to appear at all.

I'm hoping there is some way to conditionally exclude the column for a specific across category.

Thanks,
Sean

I have similar reasons to do this and than I do defines based on the several across values.

I see that WF 7 has a HIDE stylesheet keyword, but that won't help me unfortunately, because I'm currently on WF 5.2.8.

I did not know, will take a look next week

Try this:

TABLE FILE CAR
SUM RETAIL_COST ACROSS COUNTRY COLUMNS
ENGLAND AND JAPAN AND ITALY
END

Sean,

You might want to consider going to a HOLD file. Then TABLEing the HOLD file for your final output.

For example:

This code:

  
TABLE FILE CAR
SUM SALES
    RETAIL_COST
BY SEATS
ACROSS COUNTRY
ON TABLE HOLD
ON TABLE SET ASNAMES ON
WHERE COUNTRY IN ('ENGLAND', 'FRANCE', 'JAPAN');
END
-RUN
TABLE FILE HOLD
PRINT SEATS
      SALENGLAND
      RETENGLAND
      RETFRANCE
      SALJAPAN
      RETJAPAN
END

Yields:

  
PAGE     1


SEATS  SALENGLAND  RETENGLAND  RETFRANCE  SALJAPAN   RETJAPAN
-----  ----------  ----------  ---------  --------   --------
    2           0       5,100          0         0          0
    4           0      26,728          0     78030      6,478
    5       12000      13,491      5,610         0          0

In this example Sales and Returns by Seat is displayed, for England and Japan. For France, only Returns are displayed.

Regards,
Dave

Thanks David. Yeah, that technique would be my backup, but I was hoping that I could use a single query with ACROSS and easily strip out what I didn't need (perhaps in the stylesheet section).

Thanks!
Sean

Here's another way, if there are too many columns to list in the column statement or you don't know all the column values for country:

DEFINE FILE CAR
RETCOST/D12.2S MISSING ON=IF COUNTRY EQ 'FRANCE' THEN MISSING ELSE RETAIL_COST;
END
TABLE FILE CAR
SUM
SALES
RETCOST
BY SEATS
ACROSS COUNTRY
WHERE RETCOST IS-NOT MISSING;
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET NODATA 0
ON TABLE SET STYLESHEET *
TYPE=REPORT, BORDER=1, BORDER-COLOR=GREY, FONT='ARIAL', SIZE=10, $
ENDSTYLE
END

Sean,

Sorry I don't have time to fully develop this solution for you, but my thought process goes something like this...expanding on Frank's suggestion, alter the SEQUENCE of the columns in the stylesheet setting your blank column to be the last, set the color of the TITLE and DATA to WHITE, effectively not displaying it? You could do this conditionally in the Stylesheet by using DM branching on a variable you set?

Kevin

Kevin,

I gave that a try, by adding this:
TYPE=REPORT, COLUMN=RETAIL_COST(2), SEQUENCE=50, COLOR=WHITE, BACKCOLOR=WHITE, $

but I get the FOC error:
(FOC3220) STYLESHEETS CAN NOT SEQUENCE 'ACROSS' OR 'OVER' COLUMNS

Thanks,
Sean

gregv,

I still want to display the SALES column for France.

Thanks,
Sean

quote:

HIDE

this keyword is for use in active reports, so it's not what you need.
the solution from Greg with the "missing" is also not what you want.

I think you have to define the several columns or live with the fact it can't be done in a dynamic way.

( the Across function is strong and in many cases very usefull, but sometimes.....)

OK Frank, thanks alot for your help!

Sean