Is there a way in Reportcaster Explorer to output the list of schedules(the schedules that are displayed in the right panel) into a printable report?

[Roberto Trevino]

Is there a way in Reportcaster Explorer to output the list of schedules(the schedules that are displayed in the right panel) into a printable report?

[Martin Yergeau]

Directly from RC, no.

However, you can use a SQL query to extract information directly from the Repository.

Here is a code sample that I use to figure which active schedule are distributed when, to who, from which distribution list and using which distribution parameters.

You may have to adapt for your needs.

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 [WFReposProd].[dbo].[bOTSCHED] T1 LEFT OUTER JOIN [WFRepos].[dbo].[bOTDIST] T2 ON T1.SCHEDULEID = T2.SCHEDULEID LEFT OUTER JOIN [WFReposProd].[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 [WFReposProd].[dbo].[bOTDEST] T4 ON T3.[ADDRBOOKID] = T4.[ADDRBOOKID] LEFT OUTER JOIN [WFReposProd].[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') 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_CLEANFROM #TMP_EXT ;WITH SPLITRESULTAS( 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 SPLITRESULTCROSS APPLY [xml_val].nodes('/t') CA(col)WHERE col.value('.', 'VARCHAR(800)') NOT LIKE 'support%'--and col.value('.', 'VARCHAR(800)') LIKE '%preston%'--and JOBDESC like '%Sales%'--and BOOK_DESC like '%By Rep%'ORDER BY JOBDESC DROP TABLE #TMP_EXTDROP TABLE #TMP_CLEAN

[Ron Moore]

Hi Roberto. There is the option to write your own reports and with that write the output to a printable format. Take a look at the ReportCaster Guide, appendix B, WebFOCUS Repository Tables for ReportCaster. There you will find a description on the underlying tables

[David Briars]

Hi Roberto,

I created a New Feature Request for 'ReportCaster Reporting' a couple of years ago.

Maybe if you get a chance, create a NFR and ask that it be added to mine?

I had put the request out on FocalPoint as well.

 

-Dave

[Roberto Trevino]

I will look into it.(edit: Just added it the Ideas area, its under the More section on the main menu on top of the communities page)

I am currently using a word doc 'template' to record the settings of the schedule. At least I have the information backed up in case something happens to the schedule.

[David Briars]

Thanks for creating an entry in the 'Ideas' area.

I've voted +1 for the idea just now.

-Dave

[Dirk Kuerbig]

Hello everyone,

WebFOCUS provides a Reporting Server Adapter, called "TIBCO WebFOCUS Client" which can be configured, and provides direct access to the WebFOCUS Client Repository content.

Master files and sample procedures, to access list of WebFOCUS users, list of schedules, ...

Once configured, the master file you will want to look at is called

"get_schedulelist.mas"

the sub folder "wfcsampl" contains a corresponding sample procedure

"get_schedulelist.fex"

Please feel free to create a copy of the sample procedure, and make changes so that it fits your purpose.

In the simplest form, please change the line command pointing to the folder you want to report against, setting the variable &IBIRS_PATH

e.g.

-SET &IBIRS_PATH = 'IBFS:/WFC/Repository/IBUG_Fall_2020';     

Then run the procedure, reformat to PDF, HTML, Excel, and format you need

Here is my sample output

 

You can build more sophisticated reports with parameters / traversing of Repository hierarchy, ..., and present them for business users / administrators to consume.

Client Repository synonyms are invaluable for administrators and users who need to understand content of the WebFOCUS Repository.

Enjoy

[Roberto Trevino]

Where do I can I download the Reporting Server Adapter - "TIBCO WebFOCUS Client" ?

[Dirk Kuerbig]

The Adapter is one of the WebFOCUS Reporting Server Adapter types you can configure.

Similar to Oracle or SQL Server.

Here are the steps on my WebFOCUS 9.02 environment, and they are very similar in other releases:

1. Connect to the WebFOCUS Reporting Server, e.g. http://localhost:8121 (or in the WebFOCUS Hub Click on "Get Data" button

2 In the "Connect to Data" section, either locate the "TIBCO WebFOCUS Client" Adapter or click on the + symbol to locate it nd add it

3 Click + to add a new connection or double click on one of them (in my case CON01) to configure it

4 The question marks in the input boxes will give you some help, but the screenshot should give you the idea on what to enter

5 Click on "Configure" to configure

6 Right mouse click on CON01 and select "Show DBMS Objects"

7 Select folder where the new master files and sample procedures sub folder is supposed to be created and click on button to "Create Synonym(s) and Examples"

8 Navigate to that folder and locate the procedures

9 Navigate to sub folder wfcsampl to find the sample procedures on how to query from these master files.

10 Enjoy

[Roberto Trevino]

******Update on Error Message at bottom of reply.

I made a copy of the get_schedulelist.fex and modified the &IBIRS_PATH to point to a folder that has a few schedules in it.

I made no other changes.

When I ran it I got an two FOC Error's. Can you point me in the direction to sort out the error?

Error Message:

(FOC44540) Web service request received error. Response Status : 403, URL: https://lvwfocus-prod2.tamiu.edu:8443/ibi_apps/rs

(FOC44545) HTTP Response :

<!DOCTYPE html>

<html lang="en-US">

<HEAD><!--beginject--><script type='text/javascript'>

window.ibiLog = { 'level':'ERROR', 'url':'/ibi_apps/WFClientTrace', 'context':'/ibi_apps', 'servlet'

******************

I have gotten that message to disappear but now I am getting this error message:

The Value of this Variable is Invalid! 'IBIRS_PATH'

[Debra Waybright]

I use SQL to hit the repository tables to get lists of schedules. Here's how I list schedules' tasks:

select s.JOBDESC, t.ASVALUE, t.EXECID, t.PRERPC1 as pre_process_procedure_1, t.PRERPC2 as pre_process_procedure2

, t.TASKNAME, COALESCE(t.TASKOBJPATH, t.TASKOBJ) as TaskObj

 FROM [WFRep2_Prd2].[dbo].[bOTTASK] t

 INNER JOIN  [WFRep2_Prd2].[dbo].[bOTSCHED] s

 ON t.[PACKETID] = s.[PACKETID]

 WHERE t.ACTIVE = 1

There's another posting out there somewhere which has SQL to list schedules with frequency.

[SATHEESH B]

I am working on the rest adapter method. use the bot table . you may need convert the milliseconds to get the next runtime . Also check for daylight savings for offset the values.

NOW_DTTM/HYYMDIA WITH NEXTRUNTIME=HGETC(8,'HYYMDIA');

NOW_TIME/D20c=HTIME(8,NOW_DTTM,'D20c');

RC_BASEDATE/YYMD WITH NEXTRUNTIME='19700101';

RC_BASEDTTM/HYYMDIA=HDTTM(RC_BASEDATE,8,'HYYMDIA');

NRT_NUMERIC/D32c=EDIT(NEXTRUNTIME);

NRT_DTTM/HDMYYSA=HADD(RC_BASEDTTM,'MILLISECOND',NRT_NUMERIC,8,'HMDYYIA');

NRT_TIME/D20c=HTIME(8,NRT_DTTM,'D20c');

NEXT_RUN_DTTM/HMDYYIA=HADD(NRT_DTTM, 'hour', &DSTOFFSET.EVAL, 8, 'HYYMDIA');

LASTRUN_DT/YYMD=HDATE(LASTEXTIME, 'YYMD');

NEXTRUN_DT/YYMD=HDATE(NEXT_RUN_DTTM , 'YYMD');

NOW_DT/YYMD=HDATE(NOW_DTTM , 'YYMD');