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     [CLOSED]Multiple Excel spreadsheets from one fex run
Go
New
Search
Notify
Tools
Reply
  
[CLOSED]Multiple Excel spreadsheets from one fex run
 Login/Join
 
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, 2007Reply With QuoteReport This Post
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: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
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: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
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: 1664 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
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, 2012Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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: 1664 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
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, 2011Reply With QuoteReport This Post
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, 2013Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.