Focal Point
[SOLVED] Search Distribution List in Report Caster

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7067020196

January 08, 2019, 11:12 AM
Jveselka
[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
JV

This 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



WF 8.0.08 (Prod); WF 8.2.06 (Dev)