Is there a way to export the total number of Report Caster J...

[Martin Yergeau]

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

[Jeremy Veselka]

Thanks MartinY i will give this a go.

[Jeremy Veselka]

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

[Jeremy Veselka]

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

[David Briars]

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.

[Martin Yergeau]

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