Blog Pro de Jean-Baptiste HEREN

Notes d'un consultant Freelance en Informatique

To content | To menu | To search

Extract Cognos reports & packages list with path

Posted on Monday 28 March 2011, 22:33 - Décisionnel - Permalink

When you want to make a documentation on Cognos (or anything), it's hard to keep it updated...

One of my recent projects has been to buid an automatic documentation on Cognos, showing dependencies between packages, reports and even source tables or ETL packages. Here I share with you, some SQL scripts extracting information from the Cognos Content Store. Hope it will be helpful.

[SQL]
/* Author: Jean-Baptiste HEREN - 2010-07-06      */
/* Descr: extract Report model XML from database */
select names.name, specs.spec
from CMOBJNAMES names
     inner join CMOBJPROPS7 specs 
     on names.CMID = specs.CMID 
where names.name LIKE '%Audit%'

/* Author: Jean-Baptiste HEREN - 2010-07-06      */
/* Descr: extract packages list */
SELECT DISTINCT
       cmobjects.cmid,
       cmobjnames.name as package_name
FROM   cmrefnoord1
       INNER JOIN cmobjects
         ON cmrefnoord1.refcmid = cmobjects.cmid
       INNER JOIN cmobjnames
         ON cmobjects.cmid = cmobjnames.cmid
WHERE  cmrefnoord1.propid = 31
       AND cmobjnames.localeid IN ( '24', '100')

/* Author: Jean-Baptiste HEREN - 2011-03-15 */
/* Descr: - Report names, full Paths & related package name from cognoscs database */
/* CMOBJPROPS25 - Stores the data regarding the objects deployed, like the deployed folder, the reports, the number of folders present in the deployment archive, etc*/
SELECT DISTINCT rank() OVER(ORDER BY package_name,NAME,max(Path) ASC) as rank, NAME as report_name, package_name, RTRIM(max(Path)) as path, cast(storeID as nvarchar) as store_id
FROM (
SELECT A.CMID, L1.mapdlocaleid, L1.locale, L1.TYPE ,L1.NAME /*, L9.NAME, L8.NAME, L7.NAME, L6.NAME , L5.NAME, L4.NAME  , L3.NAME  , L2.NAME*/
, LTRIM(SUBSTRING(
  CASE WHEN isnull(L10.NAME,'') = '/' THEN ' ' ELSE isnull(L10.NAME,'') END + CASE WHEN L10.NAME = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L9.NAME,'')  = '/' THEN ' ' ELSE isnull(L9.NAME,'')  END + CASE WHEN L9.NAME  = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L8.NAME,'')  = '/' THEN ' ' ELSE isnull(L8.NAME,'')  END + CASE WHEN L8.NAME  = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L7.NAME,'')  = '/' THEN ' ' ELSE isnull(L7.NAME,'')  END + CASE WHEN L7.NAME  = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L6.NAME,'')  = '/' THEN ' ' ELSE isnull(L6.NAME,'')  END + CASE WHEN L6.NAME  = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L5.NAME,'')  = '/' THEN ' ' ELSE isnull(L5.NAME,'')  END + CASE WHEN L5.NAME  = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L4.NAME,'')  = '/' THEN ' ' ELSE isnull(L4.NAME,'')  END + CASE WHEN L4.NAME  = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L3.NAME,'')  = '/' THEN ' ' ELSE isnull(L3.NAME,'')  END + CASE WHEN L3.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L2.NAME ,'') + CASE WHEN L2.NAME  = '/' THEN '' ELSE '/' END
--+ isnull(L1.NAME ,'')
  , CHARINDEX('Public',
  isnull(L10.NAME,'') + CASE WHEN L10.NAME = '/' THEN '' ELSE '/' END
+ isnull(L9.NAME ,'') + CASE WHEN L9.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L8.NAME ,'') + CASE WHEN L8.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L7.NAME ,'') + CASE WHEN L7.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L6.NAME ,'') + CASE WHEN L6.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L5.NAME ,'') + CASE WHEN L5.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L4.NAME ,'') + CASE WHEN L4.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L3.NAME ,'') + CASE WHEN L3.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L2.NAME ,'') + CASE WHEN L2.NAME  = '/' THEN '' ELSE '/' END
--+ isnull(L1.NAME ,'')
), LEN(
  isnull(L10.NAME,'') + CASE WHEN L10.NAME = '/' THEN '' ELSE '/' END
+ isnull(L9.NAME ,'') + CASE WHEN L9.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L8.NAME ,'') + CASE WHEN L8.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L7.NAME ,'') + CASE WHEN L7.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L6.NAME ,'') + CASE WHEN L6.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L5.NAME ,'') + CASE WHEN L5.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L4.NAME ,'') + CASE WHEN L4.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L3.NAME ,'') + CASE WHEN L3.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L2.NAME ,'') + CASE WHEN L2.NAME  = '/' THEN '' ELSE '/' END
--+ isnull(L1.NAME ,'')
))
  ) as Path
