[SOLVED] Search Distribution List in Report Caster
WF 8.1.0.5m Gen 172
Is there a way to search all distribution list in Report Caster for a single email address, all at one time?
Thanks JVThis message has been edited. Last edited by: FP Mod Chuck,
WebFOCUS 8105m Windows, All Outputs
January 08, 2019, 12:17 PM
MartinY
You will need to write SQL code that will access RC repository using DBMS
Under 8105 it's something such as this
SELECT S.JOBDESC
,T0.[MAILSUBJECT]
,T0.[LISTTYPE]
,T0.[MAILFROM]
,T0.[MAILUSER]
,[DISTLIST]
,CASE WHEN T0.[LISTTYPE] = 'S' THEN T0.[DISTLIST] ELSE T1.[DESTFN] END AS 'SEND_TO'
FROM [YourRC_DB_Name].[dbo].[BOTSCHED] S
INNER JOIN [YourRC_DB_Name].[dbo].[BOTDIST] T0
ON S.SCHEDULEID = T0.SCHEDULEID
LEFT OUTER JOIN [YourRC_DB_Name].[dbo].[BOTDEST] T1
ON T1.ADDRBOOKID = T0.[DISTLIST]
WHERE
-- s.ACTIVE = 'Y' and
T0.[DISTLIST] like '%jacobs%' or T1.[DESTFN] like '%jacobs%'
Or you can create a FOCUS report that will access the same tables as in the SQL code above. If you're not using SQL pass through, you will have to create the respective master files
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
January 09, 2019, 03:12 PM
Jveselka
Thanks Martin, I will give it a go.
WebFOCUS 8105m Windows, All Outputs
January 10, 2019, 10:24 AM
evan.brown
I got tired of people always asking me what all reports a person gets distributed or who gets distributed a certain report, so I made a little fex to do it. I have a simple launch screen where the user selects the radio button indicating if they want to search email addresses or report name. They can enter a partial name for either. One thing to note, we have it set up where we only have one email address in DESTFN or else we set it up with an address distribution list. That's when EMAIL3 is "ERROR".
-SET &NAME=IF &TYP EQ 'E' THEN 'REPORT_NM' ELSE 'EMAIL';
-SET &BY=IF &TYP EQ 'E' THEN 'EMAIL' ELSE 'REPORT_NM';
-SET &AS=IF &TYP EQ 'E' THEN ' AS ''Email Distribution''' ELSE ' AS ''Report Name''';
-SET &SEARCHFMT=UPCASE(&SEARCH.LENGTH, &SEARCH, 'A&SEARCH.LENGTH');
-SET &SEARCHFOC='%'||&SEARCHFMT||'%';
-SET &WHERE=IF &SEARCHFOC EQ '%ALL%' THEN '-*' ELSE 'WHERE &BY.EVAL LIKE ''&SEARCHFOC.EVAL''';
-*
JOIN LEFT_OUTER SCHEDULEID IN BOTSCHED TO ALL SCHEDULEID IN BOTDIST AS A.
JOIN LEFT_OUTER DISTLIST IN BOTSCHED TO ALL ADDRBOOKID IN BOTDEST AS B.
-*
TABLE FILE BOTSCHED
PRINT DESTFN DISTLIST
BY JOBDESC
ON TABLE HOLD
END
-*
JOIN CLEAR *
-*
DEFINE FILE HOLD
EMAIL1/A800=IF DESTFN EQ ' ' THEN DISTLIST ELSE DESTFN;
EMAIL2/A800=STRIP(1,EMAIL1,' ','A800');
EMAIL3/A800=UPCASE(800,EMAIL1,'A800');
EMAIL/A800=IF EMAIL3 LIKE 'U$$$$$%' OR '%;%' THEN 'ERROR' ELSE
IF EMAIL3 LIKE '%/%' THEN 'DYNAMIC DISTRIBUTION' ELSE EMAIL3;
REPORT_NM/A128=UPCASE(128,JOBDESC,'A128');
END
-*
TABLE FILE HOLD
PRINT &NAME &AS
BY &BY &AS
&WHERE
ON TABLE SET EMPTYREPORT ON
ON TABLE SET PAGE-NUM TOP
ON TABLE SET STYLE *
TYPE=REPORT, FONT='CALIBRI', SIZE=10,$
TYPE=TITLE, STYLE=BOLD, SIZE=10, JUSTIFY=CENTER, BACKCOLOR=SILVER,$
TYPE=HEADING, STYLE=BOLD, SIZE=10, BACKCOLOR=SILVER,$
END