Automated Cognos Report Documentation

If you need a list with all reports (Cognos ReportNet or Cognos8), including the location of the report you can query the content store. It's easy to retrieve a list like:

Location	Name	Created	Modified	Type	Contact	Description
GO Data Warehouse/Employee Profile	Employee Profile	2005-09-07 18:06:45.790	2007-01-04 20:43:34.617	powerplay cube	Stewart James	Human resources cube showing data for each employee.
GO Data Warehouse/Sales Target by Region	Sales Target by Region	2005-07-17 20:04:09.890	2007-01-04 20:44:12.587	powerplay report	Stewart James	Report showing sales target by region using a prompt to provide values for the report.
GO Data Warehouse/Tool Tips	Tool Tips	2005-08-23 19:26:18.127	2007-01-04 20:44:30.040	report	Stewart James	Report that shows tool tips and baselines in chart.
GO Sales and Retailers/Actual Sales against Target Sales	Actual Sales against Target Sales	2005-05-27 20:46:43.640	2006-12-14 15:41:06.260	report	Melvin John	The report shows a simple list with conditional formatting that drills through to the Sales Representative Contact List report.
GO Sales and Retailers/Actual Sales against Target Sales-burst	Actual Sales against Target Sales-burst	2005-05-27 20:50:06.910	2006-12-14 15:41:15.463	report	Melvin John	Report that is set up for bursting to email addresses. The email addresses point to sales representatives in the Sales reps table in the GOSALES database.
GO Sales and Retailers/Banded Report	Banded Report	2005-05-27 20:57:01.480	2006-12-14 15:41:26.433	report	Melvin John	Banded report that shows Product name Quantity and Revenue with sales opportunities for each Product line category.
GO Sales and Retailers/Business Details Drill Through	Business Details Drill Through	2005-05-27 19:12:28.627	2006-12-14 15:42:07.150	report	Melvin John	Report that shows product details in charts. This report is a drill-through target report for the GO Business View report. You can also run it as a product-prompted report.
GO Sales and Retailers/Consumer Trends	Consumer Trends	2007-01-05 15:35:24.003	2007-01-05 16:24:21.317	report	Melvin John	This complex report shows a list chart bar chart and product images to illustrate revenue by product type.
GO Sales/Conditional Display	Conditional Display	2006-05-30 18:42:25.157	2007-01-04 20:45:26.793	report	Dropster Tina	The report uses a prompt page to query a chart and a list report.
GO Sales/Custom Legend	Custom Legend	2005-05-19 18:57:04.317	2007-01-04 20:45:34.840	report	Dropster Tina	The report shows that the legend can be customized in a similar way to the titles.
GO Sales/Orders Report	Orders Report	2005-06-13 12:50:18.680	2007-01-04 20:45:52.907	report view	Dropster Tina	This report shows the default style applied to List Column Title and the List Column Body in a single step.
GO Sales/Product Report	Product Report	2005-05-19 18:56:43.683	2007-01-04 20:47:20.690	report	Dropster Tina	The report displays combination charts with drills through options.
GO Sales/Retailer Report (Multiple Prompt Values)	Retailer Report (Multiple Prompt Values)	2005-06-16 16:50:21.937	2007-01-04 20:56:16.270	report	Dropster Tina	This list report accepts multiple prompt values.
GO Sales/Returns by Order Method -Prompted Chart	Returns by Order Method -Prompted Chart	2005-08-05 17:00:31.907	2007-01-04 20:56:56.177	report	Dropster Tina	This report uses a bar chart and a list to show the reasons why products are returned. It uses a prompt to filter on the return description. It is illustrated in the Getting Started tutorial.
GO Sales/Revenue by Sales Territory	Revenue by Sales Territory	2005-06-14 18:19:12.653	2007-01-04 21:00:04.703	report	Dropster Tina	The report shows a chart and a crosstab display with conditional formatting that drills through to the Orders Report report.
GO Sales/Top 5 Sales Staff	Top 5 Sales Staff	2005-07-22 23:44:55.907	2007-01-04 21:00:13.737	report	Dropster Tina	This list report embeds a bar chart that shows the top five sales representatives by sales targets and revenue.
GO Sales/Top Revenue (Conditional)	Top Revenue (Conditional)	2005-06-16 19:22:10.160	2007-01-04 21:00:27.347	report	Dropster Tina	A list report that conditionally hides the Product Line group footers for the revenue lower than the specified value.

