Hi Everyone,
My requirement is to get list of all the fex and their master files .Currently i have these two tables wf_reposobj and sysfiles.

I appreciate anyone have suggestion to create fex and mas file in one report.

This message has been edited. Last edited by: FP Mod Chuck,

Where are your fexes ?, on the reporting server or in the repository.

On the reporting server, you can run an Impact Analysis against a fex or app folder.

They ask me too figure out alternate way other than impact analysis.

Here is an option, not pretty, but possible.

Assuming your fexes are in the repository

Export your fexes to the reporting server place them in an app folder and run impact analysis.

Or, you can write something, if you have access to the repository from the reporting server, to pull each fex to the temp folder, then app amp it, and run the following code (untested). This is the command run from impact analysis.

TABLE FILE _EDAHOME/CATALOG/SYSIMP
PRINT CFILE CAPPLICATION CEXTENSION CTYPE CIMAGE CDESCRIPTION CSERVER RAPPLICATION RFILE RADAPTER RTABLE RPRIOR_TO_LOAD RLOAD_TYPE RLINENUM RUSAGE RDESCRIPTION
WHERE RPT_TYPE EQ 'MFD' AND CAPPLICATION EQ '{app folder}' AND RAPPLICATION EQ '{app folder}' OR ''
END

If they are on your reporting server the following is a dirty way that I achieve getting a list but not the associations - usually to make a dynamic use statement dependent on the contents of the folder

Will eave the tinkering to your creativity.


