Hi!!

I am trying to send webfocus reports to blackberry either in excel/exl97/exl2k or html format. They do not work. Can anybody help me here - as to what format etc.

thanks
vj.

Could you please post your fex?

Hello Pietro,

Here is the .fex.

APP MAP PDFHOLD C:\MYNAME\
APP HOLD PDFHOLD
-SET &HEAD_DATE = EDIT(&DATEMTDYY,'999999999999');
-SET &START_DATE = EDIT(&DATEYYMD,'99999999') || '01';
-SET &END_DATE = &DATEYYMD;
-SET &START_YR_DATE = EDIT(&DATEYYMD,'99999999') ;
-RUN
JOIN
NLG_POLICY_TOT.AGENT_CODE IN NLG_POLICY_TOT TO NLG_ORG_TERRITORY.AGENT_CODE
IN NLG_ORG_TERRITORY AS J0
END
DEFINE FILE NLG_POLICY_TOT
-SET &ACTIVITY_CODE = 'S';
WS_SIGN/P1 =
IF ('&ACTIVITY_CODE' EQ 'S' AND ACTIVITY_CODE IN ('T','X') AND STATUS_CODE EQ 'A') THEN -1 ELSE
IF ('&ACTIVITY_CODE' EQ 'S' AND AGENT_SIGN EQ '-') THEN -1 ELSE
IF ('&ACTIVITY_CODE' EQ 'S' AND NAFYP_SIGN EQ '-') THEN -1 ELSE
IF ('&ACTIVITY_CODE' EQ 'S' AND CASES_SIGN EQ '-'
AND POLICY_TYPE EQ 'F') THEN -1 ELSE 1;
WS_NAFYP_SIGN/P1 = IF NAFYP_SIGN EQ '-' THEN -1 ELSE 1;
WS_NAFYP_PREM_AMT/P20.2 = NAFYP_PREM_AMT * WS_NAFYP_SIGN;
MTD_SETTLED_CASE/P20.2 =
IF (ACTIVITY_DATE GE '&START_DATE') AND (ACTIVITY_DATE LE '&END_DATE') THEN
CASE_COUNT * WS_SIGN ELSE 0;
MTD_SETTLED_PREM.CR/P20.2 =
IF (ACTIVITY_DATE GE '&START_DATE') AND (ACTIVITY_DATE LE '&END_DATE') THEN
(GROSS_PREM_CREDIT * WS_SIGN) + WS_NAFYP_PREM_AMT ELSE 0 ;
YTD_SETTLED_CASE/P20.2 =
IF (ACTIVITY_DATE GE '&START_YR_DATE') AND (ACTIVITY_DATE LE '&END_DATE') THEN
CASE_COUNT * WS_SIGN ELSE 0;
YTD_SETTLED_PREM.CR/P20.2 =
IF (ACTIVITY_DATE GE '&START_YR_DATE') AND (ACTIVITY_DATE LE '&END_DATE') THEN
(GROSS_PREM_CREDIT * WS_SIGN) + WS_NAFYP_PREM_AMT ELSE 0;
END
TABLE FILE NLG_POLICY_TOT
SUM
MTD_SETTLED_CASE/P10.1C
MTD_SETTLED_PREM.CR/P10C
YTD_SETTLED_CASE/P10.1C
YTD_SETTLED_PREM.CR/P10C
BY
TERR_DESC
WHERE ( ACTIVITY_DATE GE
'&START_YR_DATE'
)
AND ( ACTIVITY_DATE LE
'&END_DATE'
);
WHERE POLICY_TYPE IN ('N','F')
WHERE COMPANY EQ 'N'
-*-- Activity: Settled -------------------------------------
WHERE (ACTIVITY_CODE IN ('S','+','Y','M','A','C')) OR
(ACTIVITY_CODE IN ('T','X') AND STATUS_CODE EQ 'A')
-*----------------------------------------------------------
ON TABLE HOLD FORMAT EXL97 AS settled
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET SPACES 1
ON TABLE SET STYLE *
TYPE=REPORT, FONT='COURIER NEW', SIZE=8, PAGESIZE=LEGAL, ORIENTATION=LANDSCAPE, $
TYPE=TITLE, FONT = 'COURIER NEW', SIZE = 8, STYLE = BOLD, $
TYPE=HEADING, FONT = 'COURIER NEW', SIZE = 8, STYLE = BOLD, $
GRID=OFF, $
END

It creates the file with an .htm extension. NOt a true excel file. If we do a File Save AS after the report is created - we can email this file to BB.

Please change your HOLD statement to

ON TABLE HOLD AS settled FORMAT EXL97

and try running the fex again.

Hi Pietro,

Thanks, but no go. It creates the .xls file. But I guess it is not a true excel file. As such, when I email to Blackberry - comes out with html codes.

But, if I do a 'file save as' within excel and save it as a true excel 97 or excel 2000 file it works.

Is there a way of doing a file save as in the fex itself - after the excel file is created - so this file can be emailed?

Add this to the top of the program:

FILEDEF SETTLED DISK C:\TEMP\SETTLED.XLS

This allocates a physical file in the C:\TEMP directory (or a directory of your choice).

