I found the change required. It's in the query for package name and I just needed to add propid 58 in the where clause for data modules.
Original Message:
Sent: Thu January 08, 2026 08:36 AM
From: Jeremy Aude
Subject: How to get the report paths from all report objects of cognos analytics catalog
Hi Brenda,
Thanks for sharing this! Were you able to figure out how to capture data module names? The Package Name logic doesn't appear to work as it's just integer values when the source is a data module.
I'll keep investigating too.
Thanks again. This is fantastic.
------------------------------
Jeremy Aude
Original Message:
Sent: Wed February 12, 2025 04:15 PM
From: brenda grossnickle
Subject: How to get the report paths from all report objects of cognos analytics catalog
I wrote a MS SQL stored procedure. Below is the tsql code to (1) create a database called temp1 (2) create a stored procedure in temp1 to read the cognos content store looking for reports and queries and writing them to a table (3) call the stored procedure passing in the name of the cognos database - exec cm_ListAllReportsAndQueries 'Store32cm_Cognos11' (4) display the temp1 table CM_ReportList.
I wrote the stored procedure so that it could be created and run from a database other than the cognos content store.
--drop database temp1create database temp1gouse temp1goIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cm_ListAllReportsAndQueries]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[cm_ListAllReportsAndQueries]GOCREATE PROCEDURE [dbo].[cm_ListAllReportsAndQueries]/***************************************************************cm_ListAllReportsAndQueries creates table cm_reportlist which lists all thereports and queries. Example=======-- pass in the Cognos CM Storeexec cm_ListAllReportsAndQueries 'Store32CM_Cognos11' QUERIES========-- select rows from the output table cm_reportListselect [Directory Path],[Report Name],[Package Name],[Owner Name],[Type],[Modified],[Created],[CMID],[PCMID],[mapdlocaleid], ReportList_Create from cm_reportlistChanges1========2/11/2025 - bxg - created sproc ****************************************************************/@pCognosCMDataBase varchar(28) AS BEGIN SET NOCOUNT ON declare @note varchar(500) = '' declare @rowcount int = 0 declare @DBName nvarchar(128) = db_name() declare @SQL nvarchar(max) = '' declare @SQL2 nvarchar(max) = '' /* IF @pReportSpecification IS NULL or @pReportSpecification <> 0 Set @pReportSpecification = 1 */ IF @pCognosCMDataBase IS NULL or @pCognosCMDataBase = '' or not exists (select top 1 1 from sysdatabases) begin Print 'Invalid Cognos CM Database Name: ' + isnull(@pCognosCMDataBase, 'NULL') end IF OBJECT_ID('[dbo].[CM_ReportList]') IS NOT NULL DROP TABLE [dbo].[CM_ReportList] CREATE TABLE [dbo].[CM_ReportList] ( [Directory Path] VARCHAR(512) NULL, [Report Name] NVARCHAR(256) NULL, [Package Name] NVARCHAR(256) NULL, [Owner Name] NVARCHAR(256) NULL, [Type] VARCHAR(128) NULL, [Modified] DATETIME NULL, [Created] DATETIME NULL, [CMID] INT NULL, [PCMID] INT NULL, [mapdlocaleid] SMALLINT NULL, [ReportList_Create] DATETIME NULL) set @SQL = N' use ' + @pCognosCMDataBase + N' /** query on the Cognos Content Store to find reports and queries (10 and 37) **/ ; with base_query as ( select classid, cmobjects.cmid, cmobjects.pcmid, cmobjnames.name as reportname, cmobjects.disabled, cmobjnames.mapdlocaleid, cmobjects.CREATED, cmobjects.modified from cmobjects , cmobjnames where cmobjects.cmid = cmobjnames.cmid and (cmobjects.disabled is null or cmobjects.disabled <> 1) and classid in (10, 37) and cmobjnames.mapdlocaleid in (24,96, 92) union select classid, cmobjects.cmid as cmid, cmobjects.pcmid, cmobjnames.name as reportname, cmobjects.disabled, cmobjnames.mapdlocaleid, cmobjects.CREATED, cmobjects.modified from cmobjects , cmobjnames where cmobjects.cmid = cmobjnames.cmid and (cmobjects.disabled is null or cmobjects.disabled <> 1) and cmobjects.cmid in (select distinct cmobjects.pcmid from cmobjects) and cmobjnames.mapdlocaleid in (24,96,92) ), /** recursive query through the folders to build the report and query path **/ ctebuildpath as ( select base_query.cmid, base_query.pcmid, base_query.reportname, base_query.created, base_query.modified, base_query.[disabled], base_query.mapdlocaleid, 1 as Hlevel,classid, cast(base_query.reportname as varchar(500))as directory_path from base_query where reportname = ''Team Content'' or reportname = ''My Folders'' union all select recur.cmid, recur.pcmid, recur.reportname, recur.created, recur.modified, recur.[disabled], recur.mapdlocaleid, cte.Hlevel+1 as Hlevel,recur.classid, cast(cte.directory_path + ''>'' + recur.reportname as varchar(500))as directory_path from base_query as recur, ctebuildpath as cte where cte.cmid = recur.pcmid ), /** charindex('']/package[@name='', cast(spec as varchar(max)))+17 as startposition, charindex('']/model[@name='', cast(spec as varchar(max)))-18-charindex('']/package[@name='', cast(spec as varchar(max))) as endposition **/ report_spec as ( select cast(spec as varchar(max)) as spec, c.cmid from cmobjprops7 c inner join ctebuildpath b on c.cmid = b.cmid where spec is not null ), /** get the package name **/ package 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 ), /** query to find the Owner of the reports and queries **/ owner_name as ( select a.name, b.cmid from cmobjprops33 a, cmobjnames b, cmrefnoord2 c, cmobjects d, cmclasses e, ctebuildpath cte where b.cmid = c.cmid and a.cmid = c.refcmid and b.cmid = d.cmid and d.classid = e.classid and (e.name = ''report'' or e.NAME = ''query'') and cte.CMID = b.cmid ), /** get rid of duplicates **/ get_distinct as ( SELECT distinct directory_path as [Directory Path], reportname as [Report Name], package.package_name as [Package Name], ISNULL(Owner_name.name, ''Unknown'') as ''Owner Name'', Case classid When 10 Then ''Report'' Else ''Query'' End as Type, Modified, Created, cte.cmid as CMID, cte.pcmid as PCMID, mapdlocaleid FROM ctebuildpath cte inner join report_spec rep on cte.cmid = rep.cmid full outer join owner_name on cte.CMID = owner_name.cmid left join package on package.pid = cte.cmid WHERE cte.classid in (10, 37) ) ' -- sql literal cannot be more than 4000 characters. so splitting the dynamic sql into two parts Set @SQL2 = '/** sort My Folders first, then within My Folder sort by Owner Name. WIthin the second group (Public Folders and ??) sort by Directory Path **/ Insert into ' + @dbname + N'..CM_ReportList ([Directory Path],[Report Name],[Package Name],[Owner Name],[Type],[Modified],[Created],[CMID],[PCMID],[mapdlocaleid], ReportList_Create) Select [Directory Path],[Report Name],[Package Name],[Owner Name],[Type],[Modified],[Created],[CMID],[PCMID],[mapdlocaleid], getdate() from get_distinct ORDER by CASE WHEN LEFT([directory path], 11) = ''My Folders>'' THen 0 ELSE 1 END, CASE WHEN LEFT([directory path], 11) = ''My Folders>'' THen [Owner Name] ELSE [Directory Path] END, [directory path] ' set @sql = @sql + @sql2 --exec util_longprint @sql exec sp_executesql @SQL set @rowcount = @@rowcount Print 'CM Create ReportList created with ' + cast(@RowCount as varchar(12)) + ' rows.' ENDGOexec cm_ListAllReportsAndQueries 'Store32cm_Cognos11'select * from CM_ReportList
------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
Original Message:
Sent: Wed January 22, 2025 12:20 PM
From: Paulo McMiller
Subject: How to get the report paths from all report objects of cognos analytics catalog
I would like to get the report paths from the report objects of the cognos catalog, I'm trying to use the CMOBJECTS, CMOBJNAMES, CMCLASSES and CMOBJPROPS tables to get this information but I'm still confused on how exactly to do this. The only approach that I have tried was using the xml column found in CMOBJNAMES7 table to extract the stored paths, but I'm in doubt because when I join it with the audit table COGIPF_RUNREPORT it seems that there is not much matches.
Does anyone knows an alternative approach to do this?
------------------------------
Paulo Paulo Mcmiller Crisostomo De Olivei
------------------------------