FROM CMOBJECTS A
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmclasses.name in('report')
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L1 ON A.cmid = L1.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L2 ON L1.pcmid = L2.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L3 ON L2.pcmid = L3.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L4 ON L3.pcmid = L4.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L5 ON L4.pcmid = L5.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
        	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L6 ON L5.pcmid = L6.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L7 ON L6.pcmid = L7.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L8 ON L7.pcmid = L8.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L9 ON L8.pcmid = L9.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L10 ON L9.pcmid = L10.cmid
WHERE CLASSID IN (10,18) -- reports & packages
--  AND DISABLED is NULL
) Z INNER JOIN cmstoreids on Z.CMID = cmstoreids.CMID
    INNER JOIN (SELECT cmrefnoord1.cmid AS pid,
       cmobjnames.name as package_name
FROM   cmrefnoord1
       INNER JOIN cmobjects
         ON cmrefnoord1.refcmid = cmobjects.cmid
       INNER JOIN cmobjnames
         ON cmobjects.cmid = cmobjnames.cmid
WHERE  cmrefnoord1.propid = 31
       AND cmobjnames.localeid IN ( '24', '100')
     ) PKG on Z.CMID = PKG.PID
--WHERE Path like '%/REPORTING/%'
group by NAME, package_name, storeID
Jean-Baptiste Heren

Author: Jean-Baptiste Heren

Stay in touch with the latest news and subscribe to the RSS Feed about this category

Mars13008 Mars13008 ·  30 September 2011, 18:31

