Focal Point
Newbie question regarding HOLD files
July 24, 2008, 02:15 PM
Rob M.Newbie question regarding HOLD files
I have a large data source and what I want to do is set up a report caster job to execute a query nightly to run a report for open purchase orders meeting certain criteria and have it store/hold the file somewhere on the server.
Then I want to have my end user reports use this held file as the data source so it can query the data really fast since it won't have to process a lot of WHERE statements and sift through the huge database.
How do I set up my first report to save the file or hold the file out on the server somewhere? I know how to do the report caster stuff, just don't know how to do this hold file thing.
Sorry for the newbie question. = )
Rob M.
Target Corporation
WF 7.1.4
July 24, 2008, 02:25 PM
GinnyJakesRob,
First, before I forget, please update your signature with your platform. That would really be helpful.
Here is one method that works pretty well but I'm sure that others will chime in with others.
First, let us assume for purposes of simplicity, that you want to store the file in the ibi/apps of the reporting server. If you want to put it somewhere else, look up the APP MAP.
One thing you can do in the program that creates the file is put an APP HOLD dirname where dirname is the directory where your master and ftm file will go when you say ON TABLE HOLD AS rptfile FORMAT ALPHA or whatever format you want it in. When you run this program the file and master will be written to the directory specified.
In the programs that use this file, at the begining say
APP PREPENDPATH DIRNAME
APP FILE RPTFILE DISK dirname/rptfile.ftm
followed by your report request.
There are other ways. This is just one. Let me know if you have any questions. And play around with the CAR file till you understand where things go.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
July 24, 2008, 02:34 PM
Rob M.Thanks Ginny, I will give it a shot.
Regarding my platform, I have no clue what it is.
My computer that runs Dev Studio is XP Professional.
I know the webfocus data I use is on a Windows Server of some sort. Beyond that I don't have a clue.
Rob M.
Target Corporation
WF 7.1.4
July 24, 2008, 02:43 PM
susannahrob,
the manual
"developing reporting applications".
Your caster job would create a database, a .foc file, at night.
your users would execute fexes you have written to read that database.
Have you had any training at all?
| In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID |
July 24, 2008, 02:48 PM
Rob M.quote:
Originally posted by susannah:
rob,
the manual
"developing reporting applications".
Your caster job would create a database, a .foc file, at night.
your users would execute fexes you have written to read that database.
Have you had any training at all?
Yes I have done the 4 day IBI training for Dev Studio but they didn't go through a lot of stuff on HOLD's. Working for a large corporation like Target there are a lot of things we are not allowed to do or don't have access to do with Webfocus so they skimmed over a lot of stuff.
There was no information given to us on server names/paths etc.
Ironically enough there are not many training resources either, it's basically just talking to other users and searching the Dev Studio help files and searching on here for answers.
Rob M.
Target Corporation
WF 7.1.4
July 24, 2008, 03:58 PM
Rob M.Ginny, your commands are confusing, how do I know what parts I need to change and fill in my own pieces?
See code, below, am I screwing something up here for the second part?
I got the first part to work where it did the APP HOLD, but it only created a .mas, I don't see a .ftm anywhere.
2nd File......
APP PREPENDPATH DIRNAME
APP FILE RPTFILE DISK pdofin/FMANAGERSPODATAHOLD.ftm
TABLE FILE FMANAGERSPODATAHOLD
SUM
CNT.PONUM AS 'COUNT,PONUM'
TOTALCOST
BY REGION
ACROSS PO_AGE IN-GROUPS-OF 180 TOP 180
Rob M.
Target Corporation
WF 7.1.4
July 24, 2008, 04:02 PM
susannahdownload the 4 basic manuals and read them.
there are a TON of training resources.
| In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID |
July 24, 2008, 04:15 PM
Tom FlynnHey Rob,
Sorry about the previous, I thought I had seen you on here before!!
Anyway, on the reporting server, I "hope" you have a storage folder for FOCUS files, TXT or DAT files, etc.
Some explanations:
APP PREPENDPATH dirname
Prepends the FOLDER in ibi/apps to the front of the all other folders in the PATH.
APP HOLD dirname
Holds the data AND master in the folder designated
So,
In the application directory, I have a TEMP folder:
APP HOLD TEMP
TABLE FILE CAR
SUM
CAR
MODEL
BODYTYPE
SEATS
DEALER_COST
LENGTH
WIDTH
HEIGHT
WEIGHT
WHEELBASE
FUEL_CAP AS 'Fuel Capacity: '
COMPUTE PROFIT/P10.2C = RETAIL_COST - DEALER_COST;
BY CAR NOPRINT
ON TABLE HOLD AS CAR_DATA
END
-EXIT
Now you have car_data.ftm and car_data.mas in the TEMP folder.
You will HAVE to add TEMP to edasprof.prf for users to access data from this folder via the GUI.
If you do APP HOLD foldername which is "already" on the PATH, from edasprof, then they will automatically see the file...
? PATH
gives you a listing of the folders in the PATH
Hope this helps
Tom
P.S. Ginny, you weren't on, so...
Don't mean to steal your thunder!!!
Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
July 24, 2008, 04:18 PM
GinnyJakesRob,
In regard to your platform, if you are not using localhost to develop under Projects, then your reporting server is on another platform. From what you are saying, that platform is probably Windows. Or you may be using your local reporting server and an ODBC connection to get to the data on another box.
But back to your problem. You didn't post your first program. Here is an example using the CAR file. Please note that the string 'c758760' is a directory name on my server under the ibi/apps for the reporting server.
-* Program to hold the data.
APP HOLD C758760
TABLE FILE CAR
PRINT SALES DEALER_COST RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS CARHOLD FORMAT ALPHA
END
Now here is a program that uses the held data:
APP PREPENDPATH C758760
APP FI CARHOLD DISK C758760/carhold.ftm
TABLE FILE CARHOLD
SUM SALES DEALER_COST RETAIL_COST
BY COUNTRY
END
I am on a UNIX platform so my slashes go the other way. But if you take my examples and change the c758760 to a directory in your installation, you should be able to see the .mas and .ftm that are created.
Let me know how you do with this example.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
July 24, 2008, 04:21 PM
GinnyJakesSorry, Tom, we posted at the same time. I was in a meeting so didn't respond earlier.
Rob doesn't need to put the directory where he stores his file in the edasprof.prf. That can be too much maintenance in a site that has lots of different users and directories like we have. We have basic directories in edasprof and everyone else uses the syntax I show in my previous post. Eazy shmeezy as my manager would say.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
July 24, 2008, 04:23 PM
Tom FlynnHey Ginny,
The reason I put that in was that I am "guessing" the users would use the Painter for ad-hoc reports. I think they build their own...
Unless, he is using Reporting Objects...
Maybe not, over-think is over-thunk!!
Tom
Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
July 24, 2008, 04:34 PM
GinnyJakesI was assuming that the reports were already written based on what he said. I could be wrong; only Rob can tell us.
At any rate, there is enough info in both posts for him to be able to move forward.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
July 24, 2008, 05:24 PM
PBrightwellquote:
I have a large data source and what I want to do is set up a report caster job to execute a query nightly to run a report for open purchase orders meeting certain criteria and have it store/hold the file somewhere on the server.
Are these JD Edwards tables? If they are you are going to have two security issues that will need to be dealt with first.
#1. If you are extracting data and holding it in a FOCUS database (.foc) or a FOCUST flat file (.ftm) you will not have JDE security. You will probably need permission from your JDE administrator.
#2. You may need authorization to create a file on your data server.
An alternative to holding a nightly file would be to have your database admin create a view.
Target is a fairly big shop. Ask around someone can help you with this information.
Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
July 24, 2008, 05:43 PM
Rob M.quote:
Originally posted by PBrightwell:
Are these JD Edwards tables?
Oh don't even get me started on my JD Edwards woes! = )
That is such a mess here, no one wants to touch anything having to do with JDE and attempt to get a connection to Webfocus.
Rob M.
Target Corporation
WF 7.1.4
July 25, 2008, 11:56 AM
PBrightwellquote:
Oh don't even get me started on my JD Edwards woes! = )
It isn't just there. For financial reporting within JDE it is a great tool, but for anyone used to using Oracle or DB2 with WebFocus it is a pain. What is this business of right justifying alpha fields? (or it that just here).
Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
July 25, 2008, 02:46 PM
Rob M.quote:
Originally posted by GinnyJakes:
But back to your problem. You didn't post your first program. Here is an example using the CAR file. Please note that the string 'c758760' is a directory name on my server under the ibi/apps for the reporting server.
-* Program to hold the data.
APP HOLD C758760
TABLE FILE CAR
PRINT SALES DEALER_COST RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS CARHOLD FORMAT ALPHA
END
Now here is a program that uses the held data:
APP PREPENDPATH C758760
APP FI CARHOLD DISK C758760/carhold.ftm
TABLE FILE CARHOLD
SUM SALES DEALER_COST RETAIL_COST
BY COUNTRY
END
I am on a UNIX platform so my slashes go the other way. But if you take my examples and change the c758760 to a directory in your installation, you should be able to see the .mas and .ftm that are created.
Let me know how you do with this example.
Ok Ginny,
Here is what I put in my second file....
APP PREPENDPATH PDOFIN
APP FILE FMANAGERSPODATAHOLD DISK PDOFIN/FMANAGERSPODATAHOLD.ftm
But when I try to use that I get the following error when I try to save the fex file
"Error Parsing Report Request - >APP< APP PREPENDPATH PDOFIN"FYI here is the code for the first file, it seems to be working just fine, it is outputting FMANAGERSPODATAHOLD.mas and .ftm to my directory.
APP HOLD pdofin
TABLE FILE T_PROD_PO
PRINT
PO_AGE AS 'PO AGE (Days)'
PONUM AS 'PO NUMBER'
TOTALCOST/P12.2C AS 'OPEN PO,$ AMONT'
BY REGION
BY BSTGROUP
BY DIST
WHERE ( T_PROD_PO.STATUS NE 'CAN' ) AND (( GLDEBITACCT LIKE '4604600%' ) OR ( GLDEBITACCT CONTAINS '657004' OR '657006' OR '657007' OR '650124' OR '637000' OR '657008' OR '657009' OR '650132' OR '650130' OR '657011' OR '662000' OR '078000' OR '656000' OR '657001' OR '657003' OR '657010' OR '657020' OR '657030' OR '657040' OR '657050' OR '657000' )) AND (( ORDERDATE GE DT(2007-01-01 00:00:00) ) AND ( RECEIPTS EQ 'NONE' )) AND (( T_PROD_WORKORDER.STATUS NE 'CAN' OR 'CLOSE' ) AND ( T_PROD_PO.TOTALCOST NE .00 ));
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS FMANAGERSPODATAHOLD FORMAT ALPHA
Rob M.
Target Corporation
WF 7.1.4
July 25, 2008, 02:48 PM
Rob M.quote:
Originally posted by Rob M.:
quote:
Originally posted by GinnyJakes:
But back to your problem. You didn't post your first program. Here is an example using the CAR file. Please note that the string 'c758760' is a directory name on my server under the ibi/apps for the reporting server.
-* Program to hold the data.
APP HOLD C758760
TABLE FILE CAR
PRINT SALES DEALER_COST RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS CARHOLD FORMAT ALPHA
END
Now here is a program that uses the held data:
APP PREPENDPATH C758760
APP FI CARHOLD DISK C758760/carhold.ftm
TABLE FILE CARHOLD
SUM SALES DEALER_COST RETAIL_COST
BY COUNTRY
END
I am on a UNIX platform so my slashes go the other way. But if you take my examples and change the c758760 to a directory in your installation, you should be able to see the .mas and .ftm that are created.
Let me know how you do with this example.
Ok Ginny,
Here is what I put in my second file....
APP PREPENDPATH PDOFIN
APP FILE FMANAGERSPODATAHOLD DISK PDOFIN/FMANAGERSPODATAHOLD.ftm
But when I try to use that I get the following error when I try to save the fex file
"Error Parsing Report Request - >APP< APP PREPENDPATH PDOFIN"FYI here is the code for the first file, it seems to be working just fine, it is outputting FMANAGERSPODATAHOLD.mas and .ftm to my directory.
APP HOLD pdofin
TABLE FILE T_PROD_PO
PRINT
PO_AGE AS 'PO AGE (Days)'
PONUM AS 'PO NUMBER'
TOTALCOST/P12.2C AS 'OPEN PO,$ AMONT'
BY REGION
BY BSTGROUP
BY DIST
WHERE ( T_PROD_PO.STATUS NE 'CAN' ) AND (( GLDEBITACCT LIKE '4604600%' ) OR ( GLDEBITACCT CONTAINS '657004' OR '657006' )) AND (( ORDERDATE GE DT(2007-01-01 00:00:00) ) AND ( RECEIPTS EQ 'NONE' )) AND (( T_PROD_WORKORDER.STATUS NE 'CAN' OR 'CLOSE' ) AND ( T_PROD_PO.TOTALCOST NE .00 ));
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS FMANAGERSPODATAHOLD FORMAT ALPHA
Rob M.
Target Corporation
WF 7.1.4
July 25, 2008, 02:49 PM
Rob M.quote:
Originally posted by Rob M.:
quote:
Originally posted by GinnyJakes:
But back to your problem. You didn't post your first program. Here is an example using the CAR file. Please note that the string 'c758760' is a directory name on my server under the ibi/apps for the reporting server.
-* Program to hold the data.
APP HOLD C758760
TABLE FILE CAR
PRINT SALES DEALER_COST RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS CARHOLD FORMAT ALPHA
END
Now here is a program that uses the held data:
APP PREPENDPATH C758760
APP FI CARHOLD DISK C758760/carhold.ftm
TABLE FILE CARHOLD
SUM SALES DEALER_COST RETAIL_COST
BY COUNTRY
END
I am on a UNIX platform so my slashes go the other way. But if you take my examples and change the c758760 to a directory in your installation, you should be able to see the .mas and .ftm that are created.
Let me know how you do with this example.
Ok Ginny,
Here is what I put in my second file....
APP PREPENDPATH PDOFIN
APP FILE FMANAGERSPODATAHOLD DISK PDOFIN/FMANAGERSPODATAHOLD.ftm
But when I try to use that I get the following error when I try to save the fex file
"Error Parsing Report Request - >APP< APP PREPENDPATH PDOFIN"FYI here is the code for the first file, it seems to be working just fine, it is outputting FMANAGERSPODATAHOLD.mas and .ftm to my directory.
APP HOLD pdofin
TABLE FILE T_PROD_PO
PRINT
PO_AGE AS 'PO AGE (Days)'
PONUM AS 'PO NUMBER'
TOTALCOST/P12.2C AS 'OPEN PO,$ AMONT'
BY REGION
BY BSTGROUP
BY DIST
WHERE ( T_PROD_PO.STATUS NE 'CAN' ) AND (( GLDEBITACCT LIKE '4604600%' ) OR ( GLDEBITACCT CONTAINS '657004' OR '657006' OR '657007' )) AND (( ORDERDATE GE DT(2007-01-01 00:00:00) ) AND ( RECEIPTS EQ 'NONE' )) AND (( T_PROD_WORKORDER.STATUS NE 'CAN' OR 'CLOSE' ) AND ( T_PROD_PO.TOTALCOST NE .00 ));
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS FMANAGERSPODATAHOLD FORMAT ALPHA
Rob M.
Target Corporation
WF 7.1.4
July 25, 2008, 03:05 PM
Rob M.Wow looks like I got it to work!
Wow it is super fast too!!!!!!
THANK YOU Ginny and Tom!
Rob M.
Target Corporation
WF 7.1.4
July 25, 2008, 03:32 PM
GinnyJakesSo what did you do to make it work?
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
July 25, 2008, 03:57 PM
Rob M.Ginny: this is the final code for my two files....
FILE 1
APP HOLD pdofin
TABLE FILE T_PROD_PO
PRINT
PO_AGE AS 'PO AGE (Days)'
PONUM AS 'PO NUMBER'
TOTALCOST/P12.2C AS 'OPEN PO,$ AMOUNT'
BY REGION
BY BSTGROUP
BY DIST
WHERE ( T_PROD_PO.STATUS NE 'CAN' ) AND (( GLDEBITACCT LIKE '4604600%' ) OR ( GLDEBITACCT CONTAINS '657004' OR '657006' OR '657007' OR '650124' OR '637000' OR '657008' OR '657009' OR '650132' OR '650130' OR '657011' OR '662000' OR '078000' OR '656000' OR '657001' OR '657003' OR '657010' OR '657020' OR '657030' OR '657040' OR '657050' OR '657000' )) AND (( ORDERDATE GE DT(2007-01-01 00:00:00) ) AND ( RECEIPTS EQ 'NONE' )) AND (( T_PROD_WORKORDER.STATUS NE 'CAN' OR 'CLOSE' ) AND ( T_PROD_PO.TOTALCOST NE .00 ));
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS FMANAGERSPODATAHOLD FORMAT ALPHA
FILE 2
APP PREPENDPATH PDOFIN
APP FI FMANAGERSPODATAHOLD DISK PDOFIN/FMANAGERSPODATAHOLD.ftm
TABLE FILE FMANAGERSPODATAHOLD
Ok now I am trying to set up my report caster job to execute FILE 1 every morning to refresh the data.
On the report format I am assuming I choose ALPHA and use the same file name as above
FMANAGERSPODATAHOLD.ftm right?
Then on the distribution tab where it says "Distribute Reports by:" what do I pick in the drop down menu? Choices are E-mail, FTP, Printer, Managed Reporting and Library.
I tried picking Managed Reporting and putting
PDOFIN in the folder name box but report caster gives me an error. I think it is because I don't do reporting through MRE, but rather I do the report building right in the app folder on the server in Dev Studio.
Rob M.
Target Corporation
WF 7.1.4
July 25, 2008, 04:01 PM
GinnyJakesSince you not actually going to be distributing a report, use email as the distribution type.
And you are going to execute a 'WebFOCUS Procedure'. That would be on the Task tab.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google