This should work for all three of the Excel output formats.

have you tried ON TABLE PCHOLD .. FORMAT EXCEL
(the exl2k is xml format, not excel. It makes a subdirectory and a whole buncha other files.)
The old, no-longer-supported, EXCEL output might work, i haven't tried it.
but if it does, would you let us all know?
I have a blackberry job here as well.
thanks.

Susannah,

FILEDEF REP1 DISK C:\TEMP\REP1.XLS
TABLE FILE CAR
SUM SALES
BY COUNTRY
ON TABLE HOLD AS REP1 FORMAT EXL2000
END

...doesn't create any subdirectories, just one rep1.xls file which when double-clicked, opens in MS Excel...

Hello Susannah and Pietro,

No luck. I have tried with old Excel, EXL2K, EXL97. It creates the excel file. I can open the file on my desktop. But when I send it to the Blackberry as an attachment - the results IN the BB are:
Excel - Unknown document format.
EXL2K - HTML CODES SHOW.
EXL97 - HTML CODES SHOWS.

Now my .fex has been changed -

FILEDEF SETTLED DISK C:\TEMP\SETTLED.XLS
FILEDEF SETTLE$ DISK C:\TEMP\SETTLE$.XML
-
-
ON TABLE HOLD AS SETTLED FORMAT EXL97/EXL2K/EXCEL

Excel file created outside of webfocus - can be opened in BB. No problem.

Have you tried editing the WebFOCUS generated Excel file in Excel and then saving it and then sending it to the Blackberry?

Have you tried sending a WebFOCUS PDF file to the Blackberry?

Yes.

Opening the file in Excel and doing a file saveas Excel97/2000 file and sending it to BB works fine.

Sending a pdf - works. Only drawback is does not keep the formatting of the report. The output is 1 word per line. Headings/subheadings/rows columns etc. all get mixed up and come out one word per line.

Vijaya

WebFOCUS is supposed to be Blackberry friendly. Contact IBI tech support.

well whadya know...i tested 2k even with pivots and all extra stuff has gone away. way cool to know. Thanks P.
There was a product called PDA SYNC that i used to have with my mre. It was an extra cost. I recall Gerry saying it was no longer supported. But I would bet there's still a module you have to 'buy' to make wf sync w/ blackberries.

IBI tech support was contacted sometime back. They have replied that - for both excel97 and exl2k the two outputs are actually created with the Microsoft HTML/EXCEL
output type as a result the behavior is expected.

I wondered - if anybody on the forum had any ideas of doing this in a different way.

Does anyone know how to call MS Excel from a DOS command?

If we can write a DOS command to open the WF created Excel file, save it and exit, this may be the solution to the problem.

Here is vbscript code to do this from command prompt. Just invoke it using CSCRIPT.
It will also shrink your xls file by 2 thirds

' WebFocus saves an Excel spread sheet
' as a Excel Format Type Html
' This causes a problem when the xls file
' is opened by DTS.
' This scripts opens the xls file then
' saves it as FormatType xls
' LVW - 09/28/03

Dim WshShell
Dim CmdArgs
Dim Inputfile
Dim Outputfile

Dim xlApp
Dim xlBook
Dim xlSheet

set WshShell= WScript.CreateObject( "WScript.Shell" )
Set CmdArgs = WScript.Arguments

Inputfile = CmdArgs(0)
Outputfile = CmdArgs(1)

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(Inputfile)

xlbook.SaveAs Outputfile , 1

xlApp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

Beautiful!

THANK YOU ALL. I will try this.

Vijaya

Hi all--
We discontinued the PDA sync feature which used a partner product that's no longer viable. The market space never really materialized in time for this.

WebFOCUS is Blackberry friendly but the question is, is Blackberry PDF and XLS friendly?

If you email a PDF or XLS attachment to a Blackberry, it doesn't go straight from your mail server to the Blackberry. First it gets routed by a Blackberry email gateway somewhere... if you're subscribing through a service provider, it's Cingular or Verizon or TMobile or AT&T or whatever, otherwise there's a Blackberry email server at your corporate headquarters doing the work.

Blackberries do not have Acrobat or Excel on them. You can't read those file types on a Blackberry. So the Blackberry email server "transcodes" your attachments for reading on the Blackberry. That's why the output looks "funny."

I'd suggest sending plain text reports to the Blackberry. That's the format where you'll get the closest look to the way they look on your PC. The other formats will make the BEMS transcode them, and they'll never look the same. You can also try HTML email if your Blackberry is a later model (a "blueberry") equipped with a browser.

By the way, the Excel97 hold file format is really just HTML, plain and simple. That's why it has HTML codes. Using HTML was a way to solve the problem of getting formatted stuff into Excel without writing the binary excel format.

Hope this helps.

Bob

Thank you all. Lenny, Pietro - the codes and programs work.

I am glad - I joined the focal point forum. Very good impression for a first timer.

Vijaya

Hi,

I am having the same difficulty what Vijaya had. I understood that with the help of the VB script I should be able to resolve this issue, but my point is where to put this VB SCRIPT? I am not familiar with VB.. some one please help me.

Thanks,
Dayakar.