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)

Comments

  1. Any idea about how to do it for clean path name of the report i.e. Public Folder/Department/Store/Report/MyReport?

    ReplyDelete
  2. Try 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.
    thanks,
    Santosh.

    ReplyDelete
  3. it should and it does not work across all databases.
    substr is a oracle function just like instr

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete

Post a Comment

Popular posts from this blog

Query Cognos 8/10 Content Store

Cognos 8/10 CAMID-User ID/LDAP ID report

How about an Online help forum?