I am trying to create two reports from the same fex, but I don't think that I can do it because we are running EXCEL 2000.

Any suggestions?

Thanks!

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

What do you mean by two reports.
Are they completely separate ?
Do they both need to be returned to the clients browser ?

Can they be compound reports ?

This is an example of a compound report:

TABLE FILE CAR
PRINT *
ON TABLE HOLD AS CARHOLD
END

TABLE FILE CARHOLD
PRINT COUNTRY CAR MODEL SALES
WHERE COUNTRY EQ 'ENGLAND';
HEADING
"REPORT 1"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT,$
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9,$

ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END


TABLE FILE CARHOLD
PRINT
COUNTRY CAR MODEL SALES
WHERE COUNTRY EQ 'ITALY';
" "
" REPORT 2 "
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT,$

ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END

You might also be able to use BYTOC. In either case, you need Excel 2000 or higher. Neither COMPOUND or BYTOC will work with 97.

TABLE FILE CAR
PRINT CAR MODEL SALES 
BY COUNTRY 
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY'
HEADING
"REPORT FOR <COUNTRY"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT,$
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9,$

ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END

  

Here is what I am testing with...

I would like to provide Report #1 and #2 as EXCEL Spreadsheets, and I need them to be
tabbed, or be able to print each as a separate page.

I am having an issue because my workplace is running the Office2000 version of EXCEL.

TABLE FILE CAR
PRINT
CAR
MODEL
DEALER
RETAIL
BY COUNTRY
ON TABLE HOLD AS ALLCARS
END
-*
TABLE FILE ALLCARS
-* Report #1
PRINT
CAR
MODEL
BY COUNTRY
WHERE COUNTRY NE 'ENGLAND';
END
-*
TABLE FILE ALLCARS
-* Report #2
PRINT
CAR
MODEL
RETAIL
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND';
WHERE RETAIL LE 10000;
END
-RUN

If you are running 7.6.6 as your signature indicates, you could try the PDF Layout Painter with the output type specified as Excel. That would give you a tab for each report.

Do you want to create two separate Excel files by running one fex with that one fex having two "ON TABLE PCHOLD FORMAT EXCEL" statements? If so, I do not believe that you can do that as you can only open one excel file per fex execution.

Thanks Ginny,

Does anyone have a sample of that with the car or empfile?

SET HTMLARCHIVE=ON
COMPOUND LAYOUT PCHOLD FORMAT EXL2K
UNITS=IN, $
SECTION=section1, LAYOUT=ON, METADATA='0.5^0.5^0.5^0.5', MERGE=OFF, ORIENTATION=PORTRAIT, PAGESIZE=Letter, $
PAGELAYOUT=1, NAME='Page layout 1', text='Page layout 1', TOC-LEVEL=1, BOTTOMMARGIN=0.5, TOPMARGIN=0.5, METADATA='BOTTOMMARGIN=0.5,TOPMARGIN=0.5,LEFTMARGIN=0,RIGHTMARGIN=0,', $
COMPONENT='report1', TEXT='report1', TOC-LEVEL=2, POSITION=(1.775 0.832), DIMENSION=(4.480 3.957), METADATA='Z-INDEX: 100; LEFT: 1.775in; OVERFLOW: auto; WIDTH: 4.48in; POSITION: absolute; TOP: 0.832in; HEIGHT: 3.957in', $
COMPONENT='report2', TEXT='report2', TOC-LEVEL=2, POSITION=(0.938 5.516), DIMENSION=(5.833 4.686), METADATA='Z-INDEX: 100; LEFT: 0.938in; OVERFLOW: auto; WIDTH: 5.833in; POSITION: absolute; TOP: 5.516in; HEIGHT: 4.686in', $
END
SET COMPONENT='report1'
-*component_type report
-INCLUDE ggsales_compound_rpt
SET COMPONENT='report2'
-*component_type report
-INCLUDE centsales_rpt
COMPOUND END

You can write your own 2 reports on demo files and substitue their names on the -INCLUDE statements.

Will try this...Thanks!

Ginny,

I replaced the two "INCLUDE" fexes with two different, but basic, "TABLE FILE CAR" requests and I only get one file with two tabs. However, I thought that Tomsweb originally wanted to produce two separate excel files from within one fex.

quote:

I am trying to create two reports from the same fex

Is that correct?

PS: Ginny's solution works in 764 too.

And later on Tom said:

quote:

I would like to provide Report #1 and #2 as EXCEL Spreadsheets, and I need them to be tabbed, or be able to print each as a separate page.

So Ginny's solution should be ok.

Tom,
Are you "settling for" the tabbed resolution? Or, do you really want the "two separate and discrete Excel files" as the result of the execution of a single fex?

Try coding the first report as
FORMAT EXL2K OPEN

and the second report as

FORMAT EXL2K CLOSE

Tom,

