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
Hi KW,
Can you please share the query to get report name and package name ?
Hi Sunosoft,
Attached is the query. You might need some modification in the query and also change the mapdlocaleid.
Cheers,
KW
Thanks a lot. Will try and let you know.
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
Thanks Guys.
I end up using SDK to get the list. Still need some modification to get everything from SDK.
Cheers,
KW
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
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
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!
Hi @GeethaKL This example works fine on Cognos 11.1.7 . How can I add the owner of those report, the person that created