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] Search Distribution List in Report Caster
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Search Distribution List in Report Caster
 Login/Join
 
Platinum Member
posted
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
 
Posts: 122 | Registered: August 23, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 1990 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thanks Martin, I will give it a go.


WebFOCUS 8105m
Windows, All Outputs
 
Posts: 122 | Registered: August 23, 2010Reply With QuoteReport This Post
Silver Member
posted Hide Post
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 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 48 | Location: OK | Registered: November 19, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Search Distribution List in Report Caster

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