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     Excluding Across Column from Display

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Excluding Across Column from Display
 Login/Join
 
smiths
Platinum Member
posted
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


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
FrankDutch
Virtuoso
posted Hide Post
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
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
smiths
Platinum Member
posted Hide Post
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


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
FrankDutch
Virtuoso
posted Hide Post
I have similar reasons to do this and than I do defines based on the several across values.




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
smiths
Platinum Member
posted Hide Post
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.


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
FrankDutch
Virtuoso
posted Hide Post
I did not know, will take a look next week




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
AlexU
Gold member
posted Hide Post
Try this:

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


WF 7703M, XP/Win7, MRE, RC, BID, PMF, HTML, PDF, Excel 2000/7/10
 
Posts: 73 | Location: NY | Registered: February 06, 2007Report This Post
David Briars
Master
posted Hide Post
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




Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
 
Posts: 822 | Registered: April 23, 2003Report This Post
smiths
Platinum Member
posted Hide Post
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


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
gregv
Guru
posted Hide Post
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


Greg

current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
 
Posts: 274 | Location: Boston/New England | Registered: February 12, 2006Report This Post
KevinG
Platinum Member
posted Hide Post
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


WF 7.6.10 / WIN-AIX
 
Posts: 141 | Location: Denver, CO | Registered: December 09, 2005Report This Post
smiths
Platinum Member
posted Hide Post
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


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
smiths
Platinum Member
posted Hide Post
gregv,

I still want to display the SALES column for France.

Thanks,
Sean


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
FrankDutch
Virtuoso
posted Hide Post
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.....)




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
smiths
Platinum Member
posted Hide Post
OK Frank, thanks alot for your help!

Sean


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report 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     Excluding Across Column from Display

Copyright © 1996-2020 Information Builders