SQL to get a List of ReportNames in Cognos

[TheCognosDave]

I was hunting around to figure out how to do this, then reverse engineered it on the backend.... so it may not be 100%.  I figured I'd share my findings in case I'm able to help save someone else a bit time.

If you're just looking for a list of reports in the System, you can query Cognos with the following SQL and get it.  I realize the SQL is poorly syntax'd for performance, but the data is so small it's lightning fast anyways.


USE [C10.1.1_ContentStore];
GO

SELECT *
FROM CMOBJNAMES
WHERE CMID in (
            SELECT CMID
              FROM CMOBJECTS
              WHERE CLASSID = (
                        SELECT CLASSID
                        FROM CMCLASSES
                        WHERE NAME = 'report'
                        )
            )
ORDER BY NAME


Hope this saves somebody out there some time.

cheers,

TheCognosDave

[Grim]

You can do this via SDK:
http://www-01.ibm.com/support/docview.wss?uid=swg21374788

You can also use the Content Browser tool:
https://www-304.ibm.com/connections/blogs/basupportlink/entry/ibm_cognos_bi_content_manager_browser_diagnostic_tool2?lang=en_us

http://www.ibm.com/developerworks/data/library/techarticle/dm-0808masters/

http://www-01.ibm.com/support/docview.wss?uid=swg24021211


As for doing that manually via SQL - No idea. I tend to use only Cognos provided tools to go in there.

[ohiocort]

TheCognosDave,

Thanks for sharing. As I'm new to Cognos, but not to SQL, I tend to feel a little more comfortable right now navigating through the backend. This snippet was helpful, although I had to change CMOBJNAMES to CMOBJNAMES_BASE.

Grim, Thanks for showing the alternative front-end approach.

-Cort

[TheCognosDave]

Ohiocort:  Glad to help man.  I'm the same way ... all the answers I want are in the database somewhere ... it's just the work of reverse engineering a bit to figure out where !  ;D
Grim: thx for the tip !

[Grim]

NP

Add: I don't know why this slipped my mind either, but you can produce a list of all reports via a quick list report against the Audit package too (If you have an Audit DB setup, which you should!).  ;)