List of Reports that Query Certain Views

[dlwearl]

I'd like to generate a list of report titles that query a specific view within a given package. Is this possible?

For instance: "Show me a list of report names in [package] that query from the Admissions Application view object."

Thanks.

[MFGF]

I'd like to generate a list of report titles that query a specific view within a given package. Is this possible?

For instance: "Show me a list of report names in [package] that query from the Admissions Application view object."

Thanks.

I think this is something you could get using MetaManager. I expect one of the Admins will reach out to you with details.

Cheers!

MF.

[dougp]

Is the query subject name the same as the database object name?  If so...

Inspecting the report spec for instances of [Admissions Application] query subject could produce false positives.  It would pick up anywhere a report developer used that as the name of a query.  You would need to inspect the report spec for the existence of [namespace].[Admissions Application] for all namespaces that use it.  If you are only looking in reports...

Code Select Expand


        cast(CMOBJPROPS7.SPEC as varchar(max)) like '%[namespace].[Admissions Application]%'


If you need to know which reports, datasets, data modules, or dashboards are using it, that gets more complicated -- especially when you include data modules because variable-depth nesting and recursion can occur.  Searching data modules for specific dependencies once upon a time, I had a recursive CTE that looped infinitely because of a self-referencing data module.  Be careful.  Know how your data flows.  Don't overload the Content Store database and crash Cognos.

Searching a very large string (the report spec) for the existence of a specific, small string across thousands of report specs will take some time.  Be careful to not cause resource load problems in the Content Store database.
Using the STOREID (quoted to narrow the search) in the search bar in Cognos will find it.
You can also write a recursive CTE to get the full path to each report.  I'll leave that to you since me doing all of the work for free isn't fair to me or to 3rd party tool vendors of products like Meta Manager or Motio.