Extract Package name from search path of Cognos reports
The Audit package that cognos provides doesn't come with a data item that just lists out the package name, you will have extract it for QS and RS reports. PP is an exception .Here is the syntax used to extract package name from search path for RS and QS against Oracle, you can alter this for non-oracle databases with equivalent functions.
QS:
SUBSTR ([Audit Database].[COGIPF_EDITQUERY].[PACKAGE],position ( 'package[@name=', [Audit Database].[COGIPF_EDITQUERY].[PACKAGE] )+15,(position ( ']/model', [Audit Database].[COGIPF_EDITQUERY].[PACKAGE] )-1)-(position ( 'package[@name=', [Audit Database].[COGIPF_EDITQUERY].[PACKAGE] )+15))
RS:
SUBSTR ([Audit Database].[COGIPF_RUNREPORT].[PACKAGE],position ( 'package[@name=', [Audit Database].[COGIPF_RUNREPORT].[PACKAGE] )+15,(position ( ']/model', [Audit Database].[COGIPF_RUNREPORT].[PACKAGE] )-1)-(position ( 'package[@name=', [Audit Database].[COGIPF_RUNREPORT].[PACKAGE] )+15))
Folder Name:
QS:
SUBSTR ([Audit Database].[COGIPF_EDITQUERY].[PACKAGE] , instr([Audit Database].[COGIPF_EDITQUERY].[PACKAGE] ,'''',1,1)+1,instr([Audit Database].[COGIPF_EDITQUERY].[PACKAGE] ,'''',1,2)-instr([Audit Database].[COGIPF_EDITQUERY].[PACKAGE] ,'''',1,1)-1)
RS:
SUBSTR ([Audit Database].[COGIPF_RUNREPORT].[PACKAGE] , instr([Audit Database].[COGIPF_RUNREPORT].[PACKAGE] ,'''',1,1)+1,instr([Audit Database].[COGIPF_RUNREPORT].[PACKAGE] ,'''',1,2)-instr([Audit Database].[COGIPF_RUNREPORT].[PACKAGE] ,'''',1,1)-1)
QS:
SUBSTR ([Audit Database].[COGIPF_EDITQUERY].[PACKAGE],position ( 'package[@name=', [Audit Database].[COGIPF_EDITQUERY].[PACKAGE] )+15,(position ( ']/model', [Audit Database].[COGIPF_EDITQUERY].[PACKAGE] )-1)-(position ( 'package[@name=', [Audit Database].[COGIPF_EDITQUERY].[PACKAGE] )+15))
RS:
SUBSTR ([Audit Database].[COGIPF_RUNREPORT].[PACKAGE],position ( 'package[@name=', [Audit Database].[COGIPF_RUNREPORT].[PACKAGE] )+15,(position ( ']/model', [Audit Database].[COGIPF_RUNREPORT].[PACKAGE] )-1)-(position ( 'package[@name=', [Audit Database].[COGIPF_RUNREPORT].[PACKAGE] )+15))
Folder Name:
QS:
SUBSTR ([Audit Database].[COGIPF_EDITQUERY].[PACKAGE] , instr([Audit Database].[COGIPF_EDITQUERY].[PACKAGE] ,'''',1,1)+1,instr([Audit Database].[COGIPF_EDITQUERY].[PACKAGE] ,'''',1,2)-instr([Audit Database].[COGIPF_EDITQUERY].[PACKAGE] ,'''',1,1)-1)
RS:
SUBSTR ([Audit Database].[COGIPF_RUNREPORT].[PACKAGE] , instr([Audit Database].[COGIPF_RUNREPORT].[PACKAGE] ,'''',1,1)+1,instr([Audit Database].[COGIPF_RUNREPORT].[PACKAGE] ,'''',1,2)-instr([Audit Database].[COGIPF_RUNREPORT].[PACKAGE] ,'''',1,1)-1)
Any idea about how to do it for clean path name of the report i.e. Public Folder/Department/Store/Report/MyReport?
ReplyDeleteTry concatanating: Package RS||'/'||Folder RS expressions above that should give you Folder and package name, the same for report name too. I will try it tomorrow and let you know the exact syntax.
ReplyDeletethanks,
Santosh.
it should and it does not work across all databases.
ReplyDeletesubstr is a oracle function just like instr
This comment has been removed by a blog administrator.
ReplyDelete