Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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: 156 | Registered: August 23, 2010Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
Thanks Martin, I will give it a go.


WebFOCUS 8105m
Windows, All Outputs
 
Posts: 156 | Registered: August 23, 2010Report This Post
Gold 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 (Prod); WF 8.2.06 (Dev)
 
Posts: 83 | Location: OK | Registered: November 19, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders