I have a report with 4 OVER values and across columns, so the titles of the OVER values are printed on the left of the across columns.

One of the four OVER values is needed in some lines but not in all lines.
I can easily suppress the values using zero suppression, but I also need to suppress the title. Dynamic titles are not possible, so I thought to use dynamic styling to make the title text white in some cases. I got this working for PDF output, but it does not work for HTML en EXCEL.

Let me share part of the codes, and an example what I want.

SUM
       AANT_LOPEND_JR/D24C    AS 'Aantal'
OVER   VERSCHIL_TOV_VORIG_JR  AS 'Vers. vorig Jr %'
OVER   MARKT_AANDEEL          AS 'Marktaandeel %'
OVER   PENETRATIE             AS 'Penetratie %'
OVER   INDUSTRY_POT           AS 'Industry pot.%'
BY     IND                    NOPRINT
BY     GEBIED                 AS 'Gebied' NOSPLIT
ACROSS ACROSSSORT             NOPRINT
ACROSS RANKNAAM               NOPRINT
ACROSS MERKNAAM               AS ''

The report generated looks like this:

  
                                                Totaal   Overigen  MERCEDES    SMART 
Gebied
Totaal Land                  Aantal             77.698     43.668     2.106       52 
                             Vers. vorig Jr %     -2,3       -3,0       4,2     15,6
                             Marktaandeel %     100,00      56,20      2,71     0,07
                             Penetratie %         0,00       0,00      0,00     0,00
                             Industry pot.%         
                  
Totaal Regio                 Aantal              1.253         87        77        2 
                             Vers. vorig Jr %     29,2      -20,9      11,6    100,0 
                             Marktaandeel %     100,00       6,94      6,15     0,16
                             Penetratie %         0,00     -87,65    126,94   128,57 
                             Industry pot.%       1,61       0,20      3,66     3,85 

1040024 Baan HENGELO OV      Aantal                612         35        43        2
                             Vers. vorig Jr %      2,7      -54,5       4,9    100,0 
                             Marktaandeel %     100,00       5,72      7,03     0,33 
                             Penetratie %         0,00     -89,82    159,41   371,43
                             Industry pot.%                           

1040025 Baan RIJSSEN         Aantal                445          6        16      158 
                             Vers. vorig Jr %    138,0      -40,0      77,8  1.655,6 
                             Marktaandeel %     100,00       1,35      3,60    35,51 
                             Penetratie %         0,00     -97,60     32,84   374,73
                             Industry pot.%                           

I only want to show the "Industry pot.%" measurement in the "Totaal Regio" block, which is IND EQ '2'
As you can see I suppressed the values by zero suppression, but the title is still showing in all cases.

I have tried the following styling to 'suppress' the title:

TYPE=TITLE, COLUMN=INDUSTRY_POT, COLOR=WHITE, WHEN=IND NE '2' ,$

This works for PDF output, but it does not work for HTML en EXL07.
Does someone know why?
Or does someone have a suggestion how to solve it differently.

thanks, Martin.

This message has been edited. Last edited by: Martin vK,

I can't explain why the style doesn't work for HTML or Excel, but here is alternative - create your own titles as data columns - you have more control over them.

DEFINE FILE CAR
WHEELBASE1/D6S = IF COUNTRY EQ 'JAPAN' THEN 0 ELSE WHEELBASE;
END
 
TABLE FILE CAR
SUM
COMPUTE TSALES/A10 = 'SALES'; AS ''
SALES AS ''

OVER
COMPUTE TWEIGHT/A10 = 'WEIGHT'; AS ''
WEIGHT AS ''

OVER
COMPUTE THEIGHT/A10 = 'HEIGHT'; AS ''
HEIGHT AS ''

OVER
COMPUTE TWHEELBASE/A10 = IF COUNTRY EQ 'JAPAN' THEN '' ELSE 'WHEEL BASE'; AS ''
WHEELBASE1 AS ''

BY COUNTRY AS ''

HEADING
"WEBFOCUS REPORT"

ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON, FONT='ARIAL', SIZE=9, $
ENDSTYLE
END

Hi Francis,

Thanks, I like your suggestion. I have never thought of this way of creating your own titles.
Without across it works very nice.

Unfortunately it does not work with an across report.
I added

ACROSS BODYTYPE

to your code and it gives strange results.

Any suggestions how to solve it for an across report?

Martin.