If you want the reports tabbed, here is a solution:

  
-* File tomsweb5.fex
TABLE FILE CAR
PRINT
CAR
MODEL
DEALER
RETAIL
BY COUNTRY
ON TABLE HOLD AS ALLCARS
END
-*
DEFINE FILE ALLCARS
TAB/I1=IF COUNTRY NE 'ENGLAND' THEN 1 ELSE IF RETAIL LE 10000 THEN 2 ELSE 0;
END

TABLE FILE ALLCARS
PRINT
CAR
MODEL
RETAIL
BY TAB NOPRINT
BY COUNTRY
WHERE TAB NE 0;
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END

I tried Daniel's suggestion:

quote:

TABLE FILE CAR
PRINT
CAR
MODEL
DEALER
RETAIL
BY COUNTRY
ON TABLE HOLD AS ALLCARS
END
-*
DEFINE FILE ALLCARS
TAB/I1=IF COUNTRY NE 'ENGLAND' THEN 1 ELSE IF RETAIL LE 10000 THEN 2 ELSE 0;
END

TABLE FILE ALLCARS
PRINT
CAR
MODEL
RETAIL
BY TAB NOPRINT
BY COUNTRY
WHERE TAB NE 0;
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END

and I got this garbage when EXCEL opened
in my browser.

Perhaps there is some setting I need to change or activate in the WebFOCUS profile whatever,
but I wanna believe that this can be done even
if you are using Microsoft Excel 2000.

I tried Daniel's code and it worked fine for me. Please confirm that you have Excel 2000 and not Excel 97. I have had an issue where we created a report with BYTOC that a user with 97 got the underlying code rather than the report when they tried to open it.

I have a compound report which I use the bytoc command in. It is one fex that generates four separate tabs. In my fex I use the following command ON TABLE SET COMPOUND 'BYTOC' Your highest sort field must be the field you want the tabs generated on.

I opened EXCEL and clicked on Help on the toolbar.
I then clicked on About Microsoft Excel, where
the dialogue box stated across the top: Microsoft
Excel 2000 (9.0.4402 SR-1).

When I tried Daniel's suggestion with ON TABLE PCHOLD
FORMAT EXL2K BYTOC, I got the underlying code rather
than the report when I opened it up.

I am using WF 766

What?!?!

BYTOC is only available with excel 2002 or higher. From the documentation:

quote:

Excel Table of Contents (TOC) enables you to generate a multiple worksheet report in which a separate worksheet is generated for each value of the first BY field in the FOCUS report.

Note: This feature can be used only with EXCEL 2002 or higher releases because it requires the Web Archive file format, which was not available in Excel 2000 and earlier releases.

Sorry Tom you can not do it in any way shape or form unless you convert the WebFOCUS output to a binary using some sort of converter.

quote:

Note: This feature can be used only with EXCEL 2002 or higher releases because it requires the Web Archive file format, which was not available in Excel 2000 and earlier releases .

Thanks everyone!

Tom - If you are executing this application on a mainframe server, please see if the following would work for you to split output to multiple excel files.
The concept is to build a JCL from inside the WF fex and submit it to email each file individually.

High level Steps:

1) Go thru the necessary initial processing steps and generate your "master" hold file.
2) Split the master-hold file into multiple individual hold files, the way you want the final output split.
Say you name them HOLD1, HOLD2, HOLD3, etc., sequentially.
3) -SET a variable to store the number of output files from above. (2 in your case, will work fine for any number).
4) DYNAM alloc a mainframe flat file (lets say FLAT-0 for now)
5) -WRITE your shop standard JOB card to FLAT-0
6) -REPEAT loop start with &variable counter from 1 to &count in #3 above
7) DYNAM allocate a unique flat file in each loop flow by using the loop counter in the filename
8) The DD name for each alloc above should also have the loop counter in name for uniqueness like H&CNT..F
8) TABLE FILE HOLD&CNT (will automatically run thru each of your split HOLDx files from #2) above.
9) output of this table-file should be something like this -
ON TABLE HOLD FORMAT EXL2K AS H&CNT..F
10) -WRITE (append) to file FLAT-0 above, a new Job step using H&CNT..F. as the attachment file.
This job step will be whichever standard mainframe jcl utilitly your shop uses to email from mainframe jobs.
11) -ENDLOOP
12) At this point, FLAT-0 will have a jobcard at the top, and an email-utility job-step for each excel file.
13) DYNAM submit FLAT-0.
14) The job will run on the mainframe and send an email per file generated. Works like a charm.

Above steps are just a high level concept. You can get as fancy as you need this to be.
You could hard-code the the email-to parmeter in the job-step -WRITE or define it in a variable too.
The _REPEAT loop times can be dynamic based on the master-hold file contents.
All the the email-utility parameters can be variablized, like the from/to email ids, attachment names. etc.
We actually have an HTML launch page that lets users key-in their email address, runs the app and sends the output to which ever email address was keyed-in by the user.
From the HTML page edits, I have locked the possible domains the users can key-in email addesses, so they do not send to external parties even by mistake.

As long as this process is used on the mainframe, it works with any version of excel, and any version of WF.

Hope this helps.
Sandeep Mamidenna

Tom - I was not sure if you had a chance to see my earlier message. Reposting to get this back to the top of the list.