--MS SQL
-- cte for retrieving the report specification is in the code, but not referenced in the SELECT/INSERT statement at the end
-- run from the context of your cognos content manager
use Store01cm_cognos11 -- CHANGE THIS
-- optional to insert results into a table. uncomment INSERT statement below also
/*
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)
*/
/** 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) -- I have never been able to figure out the maplocaleid
),
/** 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)
)
-- optional to insert results into a table. uncomment CREATE TABLE above also
-- 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() as ReportList_Create
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]