With WebFOCUS, there is always a workaround:

  
-* File martin01.fex
-*
-* Get number of seats
-*
TABLE FILE CAR
BY SEATS
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HSEATS
END
-RUN
-* Save number of SEAT values
-SET &SNUM=&LINES;
DEFINE FILE CAR
WHEELBASE1/D6S = IF COUNTRY EQ 'JAPAN' THEN 0 ELSE WHEELBASE;
-*
-* Instead of ACROSS create DEFINE fields for each value of SEATS
-*
-REPEAT #DEFS FOR &I FROM 1 TO &SNUM;
-READFILE HSEATS
-SET &SEATS.&I = &SEATS;
SALES&I / I5S = IF SEATS EQ &SEATS THEN SALES ELSE 0;
WEIGHT&I / I5S = IF SEATS EQ &SEATS THEN WEIGHT ELSE 0;
HEIGHT&I / I5S = IF SEATS EQ &SEATS THEN HEIGHT ELSE 0;
WHEELBASE&I / I5S = IF SEATS EQ &SEATS THEN WHEELBASE1 ELSE 0;
-#DEFS
END
-RUN
-*
TABLE FILE CAR
-*
-* Generate the HEADING
-*
HEADING
"WEBFOCUS REPORT"
" <+0>SEATS"
-SET &HEAD='COUNTRY<+0>MEASURE';
-REPEAT #HEAD FOR &I FROM 1 TO &SNUM;
-SET &HEAD=&HEAD | '<+0>' | &SEATS.&I;
-#HEAD
"&HEAD"
-*
-* For each measure display all the columns/SEAT value
-*
SUM
COMPUTE TSALES/A10 = 'SALES'; AS ''
-REPEAT #SAL FOR &I FROM 1 TO &SNUM
SALES&I AS ''
-#SAL
-*
OVER
COMPUTE TWEIGHT/A10 = 'WEIGHT'; AS ''
-REPEAT #WTE FOR &I FROM 1 TO &SNUM
WEIGHT&I AS ''
-#WTE
-*
OVER
COMPUTE THEIGHT/A10 = 'HEIGHT'; AS ''
-REPEAT #HTE FOR &I FROM 1 TO &SNUM
HEIGHT&I AS ''
-#HTE
-*
OVER
COMPUTE TWHEELBASE/A10 = IF COUNTRY EQ 'JAPAN' THEN '' ELSE 'WHEEL BASE'; AS ''
-REPEAT #WBS FOR &I FROM 1 TO &SNUM
WHEELBASE&I AS ''
-#WBS
-*
BY COUNTRY AS ''
-*
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
-*
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON, FONT='ARIAL', SIZE=9, $
-*
-* Style the HEADING to align columns
-*
TYPE=HEADING, HEADALIGN=BODY, $
-SET &COLSP1=2+&SNUM;
TYPE=HEADING, LINE=1, ITEM=1, COLSPAN=&COLSP1, JUSTIFY=CENTER, $
TYPE=HEADING, LINE=2, ITEM=1, COLSPAN=2, $
TYPE=HEADING, LINE=2, ITEM=2, COLSPAN=&SNUM, $
ENDSTYLE
END

DAN: What, no McGuyver today?

MARTIN: Here's how I would approach it, avoiding the Dialog Manager jig.

Use a McGuyver JOIN to make 5 rows out of each incoming data row, with SEQ = 1 to 5.

Define
* the statistic (one variable for all 5 rows and all the ACROSS columns)
VAL/D12.2 = an IF THEN ELSE structure, yielding AANT_LOPEND_JR or VERSCHIL_TOV_VORIG_JR ... or INDUSTRY_POT, depending on SEQ
* its presentation format
FMT/A8= a DECODE of SEQ yielding the appropriate format for each row: D12, D12.1, D12.2,...
* and the row title
DESCR/D20= DECODE of SEQ yielding 'Aantal', etc

Your TABLE request will look like

SUM VAL/FMT AS ''
BY ... /* for the series of groups */
BY SEQ NOPRINT /* for row within group */
BY DESCR AS ''
ACROSS ... /* for the 4 columns */
WHERE ( SEQ LT 5 ) OR ( IND EQ '2' ); /* making the 5th row conditional */

Jack,
Sorry to disappoint you... still one should vary one's pleasures!
Nice with McGuyver! I'll try it out.

