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.
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.JOBDESCThis message has been edited. Last edited by: FP Mod Chuck,
Windows, All Outputs
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,
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.
Windows, All Outputs
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
|Powered by Social Strata|