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     [SOLVED] Deferring a drill down report in MRE
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Deferring a drill down report in MRE
 Login/Join
 
Gold member
posted
I have a report that I need to run deferred because it takes quite a while to run. The report runs a query and puts the detail data into a hold file. Then I'm displaying it by summing by date- so a months worth of data would should a line per day with totals, averages, etc by date. Each date is then a drill down on that hold file so see the detail information.

This works fine if I use an APP HOLD command, but I'm worried about multiple users overwriting each other's hold file.

If I do not specify the APP HOLD, the summary by date report works, but the drill down returns an error that the hold file cannot be found.

The user would be running this as a deferred report through an MRE/BI dashboard. Is there some way to specify a directory per user where I can hold that file? Another post suggested something like
APP MAP WFTEMP c:\ibi\apps\&USERID
APP HOLD WFTEMP
but it was rather old and I'm wondering if there is a better way now.

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


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You could make the HOLD name variable (unique to each execution), use a common APP folder to hold its data and synonym, and pass the hold-name as a drilldown parameter.

But you'll have to address housekeeping issues, to ensure the hold files are deleted after a decent interval.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
<JG>
posted
quote:
APP HOLD

I wish people would get out of the seriously bad habit of using APP HOLD in a production environment.

For your release use FOCCACHE, that's what it's designed for.

For those on a release that does not support FOCCACHE, use a user specific naming convention that
will prevent conflicts and then bother about the cleanup.

OK, Re-read, if running deferred FOCCACHE may not be an option.

The method you mentioned is 100% valid. But the user folder must exist. If you use user profiles there is already
a user specific director available. Same in MRE.

If not then you need to do a OS level check that the folder exists before you can execute the APP commands.

This message has been edited. Last edited by: <JG>,
 
Reply With QuoteReport This Post
Virtuoso
posted Hide Post
JG:

Will FOCCACHE function appropriately for a deferred report, preserving the data for use in a drilldown later in the day?

If the same user fires off two deferred instances of the same report (with different parameter values), won't the second destroy the hold file generated by the first, unless they are given distinct hold names?

-Jack
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
<JG>
posted
you replied faster than my correction Jack.
 
Reply With QuoteReport This Post
Gold member
posted Hide Post
JG-

Would you be so kind as to explain to me how to use foccache in this manner? I'd be happy to use it if I understood HOW. I don't mind reading about it in the documentation either, if you could point me to where this is explained, as foccache does not appear to be in the help index.

I was under the impression that a TABLE FILE...HOLD would save to foccache if no app hold command was issued, but as I said, this is something I have never really understood.


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Reply With QuoteReport This Post
Gold member
posted Hide Post
Just saw your correction as well.

As of right now I am not running deferred so I certainly wouldn't mind understanding foccache better, but eventually I will be so it must work with that.

I don't believe that I'm using user profiles- I certainly do not have user specific folders on edaserve- and I was afraid that I'd have to make sure the folder existed which is why I didn't really like that solution.


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Reply With QuoteReport This Post
<JG>
posted
FOCCACHE is limited to 60 minutes by default (can be extended by the WF server admin) or the end of the browser session
which ever comes first.

Syntax is

ON TABLE HOLD AS foccache/xxx FORMAT whatever

If the cached data is still available when the drilldown is selected then it will be used automatically
(may need an APP FI or a FILEDEF depending on hold format) FOCCACHE is always part of the APPPATH.

The EDATEMP folder disappears after each request but FOCCACHE is designed to persist for a standard 60 minutes (since last access)
or an admin set period as I mentioned.
 
Reply With QuoteReport This Post
Gold member
posted Hide Post
quote:
ON TABLE HOLD AS foccache/xxx FORMAT whatever


Thanks JG. I think that was exactly the piece that I've missing, and while it may not help with THIS report, it will certainly help with others.


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Reply With QuoteReport This Post
<JG>
posted
Just had a look at my 7.7.0.2 system and the caching appears to be a bit improved.
Mistake on my part the timeout of cache by default is 180 minutes not 60.

Create 2 focexecs

Car1

TABLE FILE CAR
PRINT COUNTRY MODEL CAR DEALER_COST RETAIL_COST
ON TABLE HOLD AS CDATA FORMAT FOCUS
END
-RUN
TABLE FILE CDATA
SUM DEALER_COST BY COUNTRY
ON TABLE SET STYLE *
TYPE=DATA, COLUMN = COUNTRY, FOCEXEC= CAR2(COUNTRY=COUNTRY),$
END

and Car2

SET MSG=ON
WHENCE CDATA MASTER
-RUN
TABLE FILE CDATA
PRINT *
WHERE COUNTRY EQ '&COUNTRY'
END

Run car1 and drill to car2, view source and check the output of the WHENCE.

If it shows FOCCACHE as the path then that's now the default I believe it's a server config option.
If not change HOLD AS CDATA to HOLD AS FOCCACHE/CDATA
 
Reply With QuoteReport This Post
Gold member
posted Hide Post
Yeah my default is edatemp not foccache, but I was able to use foccache/filename just fine with the deferred option.

For my purposes with this report, I think I'm ok with the deferred report overwriting the previous one as long as each user can have their own version- which I verified did work. This is only a temporary solution (6 mos to a year) as we are planning a major dashboard likely using a data warehouse to alleviate the issue of some of these long running queries. This will get them what they need for now.

Thanks for the help!


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Reply With QuoteReport This Post
Gold member
posted Hide Post
JJoyce,

You have to be careful with focccaching a hold file when you are defering a report. Remember that a user can run the report and then log out and pick it up later. If they log out after the foccache file has been created, then it will get blown away and the user will error out. If you go this route, I suggest putting a check at the beginning of your drill report to verify that the file is there. If it is not there then you need to rerun your original file before going into the drill. Or at the very least run the file filtered on the drilled criteria.


Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7
 
Posts: 95 | Registered: July 31, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Deferring a drill down report in MRE

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.