A bit later...
FSEQ.MAS for documentation purposes:

  
 FILE=FSEQ, SUFFIX=FIX, DATASET=FOCALPOINT/FSEQ.MAS, $
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$

  
JOIN BLANK WITH SEATS IN CAR TO BLANK IN FSEQ AS M_
DEFINE FILE CAR
BLANK/A1 WITH SEATS = ' ';
WHEELBASE1/I5 = IF COUNTRY EQ 'JAPAN' THEN 0 ELSE WHEELBASE;
VAL/I5S=IF COUNTER EQ 1 THEN SALES ELSE IF COUNTER EQ 2 THEN WEIGHT ELSE IF COUNTER EQ 3 THEN HEIGHT ELSE WHEELBASE1;
DESCR1/A12=DECODE COUNTER(1 SALES 2 WEIGHT 3 HEIGHT ELSE WHEELBASE);
DESCR/A12=IF COUNTRY EQ 'JAPAN' AND DESCR1 EQ 'WHEELBASE' THEN ' '  ELSE DESCR1;
END
-*
SET NODATA=' ',ACROSSTITLE=SIDE
-*
TABLE FILE CAR
HEADING
"WEBFOCUS REPORT"
SUM VAL AS ''
BY COUNTRY
BY COUNTER NOPRINT
BY DESCR AS MEASURE
ACROSS SEATS
WHERE COUNTER LE 4 
END

Very elegant, Jack!

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

Thanks Jack and Daniel,

This is indeed very elegant.
I was at first hesitant as I have not used the McGuyver technique before and was afraid it would make my already complex report more complex and too difficult to understand (thus to maintain).
But after implementing it looks quite okay and easy to follow, I added some comments to explain for future maintenance.

You can even leave out the extra define for the WHEELBASE1 and DESCR1 if you add a WHERE to suppress the whole line, makes it even more easy to understand.

JOIN BLANK WITH SEATS IN CAR TO BLANK IN FSEQ AS M_
DEFINE FILE CAR
BLANK/A1 WITH SEATS = ' ';
VAL/I5S=IF COUNTER EQ 1 THEN SALES ELSE IF COUNTER EQ 2 THEN WEIGHT ELSE IF COUNTER EQ 3 THEN HEIGHT ELSE WHEELBASE;
DESCR/A12=DECODE COUNTER(1 SALES 2 WEIGHT 3 HEIGHT ELSE WHEELBASE);
END
-*
SET NODATA=' ',ACROSSTITLE=SIDE
-*
TABLE FILE CAR
HEADING
"WEBFOCUS REPORT"
SUM VAL AS ''
BY COUNTRY
BY COUNTER NOPRINT
BY DESCR AS MEASURE
ACROSS SEATS
WHERE (COUNTRY EQ 'JAPAN' AND COUNTER LE 3) OR (COUNTRY NE 'JAPAN' AND COUNTER LE 4) 
END

Thanks Jack, also for the remark about the dynamic format of the value, as indeed I have different formats for the different OVER variables.
Thanks Francis, for making the first suggestion and the CAR example that the others worked further on.

In the end my code now looks like this:

JOIN BLANK WITH IND IN HLDFCT9 TO BLANK IN FSEQ AS M_
DEFINE FILE HLDFCT9
BLANK/A1 WITH IND = ' ';
OVERVALUE/D12.2 = IF COUNTER EQ 1 THEN AANT_LOPEND_JR ELSE 
                  IF COUNTER EQ 2 THEN VERSCHIL_TOV_VORIG_JR ELSE 
		  IF COUNTER EQ 3 THEN MARKT_AANDEEL ELSE 
		  IF COUNTER EQ 4 THEN PENETRATIE ELSE INDUSTRY_POT ;
OVERFMT/A8      = DECODE COUNTER(1 'D12C' 2 'D6.1' ELSE 'D12.2');
OVERTITLE/A16   = DECODE COUNTER(1 'Aantal' 2 'Vers. vorig Jr %' 3 'Marktaandeel %' 4 'Penetratie %' ELSE 'Industry pot.%');
END

TABLE FILE HLDFCT9
SUM
OVERVALUE/OVERFMT             AS ''
BY     IND                    NOPRINT
BY     GEBIED                 AS 'Gebied' NOSPLIT
BY     COUNTER NOPRINT
BY     OVERTITLE              AS ''
ACROSS ACROSSSORT             NOPRINT
ACROSS RANKNAAM               NOPRINT
ACROSS MERKNAAM               AS ''

WHERE (IND EQ '2' AND COUNTER LE 5) OR (IND NE '2' AND COUNTER LE 4)

This message has been edited. Last edited by: Martin vK,

Hi Martin and welcome to the forum!
You are a quick learner.
Happy to help.