I have uploaded the file CM Objects.sql.txt. Copy and paste it into an MS SQL SSMS query window and run under the context of your CM database. Read the comment box at the top of the script, which i have copied below. Also read the comment box at the top of each sproc for additional information and usage examples.
/******************************************************************
Run this entire script against the CM database to create the following list of
objects in the CM database. Once the script runs successfully and the objects
are created, then you can run the sprocs using the examples below.
More detailed examples are provided in the comments box at the top of each sproc.
Procs
=====
cm_Create_Inventory
cm_SearchForTextInReports
cm_ReplaceTextInReports
Functions
=========
fn_SPlitter
cm_GetParentPath
Tables
======
cm_Cognos_Inventory
cm_Cognos_Inventory_Temp
Sproc Examples - additional examples at the top of each sproc
================================================
1) cm_Create_Inventory – stored procedure. Writes each object to the table cm_Cognos_Inventory with each
record set having a unique datetime, sequence number, and @note. look at comment box at top of
cm_Create_Inventory to see suggested queries to run after cm_Create_Inventory finishes.
-- run the sproc to create the inventory and pass a note
exec cm_Create_Inventory @Note='2018_02 before any 2019_01 changes'
2) cm_SearchForTextInReports – stored procedure - look at comments box at top of sproc for further examples of input parameters
exec cm_SearchForTextInReports '[Account Attributes].[GL Type Code]'
3) cm_ReplaceTextInReports – stored procedure - look at comments box at top of sproc for further examples of input parameters
-- make Backup of CM database first
-- run in NO UPDATE mode first. Only searches for first string matches
exec cm_ReplaceTextInReports '[Account Attributes].[GL Type Code]', '[Account Attributes].[GL Type Code TypeZ]'
-- run in update mode after verifying results of NO UPDATE mode. Believe result set shows all matches of second string
-- and not necessarily what was changed. Cannot remember.
exec cm_ReplaceTextInReports '[Account Attributes].[GL Type Code]','[Account Attributes].[GL Type Code TypeZ]', 1
******************************************************************/
------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------
Original Message:
Sent: Tue December 10, 2019 09:10 AM
From: brenda grossnickle
Subject: List of all reports
The free MotioPI tool I think will do that for you. We use MotioPI to validate reports. Some of our clients use it to find emailed reports and emailed recipients. The free tool is like a Swiss army knife. It provides several useful functions. They have a good websites with several How To write ups and videos.
IBM has (had ??) the Dynamic Report Updater tool (DRU) that I believe I have used on C11. It is pretty techie and not easy to use but it will update report text (think field name changes or removing "<DimensionInfo>something</DimensionInfo>" for C11 upgraded reports) allowing you to use Regular Expressions. When I have to update a lot of reports, a long string, something that requires Regular Expression, or something that might have an embedded CR or LF I use this tool.
I have several MS SQL stored procedures that I can share if you want. They run again the CM database on the MS SQL Server. I think (3) is probably what you are looking for. We have used them on 11.0.9 and 11.0.11. They could be scheduled to run in MS SQL or executed from a Cognos report if wanted (which is a whole setup for executing SQL Agent jobs from reports). We execute them manually from MS SQL as needed.
1) cm_SearchForTextInReports - has several input parameters that control if the report definition should be returned, limit to a set list of CMIDs, show a preview for each text match, return a result set. if you set the search text as 'expressionLocale=' it produces a list of all reports and queries. I cannot remember if it returns My Folder reports but I believe it does.
it returns a row for each report: owner, classid, reportname, directorypath, cmid, pcmid, optional reportdefinition, created datetime, modified datetime, disabled, ishidden
2) cm_ReplaceTextInReports - almost identical to (1) except it replaces text. It does not account for search text that is broken up in the definition with a CR or LF, which I have found not to be an issue unless you have a really long text string that you are searching for.
3) cm_CreateInventory - it produces a SQL table or inserts into an existing table a list of all Cognos objects. It creates a sequence number, datetime, and inputted comments for each time that it is run. We run this on our integration server before and after testing for each release. Then compare the before rows with the after rows to make sure no folders, packages, reports, etc were accidentally deleted/added/changed.
------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
Original Message:
Sent: Mon December 09, 2019 09:16 AM
From: Veerle Hemeryck
Subject: List of all reports
Hi
I was looking for a way to make a list of all reports in Cognos Team Content.
It would be handy to run that 'All reports' report every day automatically to have an updated list off all reports.
Does anybody know a way of doing that with the Cognos audit package?
Thank you
------------------------------
Veerle Hemeryck
------------------------------
#CognosAnalyticswithWatson