While working with a client on a Cognos upgrade effort, I was asked to provide a list of each report by the package it sourced. Since I am a database fanatic I prefer to get this information directly from the Cognos content store. Ultimately, I was able to provide a list with each Report name, package and path for each report by using the query provided below.
The Cognos content store has a lot of valuable information. Unfortunately, it is not always so easy to find this information. Have you ever been asked where a specific report can be found in Cognos? Or better yet, have you had to find all the existing reports that use a certain package? Below you will find a query useful for finding Report Name, Package and Path information from the Cognos content store database.
Let us know in the comments if you have done something similar or encountered any challenges around using the content store as a data source.
This example works with Cognos 10 running on an Oracle database. In addition, it will also work on the Cognos 8 content store.
--============================================================= -- COGNOS CONTENT STORE QUERY (Oracle database) -- Use to find list REPORT_NAME, PACKAGE and SEARCH-PATH --============================================================= WITH base_query AS (select classid, cmobjects.cmid, cmobjects.pcmid, cmobjnames.name as report_name from cognos_c84.cmobjects, cognos_c84.cmobjnames where cmobjects.cmid = cmobjnames.cmid and classid in (10) and cmobjnames.mapdlocaleid = 24 union select classid, cmobjects.cmid as cmid, cmobjects.pcmid, cmobjnames.name as report_name from cognos_c84.cmobjects, cognos_c84.cmobjnames where cmobjects.cmid = cmobjnames.cmid and cmobjects.cmid in (select distinct cmobjects.pcmid from cognos_c84.cmobjects) and cmobjnames.mapdlocaleid = 24) , ctebuildpath (cmid,pcmid,report_name,Hlevel,classid,directory_path) AS (select base_query.cmid, base_query.pcmid, base_query.report_name, 1 as Hlevel, classid, to_char(base_query.report_name) as directory_path from base_query where report_name = 'Public Folders' /* add myfolders to retrieve reports from my folders of users */ union all select recur.cmid, recur.pcmid, recur.report_name, cte.Hlevel + 1 as Hlevel, recur.classid, to_char(cte.directory_path || ' > ' || recur.report_name) as directory_path from base_query recur, ctebuildpath cte where cte.cmid = recur.pcmid) , report_spec AS (select spec, cmid, instr(spec,'/package[@name=')+15 as startposition, instr(spec, ']/model[@name=')-instr(spec,'/package[@name=')-15 as endposition from cognos_c84.cmobjprops7 where spec is not null) -- SELECT report_name, dbms_lob.substr(spec, endposition, startposition) as package, directory_path FROM ctebuildpath cte, report_spec rep WHERE cte.classid = 10 and cte.cmid = rep.cmid order by directory_path, report_name
Also see our User, Group, and Role query
Be aware that future patches or hotfixes from the vendor may cause this SQL to fail or return incorrect results. There is a better method for returning this information by using the Cognos SDK. More info here.
Thanks to Cognos with Hari for putting this together using SQL Server Content Store.