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. AnonymousMarch 2, 2011 at 2:23 PM

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

    ReplyDelete
  2. Santosh KanthetyMarch 2, 2011 at 2:47 PM

    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. AnonymousMarch 1, 2012 at 1:42 AM

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

    ReplyDelete
  4. AnonymousMarch 27, 2012 at 2:34 AM

    This comment has been removed by a blog administrator.

    ReplyDelete

Post a Comment

Popular posts from this blog

Query Cognos 8/10 Content Store

I came across these queries that can give you good insight on the data and maintenance of Content Store in Cognos 8/10. You can also leverage the CMTools utility that comes with cognos install. ..\Cognos\bin\utilities\cm\CMTools\CMTools.exe select count(*) as "total number of objects" from CMOBJECTS; select c.NAME as "class name", count(*) as "number of objects" from CMOBJECTS o, CMCLASSES cgroup by o.CLASSID, c.CLASSID, C.NAME having c.CLASSID=o.CLASSID order by c.NAME ; select c.NAME as "class name", count(*) as "number of secured objects" from CMOBJECTS o, CMCLASSES c, CMPOLICIES pwhere o.CMID=p.CMID and p.POLICIES is not null group by o.CLASSID, c.CLASSID, C.NAME having c.CLASSID=o.CLASSID order by c.NAME ; select count(*) as "Total number of outputs", SUM(cast(coalesce(DATALENGTH(DATAPROP),0) as bigint)) as "Total size of outputs" from CMDATA where DATAPROP is not NULL; select
Read more

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

I came across this slick KB article on how to get the list of active sessions/users currently on any cognos environment and implemented it. https://www-304.ibm.com/support/docview.wss?uid=swg21385278 After I got this to work the servlet that runs only retrieves the CAMID and the session start time. I started looking around in the content store tables and was able to successfully  retrieve the CAMID and the associated  LDAP ID/User ID. I made this a query subject in the Audit model and joined it to the security groups table and created a report that will give me User ID, CAMID, AD Group, Name , Email. Query : SELECT  distinct ldap_id,     last_name,     first_name,     email,     objid as CAMID     FROM            (SELECT c33.cmid user_id, UPPER (c33.NAME) ldap_id,                   c1.surname last_name, c1.givenname first_name,                   c1.email email, c1.objid              FROM cmobjprops1 c1 LEFT OUTER JOIN cmobjprops33 c33                   ON c33.cmid = c1.cmid          
Read more

Cognos Cross-tab Report Studio Report Ungroup Items to display as list on every line

I recently had a requirement where the user wanted data from an existing Crosstab report to repeat every line so he can use the output in excel for comparison/analysis. It must be an old trick for most of us as I see so many posts around but none of them are straight forward and are hard to follow so i thought why not log it here, besides its only been 10 months since I shared anything. Sorry guys I'm in support right now so no time to explore new tips and tricks or maybe i'm being lazy ;) By default Crosstab groups columns nested based on the position on the cross tab, the trick here is to identify your lowest and highest level of data granularity. Lets pick the below crosstab as an example with PRODUCT, DESCRIPTION, LOCATION as nested dimension columns and default measure of SALES $ across MONTHS.                   Measure: Sales $      MONTH 1 | MONTH 2 .................................... Product | Description | Location |       $10             $20 Cognos groups PRO
Read more