Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


Jeremy Veselka

Recommended Posts

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...