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] Hide a BY column based on process data

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Hide a BY column based on process data
 Login/Join
 
Virtuoso
posted
Hi community,

I am pretty sure that's a easy one but cannot figure it out. I may think too much.
I know and found many options on how to accomplish this for measure (SUM/PRINT) data or based on a variable but it needs to be accomplished at data read where BYTOC can be used.

Using below code it result in an Excel file where each country has its own sheet (SET COMPOUND BYTOC).
Up to there, all good.
But I have a request for when COUNTRY EQ 'ENGLAND' (in this case it's the first sheet) the MODEL column (BY MODEL) must not be displayed in that sheet but in others yes.

TABLE FILE CAR
SUM RETAIL_COST
    DEALER_COST
    SEATS
BY COUNTRY
BY CAR
-* BY MODEL must not be displayed (NOPRINT) when COUNTRY EQ 'ENGLAND'
BY MODEL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET COMPOUND BYTOC
END

Any idea ?

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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Member
posted Hide Post
Hey martin ,
i guess this will work for you

-SET &A = IF COUNTRY EQ 'ENGLAND' THEN NOPRINT ELSE '';
TABLE FILE CAR1
SUM RETAIL_COST
DEALER_COST
SEATS
BY COUNTRY
BY CAR
-* BY MODEL must not be displayed (NOPRINT) when COUNTRY EQ 'ENGLAND'
BY MODEL &A
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET COMPOUND BYTOC
END


atul
 
Posts: 3 | Registered: February 06, 2020Report This Post
Virtuoso
posted Hide Post
I will love it if that would be so simple.

You cannot test a column value (field from a master file) outside the TABLE FILE...END
And you cannot (AFAIK) use a master file field in a -SET since the field is unknown in the -SET syntax

I often use variables in TABLE FILE syntax but they are predefined outside the TABLE FILE ...END and not according the data read within the TABLE FILE...END
Such as

-SET &BY01 = 'COUNTRY';
-SET &SUM1 = 'SEATS';
TABLE FILE CAR
SUM &SUM1
BY &BY01
END

But now it has to be defined (or displayed) according to data read. And in my case based on first BY field since this is the field used by the BYTOC command.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
Couple of thoughts:

* If your workbook is looking good, except for the one 'extra' column in the one sheet, then maybe create an Excel macro to delete the one column in the one sheet?

* If your input file has already been extracted from your database, and contains the data needed for the workbook, then maybe create one sheet at a time?
-DEFAULTH &COUNTRY = ' '
-DEFAULTH &COUNTER = 0
-*
-* Pull Countries to be reported.
-*
TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD 
END
-RUN
-SET &NUM_OF_COUNTRYS = &LINES;
-*
-* Create Excel workbook, each country on its own sheet.   
-*
-REPEAT END_OF_LOOP &NUM_OF_COUNTRYS TIMES 
-SET &COUNTER = &COUNTER + 1;
-READFILE HOLD 
-TYPE Creating a sheet for &COUNTRY
-SET &OPEN_OR_CLOSE = IF &COUNTER EQ 1 THEN 'OPEN' ELSE 
-                      IF &COUNTER EQ &NUM_OF_COUNTRYS THEN 'CLOSE' 
-                       ELSE ' ';
-TYPE Open or Close => &OPEN_OR_CLOSE
-SET &DASHASTER = IF &COUNTRY EQ 'ENGLAND' THEN '-*' ELSE ' ';
-*
TABLE FILE CAR
SUM RETAIL_COST
    DEALER_COST
    SEATS
BY COUNTRY
BY CAR
-* MODEL commented out when COUNTRY EQ 'ENGLAND'
&DASHASTER.EVAL BY MODEL
WHERE COUNTRY EQ '&COUNTRY'
ON TABLE PCHOLD AS MYREPT FORMAT XLSX &OPEN_OR_CLOSE 
ON TABLE SET STYLE *
 INCLUDE=jellybean_combo.sty, $
 TYPE = REPORT, TITLETEXT = '&COUNTRY',$
ENDSTYLE
END
-RUN
-END_OF_LOOP  

This message has been edited. Last edited by: David Briars,




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
Virtuoso
posted Hide Post
Thanks for the suggestions David.

Macro is not an option. I have this already in a report and I have nightmares due to my users because they can't manage that...even with all properly setup for them.
You know.. kind of users that still use flip phone :-)

I already have tried using the loop option to create each tab "manually" but it was always returning me errors (invalid format in...) when trying to use the TITLETEXT option the same way as you did and the text put there doesn't contain "invalid" characters.
So it was the reason why I have retuned to the BYTOC format that accept the TITLETEXT that I want.

I will try back and let you know the result. Maybe there is something that I didn't made the same way as it seems to work with your sample.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
This loop seemed to work for me:
 
-DEFAULTH COUNTRY = '';

TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD AS COUNTRIES
END
-RUN

-SET &COUNT = &LINES;
-SET &IDX = 0;

-REPEAT REPORTLOOP &COUNT TIMES

-READFILE COUNTRIES
-SET &IDX = &IDX + 1;
-SET &STATUS = IF &IDX.EVAL EQ 1 THEN 'OPEN' ELSE IF &IDX.EVAL EQ &COUNT.EVAL THEN 'CLOSE' ELSE '';
-SET &NOPRINT = IF &COUNTRY EQ 'ENGLAND' THEN 'NOPRINT' ELSE '';

TABLE FILE CAR
SUM RETAIL_COST
    DEALER_COST
    SEATS
BY COUNTRY
BY CAR
BY MODEL &NOPRINT
WHERE COUNTRY EQ &COUNTRY.QUOTEDSTRING;
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT XLSX &STATUS
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=&COUNTRY, $
ENDSTYLE
END

-REPORTLOOP

-EXIT
 


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Virtuoso
posted Hide Post
Thanks all.

I solved it.

The loop was working since the beginning.
The styling error that I have was due to a included style sheet that also have the TITLETEXT= line in it.
Since I was adding the TITLETEXT= again in the report it result in a bad style sheet error.
Once removed the one from the style sheet, all good.

Once again thanks


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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] Hide a BY column based on process data

Copyright © 1996-2020 Information Builders