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     (Solved) Multiple BYTOC Reports to Excel from one HOLD file

Read-Only Read-Only Topic
Go
Search
Notify
Tools
(Solved) Multiple BYTOC Reports to Excel from one HOLD file
 Login/Join
 
Silver Member
posted
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,


8009
Windows, HTML, AHTML, Excel
In FOCUS since 1983
 
Posts: 41 | Location: Charlotte, NC | Registered: January 06, 2012Report This Post
Master
posted Hide Post
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 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
Silver Member
posted Hide Post
Eric,

I like your plan I'll try it out.


8009
Windows, HTML, AHTML, Excel
In FOCUS since 1983
 
Posts: 41 | Location: Charlotte, NC | Registered: January 06, 2012Report This Post
Master
posted Hide Post
Joni,

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


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Master
posted Hide Post
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 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
Master
posted Hide Post
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.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Silver Member
posted Hide Post
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.


8009
Windows, HTML, AHTML, Excel
In FOCUS since 1983
 
Posts: 41 | Location: Charlotte, NC | Registered: January 06, 2012Report This Post
Master
posted Hide Post
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.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report 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     (Solved) Multiple BYTOC Reports to Excel from one HOLD file

Copyright © 1996-2020 Information Builders