Fun with SQL: Listing All Reports In users' "My Folders" directory using SQL

[TheCognosDave]

I needed to find a way to see all the reports that my users are putting in their "My Folders" directories.  I'm posting this here in case someone else has the same challenge and is looking for an answer.

I wrote SQL to go directly against the Cognos Metadata as follows:

/* -----------------------------------------------------
Original SQL by TheCognosDave, Oct 18,2013
-------------------------------------------------------- */

SELECT Report_Name, MODIFIED, coalesce(B.Name,'') as CognosUser
FROM (
   SELECT
      NMS.NAME as Report_Name,
      OBJ.MODIFIED,
      CASE
         WHEN OBJ_1.CLASSID=5 THEN OBJ_1.CMID
         WHEN OBJ_2.CLASSID=5 THEN OBJ_2.CMID
         WHEN OBJ_3.CLASSID=5 THEN OBJ_3.CMID
         WHEN OBJ_4.CLASSID=5 THEN OBJ_4.CMID
         WHEN OBJ_5.CLASSID=5 THEN OBJ_5.CMID
         WHEN OBJ_6.CLASSID=5 THEN OBJ_6.CMID
         WHEN OBJ_7.CLASSID=5 THEN OBJ_7.CMID
      END as ACCT_CMID
   FROM CMOBJECTS OBJ
      INNER JOIN CMCLASSES CLS ON (OBJ.CLASSID = CLS.CLASSID)
      INNER JOIN CMOBJNAMES NMS ON (OBJ.CMID = NMS.CMID)
      INNER JOIN CMOBJECTS c_OBJ ON (OBJ.PCMID = c_OBJ.CMID)
      INNER JOIN CMCLASSES c_CLS ON (c_OBJ.CLASSID = c_CLS.CLASSID)
      INNER JOIN CMOBJNAMES c_NMS ON (c_OBJ.CMID = c_NMS.CMID)
      INNER JOIN CMOBJECTS OBJ_1 ON (c_OBJ.PCMID = OBJ_1.CMID)
      INNER JOIN CMOBJECTS OBJ_2 ON (OBJ_1.PCMID = OBJ_2.CMID)
      INNER JOIN CMOBJECTS OBJ_3 ON (OBJ_2.PCMID = OBJ_3.CMID)
      INNER JOIN CMOBJECTS OBJ_4 ON (OBJ_3.PCMID = OBJ_4.CMID)
      INNER JOIN CMOBJECTS OBJ_5 ON (OBJ_4.PCMID = OBJ_5.CMID)
      INNER JOIN CMOBJECTS OBJ_6 ON (OBJ_5.PCMID = OBJ_6.CMID)
      INNER JOIN CMOBJECTS OBJ_7 ON (OBJ_6.PCMID = OBJ_7.CMID)
   WHERE  NMS.MAPDLOCALEID = 24 AND c_NMS.MAPDLOCALEID = 24 AND OBJ.CLASSID = 10
   ) A LEFT JOIN CMOBJPROPS33 B ON (A.ACCT_CMID=B.CMID)
ORDER BY MODIFIED DESC

/* ----------------------------------------------------- -------------------------------------------------------- */

I'm having fun lately reverse engineering the Cognos Datamodel to figure these things out.  I hope it's useful to someone out there beside me !  :P

cheers,
Dave