Martin Yergeau Posted August 19, 2021 Share Posted August 19, 2021 You may have to adapt a little because if I remember well, the DB structure was a little different in 8105 But if your RC is stored in a SQL DB, the below will work to list all job sent to who and when SELECT DISTINCT LTRIM(RTRIM(T1.JOBDESC)) AS 'JOBDESC', LTRIM(RTRIM(T2.[MAILSUBJECT])) AS 'MAILSUBJECT' ,CASE WHEN T5.[iNTERVALTYPE] = 'D' THEN 'Daily' WHEN T5.[iNTERVALTYPE] = 'W' THEN 'Weekly' WHEN T5.[iNTERVALTYPE] = 'M' THEN 'Monthly' WHEN T5.[iNTERVALTYPE] = 'O' THEN 'On Request' ELSE T5.[iNTERVALTYPE] END AS 'FREQUENCY' ,CASE WHEN T5.[WEEKDAYS] = '1111111' THEN 'All 7 Days' WHEN T5.[WEEKDAYS] = '1111100' THEN 'Mon To Fri' WHEN T5.[WEEKDAYS] = '1000000' THEN 'Monday' WHEN T5.[WEEKDAYS] = '0100000' THEN 'Tuesday' WHEN T5.[WEEKDAYS] = '0010000' THEN 'Wednesday' WHEN T5.[WEEKDAYS] = '0001000' THEN 'Thursday' WHEN T5.[WEEKDAYS] = '0000100' THEN 'Friday' WHEN T5.[WEEKDAYS] = '0000010' THEN 'Saturday' WHEN T5.[WEEKDAYS] = '0000001' THEN 'Sunday' WHEN T5.[WEEKDAYS] = '0000000' THEN 'On Request' ELSE T5.[WEEKDAYS] END AS 'WEEKDAYS' ,T5.[sTARTTIME] ,CASE WHEN T2.[listTYPE] = 'S' THEN LOWER(REPLACE(T2.[DISTLIST], '; ', ';')) WHEN T2.[listTYPE] = 'R' THEN LOWER(REPLACE(T2.[DISTLIST], '; ', ';')) ELSE LOWER(REPLACE(T4.[DESTFN], '; ', ';')) END AS 'SEND_TO' ,CASE WHEN [listTYPE] = 'S' THEN 'Single Adr' WHEN [listTYPE] = 'D' THEN 'Disttribution List' WHEN [listTYPE] = 'R' THEN 'Dynamic Dist List' WHEN [listTYPE] = 'Q' THEN 'Library' ELSE [listTYPE] END AS 'LISTTYPE' ,T3.BOOKNAME ,ISNULL(T3.[DESCRIPTION], '') AS 'BOOK_DESC' ,CASE WHEN ISNULL(T3.[DESCRIPTION], '') LIKE '%Burst%' THEN T4.FLDVLU ELSE '' END AS 'BURST_BY' INTO #TMP_EXT FROM [dbo].[bOTSCHED] T1 LEFT OUTER JOIN [dbo].[bOTDIST] T2 ON T1.SCHEDULEID = T2.SCHEDULEID LEFT OUTER JOIN [dbo].[bOTADDR] T3 ON SUBSTRING(T2.DISTPATH, PATINDEX('%Schedules/%', DISTPATH) + 10, 100) LIKE T3.BOOKNAME OR SUBSTRING(T2.DISTPATH, PATINDEX('%Schedules/Yoann/Distribution/%', DISTPATH) + 29, 100) LIKE T3.BOOKNAME LEFT OUTER JOIN [dbo].[bOTDEST] T4 ON T3.[ADDRBOOKID] = T4.[ADDRBOOKID] INNER JOIN [dbo].[bOTSIT] T5 ON T1.[sCHEDULEID] = T5.[sCHEDULEID] WHERE T1.ACTIVE = 'Y' AND T2.ACTIVE = 1 AND T5.ACTIVE = 1 AND T2.[listTYPE] != 'Q' -- Q is the type assigned for HOLD schedules --AND T2.[listTYPE] IN ('D', 'S') --AND T1.JOBDESC LIKE '%Except%' --ORDER BY T1.JOBDESC SELECT JOBDESC, MAILSUBJECT, FREQUENCY, WEEKDAYS, STARTTIME, LISTTYPE, BOOK_DESC, BURST_BY, --BOOKNAME, CASE WHEN SUBSTRING(SEND_TO, LEN(SEND_TO), 1) = ';' THEN SUBSTRING(SEND_TO, 1, LEN(SEND_TO) -1) ELSE SEND_TO END AS 'SEND_TO' INTO #TMP_CLEAN FROM #TMP_EXT --WHERE SEND_TO > '' ;WITH SPLITRESULT AS ( SELECT JOBDESC, MAILSUBJECT, FREQUENCY, WEEKDAYS, STARTTIME, LISTTYPE, BOOK_DESC, BURST_BY, --BOOKNAME, [xml_val] = CAST('<t>' + REPLACE(SEND_TO, ';', '</t><t>') + '</t>' AS XML) FROM #TMP_CLEAN ) SELECT DISTINCT JOBDESC, MAILSUBJECT, FREQUENCY, WEEKDAYS, STARTTIME, LISTTYPE, BOOK_DESC, BURST_BY, --BOOKNAME, SEND_TO = col.value('.','VARCHAR(800)') FROM SPLITRESULT CROSS APPLY [xml_val].nodes('/t') CA(col) ORDER BY JOBDESC DROP TABLE #TMP_EXT DROP TABLE #TMP_CLEAN Link to comment Share on other sites More sharing options...
Jeremy Veselka Posted August 19, 2021 Author Share Posted August 19, 2021 Thanks MartinY i will give this a go. Link to comment Share on other sites More sharing options...
Jeremy Veselka Posted August 19, 2021 Author Share Posted August 19, 2021 Is there a way to export the total number of Report Caster Jobs to Excel I have an auditor asking for the number of reports we have running in Webfocus, which are hundreds but not sure how to get a good count for RC. Thanks JV Link to comment Share on other sites More sharing options...
Jeremy Veselka Posted August 20, 2021 Author Share Posted August 20, 2021 Thanks again Martin, I didnt even think about the WF tables, I was just thinking there was something I could do from Report caster. I got what I needed using these two tables. JOIN BOTSCHED.BOTSCHED.SCHEDULEID IN BOTSCHED TO BOTSIT.BOTSIT.SCHEDULEID IN BOTSIT AS J0 END Link to comment Share on other sites More sharing options...
David Briars Posted August 20, 2021 Share Posted August 20, 2021 jeremy.veselka: I was just thinking there was something I could do from Report caster On 1-Apr-2020 I created a New Feature Request that ReportCaster should provide a report consisting of 'Schedule Name, Schedule Tasks,". I even provided a report design in the NFR. The NFR is Case Number 02029236. If anyone creates a similar NFR, please feel free to reference this case number. Thanks all. Link to comment Share on other sites More sharing options...
Martin Yergeau Posted August 20, 2021 Share Posted August 20, 2021 Yeah, the sample code that I provide is more complex and gives more info than what you were looking for. I always use that one and then adapt the last select to display what I need either by removing columns or adding WHERE clauses Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now