Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] ReportCaster - how to identify schedules missing access lists

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] ReportCaster - how to identify schedules missing access lists
 Login/Join
 
Member
posted
We're having a problem where one or more Report Caster access lists may have been accidently deleted. We're tying to identify schedules that should have access lists but don't.

If anyone is aware of the database field containing the 'private' indicator, it would be very helpful. We have thousands of schedules and many are 'private' so we need to filter the 'private' access option out of our results. Our results should return all non-private, Library Report distributions that do not have an access list.

Here's the basic query before any filtering. I have not been able to identify the 'Private' indicator.

SELECT *
--BOTACCES.ACCESSNAME,BOTACCES.CASTER_USER, BOTSCHED.JOBDESC
FROM BOTSCHED
LEFT JOIN BOTCAT ON
BOTCAT.SCHEDULEID = BOTSCHED.SCHEDULEID
LEFT JOIN BOTLIST ON
BOTLIST.ACCESSID = BOTCAT.ACCESSNAME
LEFT JOIN BOTACCES ON
BOTACCES.ACCESSID = BOTLIST.ACCESSID
LEFT JOIN BOTDIST ON
BOTDIST.SCHEDULEID = BOTSCHED.SCHEDULEID
ORDER BY BOTACCES.ACCESSNAME, BOTSCHED.JOBDESC

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 14 | Registered: July 03, 2012Report This Post
Master
posted Hide Post
When you say "need to filter the 'private' access option out of our results" do you mean that you are looking for all published schedules?

If so, maybe this SQL can help.
  
--Recursive CTE
WITH RESOURCES_CTE
     AS (SELECT ur.ID
              , ur.NAME                  'OBJNAME'
              , ur.OWNERID
              , ur.OWNERTYPE
              , ur.PARENTID
              , ur.SHARES
              , CAST('' AS VARCHAR(MAX)) 'PRT_PATH'
              , CAST('' AS VARCHAR(MAX)) 'FULL_PATH'
         FROM UOA_RESOURCES ur
         WHERE ur.PARENTID=-1
         UNION ALL
         SELECT ur.ID
              , ur.NAME
              , ur.OWNERID
              , ur.OWNERTYPE
              , ur.PARENTID
              , ur.SHARES
              , CAST(rc.FULL_PATH AS VARCHAR(MAX))
              , CAST(rc.FULL_PATH + '/' + ur.NAME AS VARCHAR(MAX))
         FROM UOA_RESOURCES ur
              INNER JOIN RESOURCES_CTE rc
                  ON ur.PARENTID=rc.ID
         WHERE ur.PARENTID!=-1)

     --Main Query
     SELECT wr.PRT_PATH                                        'parentPath'
          , wr.OBJNAME                                         'fileName'
          , wi.DATATYPE                                        'fileType'
          , wn.OBJDESC                                         'title'
          , wr.CLASSNAME
          , IIF(u.DESCRIPTION IS NULL
                AND NOT wr.PRT_PATH LIKE '/WFC%/~%', 'Y', 'N') 'published'
          , ISNULL(rc.SHARES, '')                              'shared'
          , ISNULL(u.DESCRIPTION, '')                          'owner'
     FROM WF_REPOSOBJ wr
          LEFT JOIN WF_ITEM wi
              ON wr.HANDLE=wi.HANDLE
          LEFT JOIN WF_NLSOBJ wn
              ON wr.HANDLE=wn.OBJ_HANDLE
          LEFT JOIN RESOURCES_CTE rc
              ON rc.OBJNAME=wr.OBJNAME
                 AND rc.PRT_PATH=wr.PRT_PATH
          LEFT JOIN UOA_USERS u
              ON rc.OWNERID=u.ID
     WHERE wr.CLASSNAME='WfItem'
           AND wr.PRT_PATH LIKE '/WFC/Repository/%'
           AND wi.DATATYPE='sch'
     ORDER BY wr.PRT_PATH
            , wi.DATATYPE
            , wr.OBJNAME;

This message has been edited. Last edited by: Hallway,


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Member
posted Hide Post
Thanks for your reply Hallway. We're not looking for published schedules. Too bad screen shots can't be posted here ... 'Private' is a radio button in 'Access Options' for 'Report Library' distribution. The two Access Options are 'Private' and 'Shared with'. 'Shared with' requires an access list. 'Private' does not require and access list.


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 14 | Registered: July 03, 2012Report This Post
Master
posted Hide Post
Gotcha. We've never used that feature.

I did find this in the docs that show the WebFOCUS Repository Tables for ReportCaster: https://webfocusinfocenter.inf..._reposreports150.htm


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Expert
posted Hide Post
quote:
Too bad screen shots can't be posted here ...
They can... Just need to post them to a site where all, or at least most, of us can get to.

Like This:
See you at Summit 2020
And here on Focal Point!




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] ReportCaster - how to identify schedules missing access lists

Copyright © 1996-2020 Information Builders