[SOLVED] Cognos 8 Query to find all Report and Column Names

Cognos 8 Query to find all Report and Column Names

I want to query the meta data in Cognos 8 to find all report and column names. If possible, I'd like to include the column definitions.

Can I do this using a Cognos report or do I need to query some repository?

Thanks.

Solution

You can select a list of reports from the content store with the following query:

SELECT     CMOBJNAMES_BASE.NAME AS ObjName, CMOBJECTS.PCMID, CMCLASSES.NAME AS ClassName, CMOBJPROPS7.spec
FROM       CMOBJECTS 
JOIN       CMOBJNAMES_BASE ON CMOBJECTS.CMID    = CMOBJNAMES_BASE.CMID
JOIN       CMCLASSES       ON CMOBJECTS.CLASSID = CMCLASSES.CLASSID
LEFT JOIN  CMOBJPROPS7     ON CMOBJECTS.CMID = CMOBJPROPS7.CMID
WHERE     CMOBJECTS.CLASSID IN (10, 37)
ORDER BY CMOBJECTS.PCMID;

I use that in Cognos 10. I believe in cognos 8 the CMOBJNAMES_BASE table is actually named 'CMOBJNAMES' without the _BASE.

UPDATE: Has been tested and works in Cognos 11r9.

The Report metadata is stored in the 'SPEC' column of CMOBJPROPS7 as XML. You can parse this XML in order to strip out the columns used in the report. It will not be a simple task.

If you have time but not money, you can write your own code to parse that XML. If you have more money than time, you can buy a 3rd party program to accomplish this, such as Motio or BSP Metamanager.

The query above is less useful for building a clean list of columns, but great for searching for specific data items. For example, you have column you are wanting to change in a data source, but you are not sure which report uses that column. Run the query above, and search for the data item. It will be embedded within the XML in the Cognos MDX format, ie. [Presentation View].[Sales Summary].[Sales]

EDIT: As requested below, here is a query that includes folder paths.

-- List of Reports, the folder they are in, and the package they are using
select distinct temp2.name as package,temp1.folder,temp1.name from
(SELECT    temp.PARENTNAME AS FOLDER,CMOBJECTS.PCMID,CMOBJNAMES.CMID, CMOBJNAMES.LOCALEID, CMOBJNAMES.MAPDLOCALEID, CMOBJNAMES.ISDEFAULT, CMOBJNAMES.NAME, 
                      CMOBJECTS.CLASSID
FROM         CMOBJNAMES INNER JOIN
                      CMOBJECTS ON CMOBJNAMES.CMID = CMOBJECTS.CMID
INNER JOIN
(SELECT P.CMID AS PARENT,P.NAME AS PARENTNAME FROM CMOBJNAMES P where P.LOCALEID between 24 and 52) temp
ON CMOBJECTS.PCMID = TEMP.PARENT
WHERE     (CMOBJECTS.CLASSID = 10)
AND SUBSTR(TEMP.PARENTNAME,1,1) NOT IN ('1','2','3','4','5','6','7','8','9') AND
TEMP.PARENTNAME NOT LIKE 'Backup%') temp1
inner join
(SELECT  CMREFNOORD1.CMID AS PID, CMREFNOORD1.REFCMID, CMOBJNAMES.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.LOCALEID between 24 and 52)) temp2
on temp1.cmid = temp2.pid and LOCALEID between 24 and 52;