I need to create Excel output that makes a separate tab for each by value with three separate twists. I'd like to do one extract to a HOLD file, then run three TABLE requests with the output going to separate reports (since a compound report can only have one BYTOC function.) My plan is to FILEDEF three separate outputs. When I move the routine to ReportCaster, how do I get those files to the email created by the job? Thanks.

This message has been edited. Last edited by: Joni Campbell - Europa Sports,

Joni,

I would consider using foccache. If I understand what you are trying to do, you want to write one query that prepares your data. Say HOLDDATA1. Then you want to Create 3 seperate excel files off of that one hold file. Then report cast all 3 excel files at once.

I would create 4 distinct procedures. 1 procedure would run the hold file and store it in foccache (you can find information on foccache in the help files if you've never used it before). Then in each of the three procedures, start with a check to validate that the HOLDDATA1 exists. If it does, then run your table file request, if it doesn't then run the Hold procedure first before proceeding to your query. Once you've built all 4 files, you would then just need to schedule the three excel files in one schedule and your done.

 
-*HOLD Your data 
TABLE FILE CAR
SUM SALES
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS foccache/HOLDMYCAR FORMAT XFOCUS
END
-* You can use what ever format you like, FOCUS / XFOCUS / ALPHA etc.  I just prefer XFOCUS for these 
-* sorts of things

-* start report sample 
-* do this for each of your reports 
-* CHECK FOR EXISTENCE OF HOLD FILE

TABLE FILE HOLDMYCAR
PRINT SALES
BY COUNTRY
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS TESTHOLD
END
-RUN
-IF &LINES GT 0 THEN GOTO :MYREPORT

-INCLUDE HOLDYOURDATA.FEX
-* Assuming your still on 7.7.03, this hold syntax will work.  if On 8.0.x or greater use new INCLUDE SYNTAX

-:MYREPORT

TABLE FILE HOLDMYCAR
SUM SALES
BY COUNTRY
ON TABLE PCHOLD FORMAT XLSX
END

-* RINSE LATHER REPEAT.

No FILEDEF'ing required.

Eric,

I like your plan I'll try it out.

Joni,

Here's another way - perhaps not as elegant as Eric's. See my comments: http://forums.informationbuild...1057331/m/3207047576

George,

Your solution is solving a different problem, although one I can't believe IBI hasn't resolved yet. Back when I was working on 7.1.4, I used ETL to write to the BOT tables and essentially do the samething you are talking about (I like your solution better though, mine was a lot more complex for the same result. I have a post under my old username, ewoerle, somewhere that explains what I did). Its amazing that we can create so many work arounds for bursting compound reports in excel, but IBI over the years hasn't implemented it as a part of ReportCaster.

Eric,

Thanks for the boost to my ego! Ineeded that after the bad news I got yesterday when filing my taxes ... :-(

I'd just like to point out that my technique isn't restricted to spreadsheet output (when will IBI embrace Open Document format I wonder ..). It's also very useful for compound reports that do not necessarily contain spreadsheets where the various sections may not have the same BY field, or the burst value is not the same as any BY field.

Success!

I've been trying lots of different combinations of code, and with the help of this other post:
APP HOLD and APP FI... post

I was able to come up with this process:

In the 1st fex, I put:
APP HOLD xyz

and then created the HOLD file with
ON TABLE HOLD AS ABC FORMAT ALPHA


In the successive report creating fex routines, I added
APP FI ABC DISK xyz/ABC.FTM

I intended to use the APP PREPENDPATH command as well, but it had a typo which gave me an error message, and created the spreadsheet anyway, so I realized that the PREPRENDPATH command was unnecessary (because the directory is in my app path.)

Early in the process I was trying to use the FOCCACHE technique, but it was unsuccessful because the separate tasks in ReportCaster use different agents, so they don't share the FOCCACHE space. From that part of the process, though, I learned that putting the HOLD file to FOCCACHE when running from DevStudio would allow me to reuse the extracted data without have to go back to the data source every time. Just for that revelation this exercise was well worth it!

Thanks to George and Eric for their expertise, and Rob M and Ginny for their posts back in 2008.

Joni,

I'm happy that you got the result you wanted but as Eric pointed out, all of that FILEDEFing (APP FI tells me you are newish to WF) isn't necessary.

When you create any HOLD file the MFD (Synonym) is created automatically and the location and synonym remain accessible for the duration of the FEX. So you could have a hundred intermediate HOLD files and just reference them anytime before the end of the FEX without any FILEDEF.

If you want to SEE the hold files somewhere, just to know they have been created, then the APP HOLD command is useful. Otherwise they get created in a temporary folder which is a bit tricky to find.