Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only
  • 1.  List of all reports

    Posted Mon December 09, 2019 09:37 AM
    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


  • 2.  RE: List of all reports

    Posted Mon December 09, 2019 09:59 AM

    Keep in mind that the Audit system only logs reports that are executed. So any report not executed during the window you are reporting will not be included.
    I think Motio sells a module that will expose all reports, but you'll have to contact them for details.
    Good luck,
    Opher



    ------------------------------
    Opher Banarie
    ------------------------------



  • 3.  RE: List of all reports

    Posted Mon December 09, 2019 05:21 PM

    ​Hi Veerle,

    As @Opher Banarie mentioned, Motio PI Pro or, similarly, MetaManager will be your best bet for this.

    We are also in the process of adding quick and easy retrievals like yours to our product, Thrive (PMsquare.com/Thrive) but it will be some time before that feature is live.
    ​​
    Good luck!



    ------------------------------
    Greta Brauer
    www.PMsquare.com
    gbrauer@pmsquare.com
    ------------------------------



  • 4.  RE: List of all reports

    Posted Mon December 09, 2019 10:06 PM

    Hello Veerle,

    Standard audit reports show what reports have been executed. Audit Extension includes information on the contents of your environment, including reports and regardless if they have been run or not. The Audit Extension is a fantastic tool for keeping a track of reports in your system, along with other information like dispatcher tuning settings, account information, and security settings. 

    When you run the Audit Extension, it writes the current state to a database. So you can schedule it to run daily and have a great store of comparative data to help you understand how your environment is changing over time. Used in conjunction with standard Auditing, Cognos Administrators can have a great view into how the system is being used and make decisions about the environment health before users are impacted. 

    https://developer.ibm.com/tutorials/ibm-cognos-11-audit-extension/

    Everything you need is included under the resources dropdown on the webpage above. This includes the application itself along with sample models and reports to get you well on your way to getting a better understanding of your environment and contents. 

    If you have a complex environment or need assistance to deploy the application, IBM services have technical experts to assist. 

    Regards.
    Luke.



    ------------------------------
    Luke Peterson
    Business Analytics Advocate
    IBM
    ------------------------------



  • 5.  RE: List of all reports

    Posted Tue December 10, 2019 09:10 AM
    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
    ------------------------------



  • 6.  RE: List of all reports

    Posted Tue December 10, 2019 04:25 PM
      |   view attached
    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
    ------------------------------

    Attachment(s)

    txt
    CM Objects.sql.txt   28 KB 1 version


  • 7.  RE: List of all reports

    Posted Wed December 11, 2019 03:13 AM
    Hi

    A few years ago I built an FM model and package to do this - will provide the list of reports and by joining to the audit reports, and also a list of reports built that have never been run.
    I'll see if I have a copy of it somewhere

    ------------------------------
    andy ellis
    ------------------------------



  • 8.  RE: List of all reports

    Posted Wed December 11, 2019 02:16 PM
    Edited by System Admin Fri January 20, 2023 04:49 PM
    Hi Veerle,

    You should be able to accomplish your requirement using evaluation version of the MotioPI  tool and should be able to export output to csv . The professional version is not very expensive either ( few hundred dollars ) and is very useful for Cognos Administration . I used the tool to get list of all scheduled reports which are scheduled using Trigger vs Time based .

    Thanks

    ------------------------------
    Madhukar
    ------------------------------