The only hard part is to create the location. You have the implement a user defined function to join the parent cmid (pcmid) to the cmid. A stored procedure fills a table with all the info you need. I wrote a script that works for ReportNet and Cognos8:

First create the UDF and the stored procedure:

CREATE FUNCTION [dbo].[ufn_GetParentPath] ( @pCurrentNodeID INT )
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @vCurrentNodeName VARCHAR(50)
DECLARE @vParentID NVARCHAR(100)
IF @pCurrentNodeID IS NULL OR @pCurrentNodeID = 0
RETURN NULL
SELECT @vCurrentNodeName = [name], @vParentID = [pcmid],@pCurrentNodeID =[cmid]
FROM [dbo].[reports]
WHERE [cmid] = @pCurrentNodeID
RETURN ISNULL([dbo].[ufn_GetParentPath] ( @vParentID ) + '/', '') + @vCurrentNodeName
END

CREATE procedure [dbo].[pLoadReportData]
as
begin
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[reports]') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table reports
CREATE TABLE [dbo].[reports](
[cmid] [int] NOT NULL,
[localeid] [smallint] NOT NULL,
[mapdlocaleid] [smallint] NULL,
[isdefault] [bit] NOT NULL,
[name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[pcmid] [int] NOT NULL,
[version] [int] NOT NULL,
[created] [datetime] NOT NULL,
[modified] [datetime] NOT NULL,
[disabled] [tinyint] NULL,
[class] [nvarchar](50) NOT NULL,
[dispseq] [int] NOT NULL,
[contactemail] [varchar] (128) NULL,
[contact] [varchar] (128) NULL,
[description] [nvarchar] (3772) NULL,
[owner] [int] NULL,
[path] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[spec] XML NULL
)

create index ix_cmid on reports (cmid)

insert into reports
(
cmid
, localeid
, mapdlocaleid
, isdefault
, name
, pcmid
, version
, created
, modified
, disabled
, class
, dispseq
, owner
, path
)
select
n.cmid
, n.localeid
, n.mapdlocaleid
, n.isdefault
, n.name
, o.pcmid
, o.version
, o.created
, o.modified
, o.disabled
, c.name
, o.dispseq
, o.owner
, '' path
from cmobjnames n join cmobjects o
on n.cmid = o.cmid and o.classid in (0,1,10,18,19,33,81,82)
join cmclasses c on o.classid = c.classid and n.isdefault = 1

update reports
set contact = c.contact
, contactemail = c.contactemail
from cmobjprops10 c
join reports on c.cmid = reports.cmid

update reports
set description = d.value
from cmobjprops3 d
join reports on d.cmid = reports.cmid
and d.isdefault = 1

update reports
set path = x.path
from
(SELECT [dbo].[ufn_GetParentPath] ( [cmid] ) AS [Path], [name],[cmid]
FROM [dbo].[reports]) x
where reports.cmid = x.cmid

end

Run (and schedule) the stored procedure.

Create a view to exclude the folder, package, root and content items:

create view [dbo].[v_reports]
as
select
path
, name
, created
, modified
, class
, description
, contact
, contactemail
from reports
where class in ('report','reportview','powerplaycube','powerplay report')

Query

'select * from v_reports order by path'

will do the final part of the job.

I implemented this for a customer with >37000 (!) reports, report views, cubes and cube reports. In this case, the script runs in about 10-15 seconds.