Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  CA Content Store Query

    Posted Mon May 02, 2022 01:27 AM
    HI

    Could you please provide the SQL query off the content store that will produce the following columns.

    Columns Sample Values
    Object Name Annual Reports
    Path Team Content\IBM\Reports
    Level 2
    Content Type Folder, Report, Dashboard
    Creation Date 01-Mar-21
    Modification Date 05-Feb-22
    Owner user1
    Package Name Great Outdoors
    Description Folder of Sales Reports

    Thanks

    ------------------------------
    Vic Santos
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: CA Content Store Query

    Posted Mon May 02, 2022 06:19 PM
    Hi,

    This might help you.

    SELECT
pack.NAME AS PACKAGE_NAME,
UPPER(c.NAME) AS OBJECT_TYPE,
COALESCE(props33.USERID, props33.NAME) OWNER,
opfolder2.NAME folder2, 
opfolder1.NAME fodler1,
opfolder.NAME folder,
obj.name OBJECT_NAME,
o.CREATED,
o.MODIFIED

FROM CMOBJECTS o
INNER JOIN CMCLASSES c on c.CLASSID = o.CLASSID
INNER JOIN CMOBJNAMES obj ON obj.CMID = o.CMID AND obj.ISDEFAULT = 1

INNER JOIN CMOBJECTS opf ON o.PCMID = opf.CMID
INNER JOIN CMOBJNAMES opfolder ON opf.CMID = opfolder.CMID AND opfolder.ISDEFAULT = 1
INNER JOIN CMOBJECTS opf1 ON opf.PCMID = opf1.CMID
INNER JOIN CMOBJNAMES opfolder1 ON opf1.CMID = opfolder1.CMID AND opfolder1.ISDEFAULT = 1
LEFT JOIN CMOBJECTS opf2 ON opf1.PCMID = opf2.CMID
LEFT JOIN CMOBJNAMES opfolder2 ON opf2.CMID = opfolder2.CMID AND opfolder2.ISDEFAULT = 1

INNER JOIN CMREFNOORD1 ref ON o.CMID = ref.CMID
INNER JOIN CMOBJECTS op ON ref.REFCMID = op.CMID
INNER JOIN CMCLASSES cp ON cp.CLASSID = op.CLASSID
INNER JOIN CMOBJNAMES pack ON pack.CMID = op.CMID AND pack.ISDEFAULT = 1
INNER JOIN CMSTOREIDS cs ON op.CMID = cs.CMID 
LEFT JOIN CMREFNOORD2 REF2 ON o.CMID = REF2.CMID
LEFT JOIN CMOBJPROPS33 props33 ON REF2.REFCMID = props33.CMID 
WHERE c.NAME IN ('dataSet2' , 'exploration' ,  'module' ,  'query' , 'report' ) 
​


    ------------------------------
    JEAM COELHO
    Cognos Solution Architect

    LinkedIn: https://www.linkedin.com/in/jeamcoelho/
    ------------------------------

    Original Message


  • 3.  RE: CA Content Store Query

    Posted Tue May 03, 2022 08:20 PM
    HI Jeam

    The first column (PACKAGE_NAME) shows invalid package names such as just numbers.

    Any idea why this is happening?

    Thanks

    Vic

    ------------------------------
    Vic Santos
    ------------------------------

    Original Message


  • 4.  RE: CA Content Store Query

    Posted Wed May 04, 2022 10:30 AM
    Hi,

    I guess that is working for most case, right?

    In rows that numbers are showing, might be model ID or model version.

    Could you send screenshot?


    ------------------------------
    JEAM COELHO
    Cognos Solution Architect

    LinkedIn: https://www.linkedin.com/in/jeamcoelho/
    ------------------------------

    Original Message


  • 5.  RE: CA Content Store Query

    Posted Thu May 05, 2022 01:53 AM
    Hi

    I sorted by PACKAGE_NAME.

    The number of rows does not match with MotioPI.

    Thanks


    ------------------------------
    Vic Santos
    ------------------------------

    Original Message


Related Content