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] Manipulate BYTOC output

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Manipulate BYTOC output
 Login/Join
 
Member
posted
WF 7.7.03

OS/Oracle

Windows

Hello,

I'm trying to modify and existing report that uses BYTOC. What I need is another excel tab added to the report that is basically all inclusive of the tab reports created using BYTOC.

My code for example:
TABLE FILE Users
PRINT USER_DESC AS 'User Name'
USER_ID
ACTIVE
DOMAIN_DESC
BY GROUP_DESC NOPRINT
BY USER_DESC NOPRINT
BY DOMAIN_DESC NOPRINT
WHERE GROUP_DESC CONTAINS 'MSN'

****
$
ENDSTYLE
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT EXL2K
END

The result produces a spreadsheet with multiple reports separated by Group_DESC. I need another tab added to this report that will only by Domain_desc. I appreciate any help on this!!!!! :-\

This message has been edited. Last edited by: <Kathryn Henning>,


7.7.03

OS/Oracle

Windows
Output:All
 
Posts: 20 | Registered: January 22, 2010Report This Post
Virtuoso
posted Hide Post
For Excel (.xlsx) output, you can combine several TABLE requests via SET COMPOUND = OPEN / BREAK / CLOSE, where each TABLE request has PCHOLD ... FORMAT XLSX or equivalent, even if one of the requests uses ON TABLE SET COMPOUND 'BYTOC 1' to populate multiple tabs.

Make sure the non-BYTOC requests specify TITLETEXT, such that all the worksheet names will be unique.

There is documentation to the contrary, but apparently the limitation on combining COMPOUND and BYTOC does not apply to Excel outout.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
Thank you J.GROSS. Do you mind elaborating just alittle more. I ran into a few errors with my first table file request. Please see below:

ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET COMPOUND EQ OPEN
END
-RUN
-*****Report 2 ***************
DEFINE FILE USER

My request did not like the word "OPEN" above?


7.7.03

OS/Oracle

Windows
Output:All
 
Posts: 20 | Registered: January 22, 2010Report This Post
Member
posted Hide Post
I also tried the following for report1 unsuccessful.


ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET COMPOUND BYTOC
ON TABLE SET COMPOUND EQ OPEN
END
-RUN
-*****Report 2 ************ ********
DEFINE FILE RUSER


7.7.03

OS/Oracle

Windows
Output:All
 
Posts: 20 | Registered: January 22, 2010Report This Post
Expert
posted Hide Post
EXL2K:
  
APP PREPENDPATH IBISAMP
SET BYDISPLAY = ON 
TABLE FILE CAR
SUM
   RETAIL_COST 
   DEALER_COST
 BY COUNTRY
  ON TABLE PCHOLD FORMAT EXL2K OPEN
END
-RUN
TABLE FILE CAR
PRINT
  RETAIL_COST
  DEALER_COST
 BY COUNTRY
 BY CAR
  ON TABLE PCHOLD FORMAT EXL2K BYTOC
END
-EXIT


EXL07/XLSX:
 
APP PREPENDPATH IBISAMP
SET BYDISPLAY = ON 
TABLE FILE CAR
SUM
   RETAIL_COST 
   DEALER_COST
 BY COUNTRY
  ON TABLE PCHOLD FORMAT XLSX OPEN
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT = 'Summary',$
ENDSTYLE
END
-RUN
TABLE FILE CAR
PRINT
  RETAIL_COST
  DEALER_COST
 BY COUNTRY
 BY CAR
  ON TABLE PCHOLD FORMAT XLSX BYTOC
END
-RUN
TABLE FILE CAR
SUM
  COUNTRY NOPRINT
  CAR NOPRINT
WHERE RECORDLIMIT EQ 1;
HEADING
"Company Name"
"IBI Car Summary / Detail Report"
"Run Date:<+0>&DATEtrMDYY"
ON TABLE PCHOLD FORMAT XLSX CLOSE
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, TITLETEXT='Parameters', $
TYPE=HEADING,  HEADALIGN=BODY, STYLE=BOLD, $
TYPE=HEADING, LINE=1, JUSTIFY=CENTER, COLSPAN=4,$
TYPE=HEADING, LINE=2, JUSTIFY=CENTER, COLSPAN=4,$
TYPE=HEADING, ITEM=2, COLOR=RED, STYLE=BOLD,$
TYPE=REPORT, WRAP=OFF,$
ENDSTYLE
END
-RUN
-EXIT


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Expert
posted Hide Post
JRS,

If you are interested, this is alink to an earlier post that has code that creates links to other tabs in Excel (EXL2K)

http://forums.informationbuild...857072026#2857072026


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Member
posted Hide Post
quote:
TYPE=REPORT, TITLETEXT = 'Summary',$


I really appreciate your help Tom and Waz! I was able to get the report to work. What was confusing to me by reading the documentation on Compound reports and also looking at similar PDF reports we have already developed. I noticed with the excel compound report there is no need to use a SET COMPOUND = ON at the beginning of the fex or
SET COMPOUND = OFF at the end of the last fex. Yesterday this was causing only one report to be produced. Also I noticed today that for a compound excel report that has a BYTOC included, that the report must be last. It also seems that since my last fex was a BYTOC it seems that the BYTOC acts like a SET COMPOUND = OFF???


7.7.03

OS/Oracle

Windows
Output:All
 
Posts: 20 | Registered: January 22, 2010Report This Post
Member
posted Hide Post
SOLVED!


7.7.03

OS/Oracle

Windows
Output:All
 
Posts: 20 | Registered: January 22, 2010Report This Post
Expert
posted Hide Post
JRS,

Pleasse edit your first post in this thread and change the title to add [SOLVED].

Also I would strongly suggest that if you have multiple TABLE FILEs going into one Excel, you use the COMPOUND commands, even if the report works witout them, as future code tightening could make this report fail on a future release of WebFOCUS.

I would like to see this code that creates a compound document without compound commands, if its possible.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
I am trying to get an empty tab to show when no data exists for the value in my BY statement. I tried the EMPTYREPORT = ANSI and it did not work.


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 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] Manipulate BYTOC output

Copyright © 1996-2020 Information Builders