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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Multiple Excel spreadsheets from one fex run

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]Multiple Excel spreadsheets from one fex run
 Login/Join
 
SRC
Gold member
posted
I have a fex that writes to an excel template and it works fine.

 
TABLE FILE CAR
PRINT CAR DCOST
WHERE COUNTRY EQ 'ITALY';
ON TABLE PCHOLD FORMAT XLSX TEMPLATE &APPROOT/baseapp/car_tmplt.xltm SHEETNUMBER 2   
END


My customer wants to do one run and get a separate spreadsheet for each of 20 departments.

They do not want to run the fex several times getting one spreadsheet per run.

I've tried all sorts of COMPOUND and loop processes but can't seem to find the right combination. May not be possible.

Any ideas?

This message has been edited. Last edited by: <Emily McAllister>,


Dev Studio /7.6.11/7.7.02M
MVS/USS
AIX/SOLARIS
Windows WF Client 7.6.8/7.6.11
 
Posts: 64 | Location: Eastern and middle NC | Registered: March 13, 2007Report This Post
GamP
Virtuoso
posted Hide Post
As far as I know (but my knowledge may be outdated a bit) it is not possible te generate more than one excel file.
You can however use the BYTOC to create a separate sheet for every department within the same file.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Dave
Master
posted Hide Post
Looping and making a seperate sheet for each value within the same excel-document works. If that's what you're looking for.


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Wep5622
Virtuoso
posted Hide Post
A single report cannot send multiple separate Excel sheet to a client.

However, it can save them to disk. After that, you can do with those saved files whatever you need, such as zipping them and e-mailing them to some person.

If you need each sheet to go to a different person, you could perhaps make use of ReportCaster's bursting feature.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Joni Campbell - Europa Sports
Silver Member
posted Hide Post
I have one ReportCaster job that, in the Task section has Burst checked and output format HTML selected. In the Distribution section I selected "Dynamic Distribution List" and then specified a fex (which I believe is required to reside in EDASERVE,) that creates a HOLD file (ON TABLE PCHOLD) which contains two fields. The field names must be VALUE (which is the same value as the burst field) and DEST (which is the email address to which the output is sent.) This process sends multiple emails with only the "burst" data included in the body of the email, no attachment.


8009
Windows, HTML, AHTML, Excel
In FOCUS since 1983
 
Posts: 41 | Location: Charlotte, NC | Registered: January 06, 2012Report This Post
SRC
Gold member
posted Hide Post
It looks like I can do the loop process and save each .xlsx file to be opened later. However, in my tests, I don't seem to be able to get the FILEDEF and HOLD/SAVE statements working. The output file appears in the folder but I get an error IBIFEXCEPTION 1212 (FOC1562) EDAGET: FILE NOT FOUND error trying to open it.

My question is: how do I save an excel file to disk, or in my case, an app dev studio folder?

  

-SET &ECHO=ALL;
-SET &CTR=1;

-S1

-SET &CNTRY=IF &CTR=1 THEN 'ITALY'
-      ELSE IF &CTR=2 THEN 'FRANCE'
-      ELSE IF &CTR=3 THEN 'JAPAN' ;

-SET &FIL='CARTST' | &CTR | '.XLSX';
FILEDEF CARTST DISK &APPROOT/src_test/&FIL

TABLE FILE CAR
PRINT CAR BY COUNTRY
WHERE COUNTRY EQ '&CNTRY';
ON TABLE HOLD AS CARTST FORMAT XLSX 
END
-RUN
-EXIT

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


Dev Studio /7.6.11/7.7.02M
MVS/USS
AIX/SOLARIS
Windows WF Client 7.6.8/7.6.11
 
Posts: 64 | Location: Eastern and middle NC | Registered: March 13, 2007Report This Post
Wep5622
Virtuoso
posted Hide Post
This code works for me, I don't see why it wouldn't for you:
-SET &ECHO=ALL;
-SET &CTR=1;

-S1

-SET &CNTRY=IF &CTR=1 THEN 'ITALY'
-      ELSE IF &CTR=2 THEN 'FRANCE'
-      ELSE IF &CTR=3 THEN 'JAPAN' ;

-SET &FIL='CARTST' | &CTR | '.XLSX';
FILEDEF CARTST DISK &APPROOT|\\ibiwork\\&FIL

TABLE FILE CAR
PRINT CAR BY COUNTRY
WHERE COUNTRY EQ '&CNTRY';
ON TABLE HOLD AS CARTST FORMAT XLSX 
END
-RUN


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Kevin W
Platinum Member
posted Hide Post
Seems to me that we have burst spreadsheets in ReportCaster on a BY NOPRINT field (that is the top level BY). I would have to look.


WebFOCUS 7.7.05 (Someday 8)
Windows 7, All Outputs
In Focus since 1983.
 
Posts: 103 | Registered: April 27, 2011Report This Post
eric.woerle
Master
posted Hide Post
Personally I would approach this in 1 of 2 ways...

If you are using ReportCaster, then absolutely use Kevin's suggestion and burst the report. Easy peasy lemon squeezy.... You can even burst it to the library for people to pick up the specific one they want....

The other way I would handle this is by writing a javascript function that would cycle through the Departments according to your "Country" criteria and run the report 20 times (once for each value). You can use a jquery request to pull the values back you need and then load them to a JS array, and step through the array to execute the report for each value in the array. Takes a little more work then bursting, but would be just as effective for a live run.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
SRC
Gold member
posted Hide Post
[Closed] Thanks everyone. We are going to try Report Caster, new to this site.

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


Dev Studio /7.6.11/7.7.02M
MVS/USS
AIX/SOLARIS
Windows WF Client 7.6.8/7.6.11
 
Posts: 64 | Location: Eastern and middle NC | Registered: March 13, 2007Report 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     [CLOSED]Multiple Excel spreadsheets from one fex run

Copyright © 1996-2020 Information Builders