Content Store Query - Report name, Package name and Data source name

[Cognos KW]

Hi Gurus,

Can someone please help me with content store SQL query to get Report name, Package name and Data source name used in the report.

I already have a query to get the report and package name.

Thanks in advance.

Regards,
KW

[sunosoft]

Hi KW,

Can you please share the query to get report name and package name ?

[Cognos KW]

Hi Sunosoft,

Attached is the query. You might need some modification in the query and also change the mapdlocaleid.

Cheers,
KW

[sunosoft]

Thanks a lot. Will try and let you know.

[RossBrown]

I don't think the Content Store tables directly track which data sources the reports are using.  The Reports are stored in XML, and the XML has Cognos MDX style references to framework model objects.  Uniquely identifying a list of all data sources would probably be a little hairy, depending on your naming structure in your framework model.

What I have done is look up names of model sources within the XML, and find which reports have XML which includes something like '[Sales Detail]'.  This is not 100%... its possible for Field names, Namespace names, or anything else that could be contained in brackets to have the same name as the object you are searching for.  Also, two different data sources may be feeding the same table name to two different namespaces within your Package.

For example, if you have an archival database for pulling old data, with its own data source, and a seperate data source for current data, you could have one package, with two different Sales Detail tables in it.

But if you know what is in your model, finding that one obscure report that refers to your '[CONTRACT_LABOR_OLD]' object could be achieved.

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)
  --and upper(SPEC) like '%[SALES DETAIL]%'
ORDER BY CMOBJECTS.PCMID

[Cognos KW]

Thanks Guys.

I end up using SDK to get the list. Still need some modification to get everything from SDK.

Cheers,
KW

[GeethaKL]

Hi Cognos KW,
If at all you have got the code ready for the List of all cubes, Packages, Dimensions and the Report Names against the Cognos Content store database, Please post here.
That will help me to go in right direction.

Thanks you and everyone else

Regards
Geetha

[GeethaKL]

Hi All,
Finally my team came out with below to get it working

With CognosContent AS (

Select a.CMID, a.PCMID, b.NAME, b.Name as Parent, c.name as Content

,Cast(b.NAme  as varchar(max)) as path

, 0 as level

from cmObjects a

inner join CMOBJNAMES b on a.CMID = b.CMID

inner join CMCLASSES c on a.CLASSID = c.CLASSID

Where b.ISDEFAULT = 1

and a.PCMID =0

and a.CMID = 2



UNION ALL

Select a.CMID, a.PCMID, b.NAME as parent, d.NAME, c.NAME

, d.path + '\' +  CAST (b.Name as varchar(Max))  as path

, d.level + 1 as level

from cmObjects a

inner join CMOBJNAMES b on a.CMID = b.CMID

inner join CMCLASSES c on a.CLASSID = c.CLASSID

inner join CognosContent d on a.PCMID = d.CMID

Where b.ISDEFAULT = 1

)



Select NAME as ReportName, path, level, content from CognosContent

Where

Content = 'report' or Content = 'dashboard' or content = 'analysis'
order by path


Regards

[jj882]

Hi All,

I have a question in relation to the above.

I am trying to join to the correct tables in order to return the original report author, or rather the 'owner' of the report.

Has anyone tried to do this previously?

Thanks all!

[germanno]

Hi @GeethaKL This example works fine on Cognos 11.1.7  . How can I add the owner of those report, the person that created