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     Want to do Compound Reports
Go
New
Search
Notify
Tools
Reply
  
Want to do Compound Reports
 Login/Join
 
Gold member
posted
Hello.

I'm trying to send more than one report into a PDF or Excel file by coding:

ON TABLE PCHOLD FORMAT '&WFFMT' OPEN (for the 1st)

...

ON TABLE PCHOLD FORMAT '&WFFMT' CLOSE (for the last)

and I get an error:

0 ERROR AT OR NEAR LINE 107 IN PROCEDURE BA_LisenFOCEXEC *
(FOC002) A WORD IS NOT RECOGNIZED: OPEN
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND
0 NUMBER OF RECORDS IN TABLE= 9 LINES= 9
(FOC1394) CONNECT FAILURE
(FOC1400) SQLCODE IS -1032 (HEX: FFFFFBF8)
: [S1000] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine
: cannot open the file '(unknown)'. It is already opened exclusively by
: another user, or you need permission to view its data. [IM006] [Microsof
: t][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
L (FOC1406) SQL OPEN CURSOR ERROR.

HTML show all reports one after the other without the OPEN and CLOSE commands but Excel 2K and PDF only show the first one.

Any idea? Confused
 
Posts: 87 | Location: Montreal, QC, Canada | Registered: September 03, 2004Reply With QuoteReport This Post
Silver Member
posted Hide Post
hi flipper.

One thing, Compound reporting only works with PDF, not excel, so you won't get it to work with any Excel.

I'm not sure why you get that error - maybe try taking the single quotes off of the amper variable.
 
Posts: 37 | Registered: April 14, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
Are you kidding? There is no way to send more than one report into the same Excel File? Eeker

I have replaced the '&WFFMT' by PDF for testing purposes, but I get the same error...

There must be a way to do this, I have 3 reports to show at the same time Mad

I really need this to work!!

Thanks
 
Posts: 87 | Location: Montreal, QC, Canada | Registered: September 03, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I'm not sure why you want the file name, parms for the pdf are not liking, however here is a code sample to create a PDF compound repor, sorry didn't clean up my commented out code. It creates two reports in one pdf file. The file can be saved from ADOBE

JOIN CLEAR *
JOIN TERM_CYT AND STU_ID IN RTRTTBL_UNO_PROD TO ALL TERM_CYT AND STU_ID IN
RTPGTBL_UNO_PROD AS AJPG
END
TABLE FILE RTRTTBL_UNO_PROD
SUM STU_ID AS STU_ID
CAREER_LEVEL AS CAREER
RT020/D12.2 AS CREDIT_HOURS
RT015/D12.2 AS FAM_HOURS
RT090 AS RT090
BY TERM_CYT AS TERM_CYT
BY STU_ID
IF ACAD_PGM_SEQ EQ 1
IF TERM_CYT EQ '20033' OR '20023'
-*IF RT090 NE 'N'
IF DEGREE EQ 'BGS'
ON TABLE HOLD AS HOLD1
ON TABLE SET ASNAMES ON
END
DEFINE FILE HOLD1
FULLPART/A5 = IF RT090 EQ 'F' OR 'S' THEN 'FULL' ELSE IF RT090 EQ 'N'
THEN 'NOT' ELSE 'PART';
END
TABLE FILE HOLD1
ON TABLE SET COMPOUND OPEN
ON TABLE PCHOLD FORMAT PDF
-*ON TABLE SET ONLINE-FMT PDF
ON TABLE COLUMN-TOTAL ROW-TOTAL
ON TABLE SET STYLE *
TYPE=REPORT,FONT = COURIER, SIZE = 10 , $
ENDSTYLE
HEADING CENTER
"UNVERSITY OF NEBRASKA AT OMAHA"
"ENROLLMENT FIGURES FOR FALL 2003/FALL 2002"
"GENERATED ON &DATE AT &TOD"
" "
COUNT STU_ID AS ''
-*BY CAREER
BY TERM_CYT AS 'TERM'
ACROSS FULLPART AS ''
END
TABLE FILE HOLD1
ON TABLE SET COMPOUND CLOSE
-*ON TABLE SET ONLINE-FMT PDF
ON TABLE PCHOLD FORMAT PDF
ON TABLE COLUMN-TOTAL ROW-TOTAL
ON TABLE SET STYLE *
TYPE=REPORT,FONT = COURIER, SIZE = 10 , $
ENDSTYLE
HEADING CENTER
"UNVERSITY OF NEBRASKA AT OMAHA"
"ENROLLMENT HOURS FOR FALL 2003/FALL 2002"
"GENERATED ON &DATE AT &TOD"
" "
SUM CREDIT_HOURS
-*FAM_HOURS
-*BY CAREER
BY TERM_CYT AS 'TERM'
ACROSS FULLPART AS ''
END
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Reply With QuoteReport This Post
<Pietro De Santis>
posted
Compound PDF report example:

-SET &ECHO=ALL;

SET PAGE = NOPAGE
-RUN

SET COMPOUND = OPEN NOBREAK
-RUN

TABLE FILE CAR
SUM SALES
BY CAR
ACROSS COUNTRY
HEADING
"Sales Report"
" "
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
UNITS=IN, PAGESIZE='Letter', SQUEEZE=ON, ORIENTATION=LANDSCAPE, $
TYPE=HEADING, SIZE=13, $
TYPE=REPORT, FONT='Arial', SIZE=11, COLOR=RED, $
ENDSTYLE
END
-RUN

SET COMPOUND = NOBREAK
-RUN

TABLE FILE CAR
SUM DEALER_COST
BY MODEL
ACROSS COUNTRY
HEADING
"Inventory Report"
" "
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
UNITS=IN, PAGESIZE='Letter', SQUEEZE=ON, ORIENTATION=LANDSCAPE, $
TYPE=HEADING, SIZE=13, $
TYPE=REPORT, FONT='Arial', SIZE=11, COLOR=GREEN, $
ENDSTYLE
END
-RUN

-SET &TEMPDIR = 'C:\Temp\';
-SET &GIFFILE=&TEMPDIR || 'CARGR.GIF';

SET COMPOUND = CLOSE
-RUN

TABLE FILE CAR
PRINT CAR
HEADING
"Car List"
" "
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET PAGE OFF
ON TABLE SET STYLE *
UNITS=IN, PAGESIZE='Letter', SQUEEZE=ON, ORIENTATION=LANDSCAPE, $
TYPE=HEADING, SIZE=13, $
TYPE=REPORT, FONT='Arial', SIZE=11, COLOR=BLUE, $
ENDSTYLE
END
-RUN
 
Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks for your Help Pietro, I had it work following your code.

FYI, to create more than one report in Excel, here's the code:

Example 1
-TYPE WEBFOCUS CGIVAR IBIWF_mreports=INDEX
-TYPE WEBFOCUS CGIVAR IBIWF_mprefix=PDFA Report
TABLE FILE CAR
PRINT CAR
ON TABLE PCHOLD FORMAT EXCEL
END
-RUN
-TYPE WEBFOCUS CGIVAR IBIWF_mprefix=PDFB Report
TABLE FILE CAR
PRINT COUNTRY
ON TABLE PCHOLD FORMAT EXCEL
END


Example 2
-TYPE WEBFOCUS CGIVAR IBIWF_mreports=frame
TABLE FILE CAR
PRINT CAR MODEL SEATS
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
TABLE FILE CAR
PRINT RETAIL_COST DEALER_COST
BY CAR
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
TABLE FILE EMPLOYEE
PRINT *
ON TABLE PCHOLD FORMAT EXL2K
END

I really like Ex 2... Wink
 
Posts: 87 | Location: Montreal, QC, Canada | Registered: September 03, 2004Reply With QuoteReport This Post
<Pietro De Santis>
posted
FliPPeR,

This is quite interesting.

Unfortunately, for example 2, when I tried to save the excel spreadsheets that were open in the web browser window, it only saves the last one.

One day we'll get multi-pane Excel spreadsheets.

Thanks.

Pietro.
 
Reply With QuoteReport This Post
<monte2000>
posted
PDF works now. We do have a new feature in 5.3 release where you can send multiple reports to a single Excel file. Speak to your rep about this.

From the Report Output page:

WebFOCUS Integration With Excel capabilities allow:
The creation of compound Excel reports that include data from varying sources as multiple, tabbed spreadsheets contained within one Microsoft Excel workbook.


Kind regards,

Monica Big Grin
 
Reply With QuoteReport This Post
Gold member
posted Hide Post
I tried both those examples in 5.2.4. The first one created 2 separate reports, the second one only created the last table file. I got excited there for a moment!
 
Posts: 68 | Location: Springfield MA | Registered: May 07, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
Pietro.

I have the same problem, only the last report can be saved. There is a workaround for that:

In Windows: Explorer on My Computer --> Menu Tools/Folder Options/File Types

Look for .XLS, click on Advanced and UNCHECK the last checkbox.

This will allow WF to open Excel instead of embedding it into your browser. That way you get 3 reports in the same Excel instance and you can save them individually.

I know, this must be done on all users PC, but if like me, only 3 users are using those Excel 'compound' reports, it's no big deal.

We're not going to upgrade to version 5.3 now, we'll wait a bit for all the bugs to be corrected, later next year... So this is what I'm using for the moment.
 
Posts: 87 | Location: Montreal, QC, Canada | Registered: September 03, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Want to do Compound Reports

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