Focal Point
[CLOSED]Multiple Excel spreadsheets from one fex run

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7057049386

September 19, 2016, 06:28 PM
SRC
[CLOSED]Multiple Excel spreadsheets from one fex run
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
September 20, 2016, 02:26 AM
GamP
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
September 20, 2016, 03:28 AM
Dave
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
September 20, 2016, 07:23 AM
Wep5622
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 :
September 20, 2016, 08:25 AM
Joni Campbell - Europa Sports
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
September 20, 2016, 11:45 AM
SRC
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
September 21, 2016, 04:49 AM
Wep5622
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 :
September 22, 2016, 01:25 PM
Kevin W
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.
September 22, 2016, 02:26 PM
eric.woerle
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
September 23, 2016, 04:45 PM
SRC
[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