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
------------------------------