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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Multiple Queries with Excel Output

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Multiple Queries with Excel Output
 Login/Join
 
Member
posted
I need to create a Compound report.
There will be 5 queries.
The output of the first 3 queries will need to go onto Sheet 1 in the Excel Spreadsheet.
The output of the last 2 queries will need to go onto Sheet 2 in the Excel Spreadsheet.
I can create the Compound report in InfoAssist with all 5 queries using the same selection criteria.
However, when I run the Compound report, each query goes to a separate Sheet in Excel so I end up with 5 Sheets in my Excel Spreadsheets.
How do I tell InfoAssist when to insert a New Sheet (page break)?

Thanks

Eric J. Songy

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


WebFOCUS 8.0.09
IBM i
HTML,PDF,Excel
 
Posts: 8 | Location: New Orleans, La | Registered: March 12, 2010Report This Post
Guru
posted Hide Post
Eric,

Unfortunately you are not able to do this using the new Compound Report (Composer tool or InfoAssist)

quote:
There are several requirements and restrictions when using these output formats with coordinated compound reports:
In active report and active Flash output formats:
  • Multiple reports and charts combined into one document.
  • You must have at least one report object.
  • LINE, IMAGE, and TEXT objects are not supported in the document.
  • Chart objects are static images, which cannot be altered using active report and active Flash menu options.

Excel:
  • Combines multiple reports as different sheets in a workbook.
  • LINE, IMAGE, and TEXT objects are not supported in the document.

PowerPoint:
  • Combines multiple reports, charts in a single slide.
  • You may embed the output into pre-existing PowerPoint templates.
  • LINE, IMAGE and TEXT objects are supported.


You will have to do this with the old compound syntax which means coding without a GUI


WebFOCUS 8.2.03 (8.2.06 in testing)
 
Posts: 253 | Location: Melbourne, Australia | Registered: February 07, 2007Report This Post
Member
posted Hide Post
StuBouyer

Can I insert commands via the Text Editor that would allow 2 or 3 queries on a single sheet with a page break (sheet break) inserted and then 2 or 3 queries on a second sheet?

I have seen references to NOBREAK and I have tried to test that but have not been successful in getting it to work yet. Every query generates a separate sheet in the Excel Workbook.

Thanks

Eric J. Songy


WebFOCUS 8.0.09
IBM i
HTML,PDF,Excel
 
Posts: 8 | Location: New Orleans, La | Registered: March 12, 2010Report This Post
Guru
posted Hide Post
Try creating a new report in the Text Editor and pasting in this code. it created 5 reports over two excel pages.

I assume you have the CAR file available.

TABLE FILE CAR
PRINT SEATS
BY COUNTRY
HEADING
"REPORT 1"
"Page 1"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD AS MYEXCEL FORMAT XLSX OPEN NOBREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

TABLE FILE CAR 
SUM SALES
BY COUNTRY
HEADING
"REPORT 2"
"Page 1"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD AS MYEXCEL FORMAT XLSX 
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

TABLE FILE CAR 
SUM DEALER_COST
BY COUNTRY
HEADING
"REPORT 1"
"Page 2"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD AS MYEXCEL FORMAT XLSX  NOBREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

TABLE FILE CAR 
SUM RETAIL_COST
BY COUNTRY
HEADING
"REPORT 2"
"Page 2"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD AS MYEXCEL FORMAT XLSX  NOBREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

TABLE FILE CAR
PRINT SEATS SALES DEALER_COST RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
HEADING
"REPORT 3"
"Page 2"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD AS MYEXCEL FORMAT XLSX  CLOSE
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END


WebFOCUS 8.2.03 (8.2.06 in testing)
 
Posts: 253 | Location: Melbourne, Australia | Registered: February 07, 2007Report This Post
Member
posted Hide Post
Thanks Stu

I got this working.
Appreciate the help.

Eric J. Songy


WebFOCUS 8.0.09
IBM i
HTML,PDF,Excel
 
Posts: 8 | Location: New Orleans, La | Registered: March 12, 2010Report This Post
Member
posted Hide Post
how can I save different reports in deticated worksheets within xlsx files?

 INCLUDE = endeflt,
$
SUMMARY='data',
TITLETEXT='data',
$
TYPE=DATA,
     IN-RANGES='T_data',
$
TYPE=TITLE,
     IN-RANGES='T_data',
$
ENDSTYLE
END 


this is working for the first report, but not for the second, obviously with different tab-name.

any ideas?


WebFOCUS 8.1.0.5 (Server + Client)
Windows 2012 R2
BW, Oracle, Excel, PDF, HTML
 
Posts: 23 | Registered: May 28, 2013Report This Post
Platinum Member
posted Hide Post
Have you tried to insert the following line of code (TITLETEXT = 'tab name') as below? You won't need "IN-RANGE = 'T-data'" line.

TABLE FILE CAR
PRINT MODEL
BY COUNTRY
ON TABLE SET STYLE *
TYPE=REPORT,
SIZE=9,
TITLETEXT='Detail',
GRID=ON,
BORDER=OFF,
BACKCOLOR=WHITE,
$
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K OPEN
END
TABLE FILE CAR
COUNT MODEL
BY COUNTRY
ON TABLE SET STYLE *
TYPE=REPORT,
SIZE=9,
TITLETEXT='Summary',
GRID=ON,
BORDER=OFF,
BACKCOLOR=WHITE,
$
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
 
Posts: 117 | Location: Denver | Registered: July 27, 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     [SOLVED] Multiple Queries with Excel Output

Copyright © 1996-2020 Information Builders