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 temp1
create database temp1
go
use temp1
go
IF 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]
GO
CREATE PROCEDURE [dbo].[cm_ListAllReportsAndQueries]
/***************************************************************
cm_ListAllReportsAndQueries creates table cm_reportlist which lists all the
reports and queries.
Example
=======
-- pass in the Cognos CM Store
exec cm_ListAllReportsAndQueries 'Store32CM_Cognos11'
QUERIES
========
-- select rows from the output table cm_reportList
select [Directory Path],[Report Name],[Package Name],[Owner Name],[Type],[Modified],[Created],[CMID],[PCMID],[mapdlocaleid], ReportList_Create from cm_reportlist
Changes1
========
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.'
END
GO
exec 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
------------------------------