Is it possible to view the SQL used in Cognos's queries?
e.g. To get the XML definition of a report you can use the below SQL (copied from https://stackoverflow.com/a/24335760/361842):
SELECT CMOBJNAMES.NAME AS ObjName
, CMOBJECTS.PCMID
, CMCLASSES.NAME AS ClassName
, cast(CMOBJPROPS7.spec as xml) ReportDefinition
FROM CMOBJECTS
INNER JOIN CMOBJNAMES ON CMOBJECTS.CMID = CMOBJNAMES.CMID
INNER JOIN CMCLASSES ON CMOBJECTS.CLASSID = CMCLASSES.CLASSID
LEFT OUTER JOIN CMOBJPROPS7 ON CMOBJECTS.CMID = CMOBJPROPS7.CMID
WHERE CMOBJECTS.CLASSID IN (10, 37)
ORDER BY CMOBJECTS.PCMID;
... and from that XML you can often find sqltext elements giving the underlying SQL. However, where existing queries are being used it's hard to see where that data's coming from.
I'd like the equivalent of the above SQL to find Query definitions; though so far have been unable to find any such column.
Failing that, is there a way to find this definition through the UI? I looked under Query Studio and found the query's lineage which gives some information about the query columns, but doesn't make the data's source clear.
NB: By query I'm referring to those such as R5BZDDAN_GRAPH in the below screenshot from Query Studio:
... which would be referred to in a Cognos report in a way such as:
<query name="Q_DEMO">
<source>
<model/>
</source>
<selection autoSummary="false">
<dataItem aggregate="none" name="REG_REG" rollupAggregate="none">
<expression>[AdvRepData].[Q_R5BZDDAN_GRAPH].[REG_REG]</expression>
</dataItem>
<dataItem aggregate="none" name="REG_ORG" rollupAggregate="none">
<expression>[AdvRepData].[Q_R5BZDDAN_GRAPH].[REG_ORG]</expression>
</dataItem>
<!-- ... -->
UPDATE
For the benefit of others, here's an amended version of the above code for pulling back report definitons:
;with recurse
as (
select Objects.CMID Id, ObjectClasses.Name Class, ObjectNames.NAME Name
, cast('CognosObjects' as nvarchar(max)) ObjectPath
from CMOBJECTS Objects
inner join CMOBJNAMES ObjectNames
on ObjectNames.CMID = Objects.CMID
and ObjectNames.IsDefault = 1 --only get 1 result per object (could filter on language=English (LocaleId=24 / select LocaleId from CMLOCALES where Locale = 'en'))
inner join CMCLASSES ObjectClasses on ObjectClasses.CLASSID = Objects.CLASSID
where Objects.PCMID = objects.CMID --cleaner than selecting on root since not language sensitive
--where ObjectClasses.NAME = 'root'
union all
select Objects.CMID Id, ObjectClasses.Name Class, ObjectNames.NAME Name
, r.ObjectPath + '\' + ObjectNames.NAME ObjectPath --I use a backslash rather than forward slash as using this to build a windows path
from recurse r
inner join CMOBJECTS Objects
on objects.PCMID = r.Id
and Objects.PCMID != objects.CMID --prevent ouroboros
inner join CMOBJNAMES ObjectNames
on ObjectNames.CMID = Objects.CMID
and ObjectNames.IsDefault = 1 --only get 1 result per object (could filter on language=English (LocaleId=24 / select LocaleId from CMLOCALES where Locale = 'en'))
inner join CMCLASSES ObjectClasses
on ObjectClasses.CLASSID = Objects.CLASSID
)
select *
from recurse
where Class in ('report','query')
order by ObjectPath
