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 in Reportcaster Explorer to output the list of schedules(the schedules that are displayed in the right panel) into a printable report?


Roberto Trevino

Recommended Posts

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 weeks later...

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

image.thumb.png.88a02d76573de7f920f227d3f15777d7.png 

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

Link to comment
Share on other sites

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

image.thumb.png.a7bf323f8c71881ab7fa624527b130e0.png2 In the "Connect to Data" section, either locate the "TIBCO WebFOCUS Client" Adapter or click on the + symbol to locate it nd add it

image.thumb.png.84a4055c09e215f952af9f4fd23364cc.png3 Click + to add a new connection or double click on one of them (in my case CON01) to configure it

image.thumb.png.6480e866fc793e6da3046a360c8540cd.png4 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"

image.thumb.png.99b0c45b405339cfd17526dabc043f29.png7 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"

image.thumb.png.d79f4f37f57e387828199cb7a48c9a58.png8 Navigate to that folder and locate the procedures

image.thumb.png.5dd7c58c52df8e57dfba35240f3d05e2.png9 Navigate to sub folder wfcsampl to find the sample procedures on how to query from these master files.

image.thumb.png.bcfaeb6906544956baebaa8612d859bb.png10 Enjoy

Link to comment
Share on other sites

******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'

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

  • 4 weeks later...

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');

image.thumb.png.735ab1093547c7d9095a6b6db1f5484e.png 

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...