Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Manipulate BYTOC output
Go
New
Search
Notify
Tools
Reply
  
[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, 2010Reply With QuoteReport 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, 2005Reply With QuoteReport 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, 2010Reply With QuoteReport 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, 2010Reply With QuoteReport 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: 1955 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport 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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6118 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport 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, 2010Reply With QuoteReport This Post
Member
posted Hide Post
SOLVED!


7.7.03

OS/Oracle

Windows
Output:All
 
Posts: 20 | Registered: January 22, 2010Reply With QuoteReport 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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6118 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Manipulate BYTOC output

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.