HomeCognos › Cognos Content Store: Retrieving Information

Cognos Content Store: Retrieving Information

Here is a  sample on getting package and report information directly from the content store.

cmobjects Croatians a parent child relationship of all objects, to obtain the full  path of an object we need a Recursive function to resolve this, let us say getpath(Parent_cmID),         a sample exist at the end of this Port.

To get Information about Published packages

SELECT cls.NAME class_name, n.NAME object_name, o.pcmid, o.cmid,
getpath (o.pcmid)
FROM cmobjnames n, cmobjects o, cmobjprops36 c, cmclasses cls
WHERE o.cmid = n.cmid
AND o.cmid = c.cmid
AND o.classid = cls.classid
AND isdefault = 1;

To get information about Reports and their specifications

SELECT cls.NAME class_name, n.NAME object_name, o.pcmid, o.cmid,
getpath (o.pcmid), c.spec
FROM cmobjnames n, cmobjects o, cmobjprops7 c, cmclasses cls
WHERE o.cmid = n.cmid
AND o.cmid = c.cmid
AND o.classid = cls.classid
AND isdefault = 1
AND UPPER (cls.NAME) = ‘REPORT’;

Sample Recursive function

CREATE OR REPLACE FUNCTION getpath (p_pcmid IN NUMBER)
RETURN NVARCHAR2
IS
l_parentname   NVARCHAR2 (100);
l_pcmid        Number;
BEGIN
IF p_pcmid = 0
THEN
RETURN ‘root’;
ELSE
SELECT n.NAME object_name, o.PCMID
INTO l_parentname, l_pcmid –, cls.NAME class_name, o.CMID,
FROM cmobjnames n, cmobjects o
WHERE o.cmid = n.cmid AND o.cmid = p_pcmid and isdefault=1;
END IF;

RETURN getpath (l_pcmid)||’\’||l_parentname;
EXCEPTION
WHEN OTHERS
THEN
RETURN sqlerrm;
END getpath;

Comments are closed.