!ls -l /w81dev/ibi/apps/*/*.mas> /w81dev/ibi/apps/__paul/temp.ftm

FILEDEF TEMP DISK /w81dev/ibi/apps/__paul/temp.ftm
FILEDEF MASTER DISK temp.mas
-RUN

-WRITE MASTER FILENAME=TEMP, SUFFIX=FIX , $
-WRITE MASTER SEGNAME=SEG01, $
-WRITE MASTER FIELDNAME=FIELD1, ALIAS=E01, USAGE=A255, ACTUAL=A255, $
-RUN

CHECK FILE TEMP
SET PAGE-NUM = OFF

SET MESSAGE = OFF
DEFINE FILE TEMP
MASTER_NAME/A50=GETTOK(FIELD1,255,-1,'/',50,'A50');
END


TABLE FILE TEMP
PRINT MASTER_NAME
BY MASTER_NAME NOPRINT
END

I like to use this. I believe I got this somewhere on FocalPoint. It works if you are looking for fex and mas on the reporting server.

  
-* list files in all app folders
-SET &ECHO=ON;

-* determine list of app folders
APP LIST HOLD
-RUN

-* allocate hold file that will contain focappq info for all app folder * important: append
FILEDEF HOLDFOCAPPQ DISK holdfocappq.ftm (APPEND
-RUN

-* read an app folder name into a variable
-READFILE FOCAPPL
-RUN

-* process each app folder
-REPEAT LOOP_END WHILE &IORETURN EQ 0;

-* create a list of all files in the specified app folder
APP QUERY '&APPNAME' HOLD
-RUN

-* add the list of files of the specified app folder to the list of files for all folders (appended)
TABLE FILE FOCAPPQ
PRINT *
ON TABLE HOLD AS HOLDFOCAPPQ FORMAT ALPHA
END
-RUN

-* read an app folder name into a variable
-READFILE FOCAPPL
-LOOP_END

-* create a report
TABLE FILE HOLDFOCAPPQ
PRINT FILENAME
BY APPNAME
WHERE FILENAME LIKE '%.fex' OR FILENAME LIKE '%.mas'
END
-RUN

*Indeed this was on FocalPoint:
http://forums.informationbuild...1057331/m/7657005276

This message has been edited. Last edited by: evan.brown,

We have a report that does the reverse - shows all reports that use a given MAS file - for when we update a master file.

We loop through SYSFILES to look for updated MAS files in the last two week sprint then look for reports that use these MAS files.

Not perfect but a good enough sanity check - has rare false positives.

Hopefully you can reverse engineer to your needs

Cheers

Stu

  
FILEDEF HOLDFILE DISK HOLDFILE.FOCUS (APPEND

-SET &FILENAME='';

-SET &ENV='';
-SET &DATE1='';
-SET &PHNAME='';
SET DEFCENT=2000

DEFINE FILE SYSFILES
ENV/A100V=TOKEN(PHNAME,'/',1);
DATE1/DMYY =DATECVT(EDIT(DATE,'99$99$99'),'A6DMY','DMYY');
CURRDATE/DMYY= DT_CURRENT_DATE();
DATE_TWO_WEEKS_AGO/DMYY = DATEADD(CURRDATE, 'WD', -14)
END

TABLE FILE SYSFILES
PRINT
FILENAME
PHNAME
DATE1
DATE_TWO_WEEKS_AGO
BY ENV
WHERE ENV IN ('apiprod','attribution','baseapp','charm','customer_dm','dashboards','datavault','dqs','emp_seg','global','instructions','marketing','mds','middle','ods','ins_data');
WHERE DATE1 GE DATE_TWO_WEEKS_AGO;
ON TABLE HOLD AS DA_LIST FORMAT ALPHA
-RUN

-SET &RECS = &LINES;

-*============================Pass MasterFile as a parameter to return FEX==============
-*-SET &ECHO=ALL;

-REPEAT MYLOOP FOR 1 TO &RECS.EVAL

-READFILE DA_LIST
-SET &FILENAME = TRIM_(BOTH, ' ', '&FILENAME.EVAL');
-SET &ENV = TRIM_(BOTH, ' ', '&ENV.EVAL');
-SET &DATE1='&DATE1.EVAL';
-SET &PHNAME='&PHNAME.EVAL';
-TYPE &FILENAME &ENV &DATE1 &PHNAME

ENGINE SQLMSS SET DEFAULT_CONNECTION WFREPOS

SQL SQLMSS PREPARE SQLLOOP FOR
SELECT T2.APPNAME,T2.OBJNAME,T2.PRT_PATH,T2.LASTMODON,T2.LASTMODBY,T1.BCONTENT
FROM WF_CONTENT_REVS T1 INNER JOIN WF_REPOSOBJ T2 ON T1.OBJ_HANDLE = T2.HANDLE
WHERE
(UPPER(T2.PRT_PATH) NOT LIKE '%ARCHIVE%') AND T2.OBJTYPE ='109' AND (UPPER(LEFT(T2.PRT_PATH,16)) = '/WFC/REPOSITORY/') AND
(LOWER(CAST(T1.BCONTENT AS VARBINARY(MAX))) LIKE '%file &FILENAME.EVAL%'  OR LOWER(CAST(T1.BCONTENT AS VARBINARY(MAX))) LIKE '%in &FILENAME.EVAL%');
END

DEFINE FILE SQLLOOP
SHORT_PATH/A100V = GETTOK(PRT_PATH, 104, -1, '/', 25, SHORT_PATH);
LASTMODIFIEDON/DMYY=LASTMODON;
END

TABLE FILE SQLLOOP
PRINT
	 COMPUTE  ENV/A50V = '&ENV';
	 COMPUTE MASTERFILE/A50V='&FILENAME';
	 COMPUTE MASTERFILEMODDATE/DMYY='&DATE1';
     COMPUTE PHNAME/A50V='&PHNAME';
	  LASTMODBY
	  LASTMODIFIEDON

	 BY PRT_PATH
	 BY SHORT_PATH
     BY OBJNAME
-*	 WHERE LASTMODIFIEDON GE '&START_DATE';
ON TABLE HOLD AS HOLDFILE
-*ON TABLE PCHOLD FORMAT HTML
-RUN

-MYLOOP

TABLE FILE HOLDFILE
PRINT
OBJNAME AS 'Reports Affected'
PRT_PATH AS 'Report Location'
LASTMODIFIEDON AS 'Report LastModified'
BY HIGHEST MASTERFILEMODDATE AS 'MasterFile ChangedDate'
BY MASTERFILE AS 'MasterFile'
BY PHNAME AS 'MasterFile Location'
-*BY MASTERFILEMODDATE AS 'MasterFile Changed Date'
BY HIGHEST LASTMODIFIEDON NOPRINT
ON TABLE HEADING
"Master files changed in last two weeks "
"Server: &SERVERNAME<+0> "
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$

TYPE=DATA,
     COLUMN=N1,
     STYLE=BOLD,
$
ENDSTYLE
END
-EXIT