Someone in our Organization changed business practices and we get to go through 500+ reports that access a particular field. We've been able to at least get the report names from the back end, but because we have been on Cognos for ages we have reports scattered all over the place. Does ANYONE have DB2 SQL we can use to access the report paths from the Content manager backend? I know this has been asked before, and I've attempted to run the code suggested, but it always errors out - and what I know about DB2 would bounce on the head of a pin.
Thank you for any help you can provide!
Pamela Farr
Hi Pamela
The Cognos Audit will show you this but you can also get this from Framework Manager which has the option to check Reports impacted for any field. It will show you the report names along with the folder they are in.
Cognos Audit will show you only reports that have been run within the scope of the audit data. (For example, mine shows only usage since 1/1/2021.)
The Cognos Audit Extension is purported to show all content, regardless of whether it has been used.
Another option is to run a query against the Content Store database. I use a recursive CTE to get this from SQL Server. I don't have a query for DB2.
Based on the original poster's scenario, it makes more sense to use the in-built feature of Framework Manager which easily looks at a particular field and tells you which reports in which folder use that field.
Open the FM Model, click on the field in the Database Layer, from the Tools menu, select Find Report Dependencies. As simple as that.
That assumes the OP is using Framework Manager models and packages exclusively, which they did not say. If a data module using a data server is in play, that won't work. Of course, in that case the variability involved will cause trouble using a query to inspect the model specs (json), related data set specs (xml), and any related report specs (xml) to identify where the column is used. (Although, I think I had to do something like that once.)
Quote from: dougp on 22 Feb 2023 10:42:08 AM
That assumes the OP is using Framework Manager models and packages exclusively, which they did not say. If a data module using a data server is in play, that won't work. Of course, in that case the variability involved will cause trouble using a query to inspect the model specs (json), related data set specs (xml), and any related report specs (xml) to identify where the column is used. (Although, I think I had to do something like that once.)
Didn't the OP say that they have had those reports for ages? As we all know, data modules is a new feature which didn't exist earlier so it is an intelligent guess to assume they used FM.