Thanks a lot.
It's very useful!
I've reworked the last script, you will find below:
with lev as
(
SELECT cmobjnames.cmid,
cmstoreids.storeid,
cmobjects.pcmid,
cmobjnames.name,
cmobjnames.mapdlocaleid,
cmobjnames.localeid,
cmclasses.name AS type
FROM cmobjects
inner join cmobjnames on cmobjects.cmid = cmobjnames.cmid
inner join cmclasses on cmobjects.classid = cmclasses.classid
inner join cmstoreids on cmobjects.cmid = cmstoreids.cmid
WHERE 1=1
AND cmobjnames.ISDEFAULT=1
),
pkg as
(
SELECT cmrefnoord1.cmid AS pid,
cmobjnames.name AS package_name
FROM cmrefnoord1
INNER JOIN cmobjects
ON cmrefnoord1.refcmid = cmobjects.cmid
INNER JOIN cmobjnames
ON cmobjects.cmid = cmobjnames.cmid
WHERE cmrefnoord1.propid = 31
AND cmobjnames.ISDEFAULT=1
)
select
--COUNT(1)
A.CMID,
L1.mapdlocaleid,
L1.TYPE ,
L1.NAME /*, L9.NAME, L8.NAME, L7.NAME, L6.NAME , L5.NAME, L4.NAME , L3.NAME , L2.NAME*/
, LTRIM(SUBSTRING(
CASE WHEN isnull(L10.NAME,'') = '/' THEN ' ' ELSE isnull(L10.NAME,'') END + CASE WHEN L10.NAME = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L9.NAME,'') = '/' THEN ' ' ELSE isnull(L9.NAME,'') END + CASE WHEN L9.NAME = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L8.NAME,'') = '/' THEN ' ' ELSE isnull(L8.NAME,'') END + CASE WHEN L8.NAME = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L7.NAME,'') = '/' THEN ' ' ELSE isnull(L7.NAME,'') END + CASE WHEN L7.NAME = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L6.NAME,'') = '/' THEN ' ' ELSE isnull(L6.NAME,'') END + CASE WHEN L6.NAME = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L5.NAME,'') = '/' THEN ' ' ELSE isnull(L5.NAME,'') END + CASE WHEN L5.NAME = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L4.NAME,'') = '/' THEN ' ' ELSE isnull(L4.NAME,'') END + CASE WHEN L4.NAME = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L3.NAME,'') = '/' THEN ' ' ELSE isnull(L3.NAME,'') END + CASE WHEN L3.NAME = '/' THEN '' ELSE '/' END
+ isnull(L2.NAME ,'') + CASE WHEN L2.NAME = '/' THEN '' ELSE '/' END
--+ isnull(L1.NAME ,'')
, CHARINDEX('Public',
isnull(L10.NAME,'') + CASE WHEN L10.NAME = '/' THEN '' ELSE '/' END
+ isnull(L9.NAME ,'') + CASE WHEN L9.NAME = '/' THEN '' ELSE '/' END
+ isnull(L8.NAME ,'') + CASE WHEN L8.NAME = '/' THEN '' ELSE '/' END
+ isnull(L7.NAME ,'') + CASE WHEN L7.NAME = '/' THEN '' ELSE '/' END
+ isnull(L6.NAME ,'') + CASE WHEN L6.NAME = '/' THEN '' ELSE '/' END
+ isnull(L5.NAME ,'') + CASE WHEN L5.NAME = '/' THEN '' ELSE '/' END
+ isnull(L4.NAME ,'') + CASE WHEN L4.NAME = '/' THEN '' ELSE '/' END
+ isnull(L3.NAME ,'') + CASE WHEN L3.NAME = '/' THEN '' ELSE '/' END
+ isnull(L2.NAME ,'') + CASE WHEN L2.NAME = '/' THEN '' ELSE '/' END
--+ isnull(L1.NAME ,'')
), LEN(
isnull(L10.NAME,'') + CASE WHEN L10.NAME = '/' THEN '' ELSE '/' END
+ isnull(L9.NAME ,'') + CASE WHEN L9.NAME = '/' THEN '' ELSE '/' END
+ isnull(L8.NAME ,'') + CASE WHEN L8.NAME = '/' THEN '' ELSE '/' END
+ isnull(L7.NAME ,'') + CASE WHEN L7.NAME = '/' THEN '' ELSE '/' END
+ isnull(L6.NAME ,'') + CASE WHEN L6.NAME = '/' THEN '' ELSE '/' END
+ isnull(L5.NAME ,'') + CASE WHEN L5.NAME = '/' THEN '' ELSE '/' END
+ isnull(L4.NAME ,'') + CASE WHEN L4.NAME = '/' THEN '' ELSE '/' END
+ isnull(L3.NAME ,'') + CASE WHEN L3.NAME = '/' THEN '' ELSE '/' END
+ isnull(L2.NAME ,'') + CASE WHEN L2.NAME = '/' THEN '' ELSE '/' END
--+ isnull(L1.NAME ,'')
))
) AS Path
,package_name
from
CMOBJECTS A
left join pkg on PKG.PID=a.cmid
INNER JOIN lev l1 on L1.cmid =A.cmid
inner JOIN lev l2 on L2.cmid =L1.pcmid
inner JOIN lev l3 on L3.cmid =L2.pcmid
inner JOIN lev l4 on L4.cmid =L3.pcmid
inner JOIN lev l5 on L5.cmid =L4.pcmid
inner JOIN lev l6 on L6.cmid =L5.pcmid
inner JOIN lev l7 on L7.cmid =L6.pcmid
inner JOIN lev l8 on L8.cmid =L7.pcmid
inner JOIN lev l9 on L9.cmid =L8.pcmid
inner JOIN lev l10 on L10.cmid =L9.pcmid
where
CLASSID IN (10,18) -- reports & packages

You Might Also Like

result_crosstab.png

Cognos Report : Display a different measure in row for the aggregated column

Following tip is when you use a Relational Datamodel (DMR). In a Cognos Report crosstab, If You need to Display different measure in the aggregated column, you can do it using a simple if-then-else...

Continue reading

Cognos 8 - convert a member unique name from one hierarchy to another

If you need to build any report or Event studio Query using the same data from different packages; and you want to prompt just once. You can convert the member unique name from one hierarchy to...

Continue reading