Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Suppress title of OVER column

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Suppress title of OVER column
 Login/Join
 
Platinum Member
posted
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,


WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 168 | Registered: March 29, 2013Report This Post
Expert
posted Hide Post
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


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
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.


WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 168 | Registered: March 29, 2013Report This Post
Virtuoso
posted Hide Post
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



Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
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 Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
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,


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Platinum Member
posted Hide Post
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,


WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 168 | Registered: March 29, 2013Report This Post
Virtuoso
posted Hide Post
Hi Martin and welcome to the forum!
You are a quick learner.
Happy to help.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Suppress title of OVER column

Copyright © 1996-2020 Information Builders