below is an image of the output and the code. unfortunately, my company's security that will not allow me to paste it as a formatted code block. There are plenty of free online SQL formatters. Read my comments at the top of the code.
USE [Cognos_CM_Database] -- CHANGE THIS to your Cognos Content Store
/***************************************************************
Returns a list of all reports, queries and report views (objects) from
the Cognos Content Store. Returns content in My Folders and Team Content.
If Owner Name is Unknown then owner is no longer in the directory.
If the report is on a schedule (report or job) the Modified timestamp is the
last time the schedule ran. CMID is the unique identifier for the object.
PCMID is the parent unique identifier which is the immediate folder where
the object is located.
Words for mapdlocaleid in (24,96, 92). If you have a different mapdlocaleid
do a find and add it to the list.
@pReportSpecification is currently set to 0 which does not return the report
defintion. change to 1 if you want the report definition.
****************************************************************/
Declare @pReportSpecification Int = 0 -- 1 Include, 0 do not include
BEGIN
SET NOCOUNT ON
IF @pReportSpecification IS NULL or @pReportSpecification <> 0
Set @pReportSpecification = 1
Declare @SearchTextLIKE varchar(512)
Declare @PreviewTextLeadSize int = 50
Declare @PreviewTextTrailSize int = 30
Declare @Results Table (OwnerName varchar(128), ClassID varchar(25), ReportName varchar(128), DirectoryPath varchar(512),
CMID int, PCMID int, ReportDefinition varchar(max))
-- complicated 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, 19) -- reports, queries, reportview
--and cmobjnames.mapdlocaleid = @pmapdlocaleid
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 = @pmapdlocaleid
and cmobjnames.mapdlocaleid in (24,96,92)
-- and cmobjnames.mapdlocaleid in (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
),
report_spec as
(
select cast(spec as varchar(max)) as spec, c.cmid --,
--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
from cmobjprops7 c inner join ctebuildpath b
on c.cmid = b.cmid
where spec is not null
),
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_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 --, -- [disabled] --,
-- Case WHEN @pReportSpecification = 0 THEN '' ELSE spec END as [Report Definition]
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)
)
Select * from get_distinct
-- sort My Folders first, then within My Folder sort by Owner Name. WIthin the second group (Public Folders and ??) sort by Directory Path
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]
END
GO