Cognos Analytics

 View Only

  • 1.  Export all Report XMLs - CA 11.1.7

    Posted Thu May 18, 2023 02:39 PM

    I am looking to export all our report XML's from the Content Store and store in BitBucket so we can create a new repository of all our reports.

    This should enable us to have version control and a standard location for all our developers to pull the latest version of the report.

    We have an Oracle Content Store - I have tried creating a SQL against the Content Store, but I am running into problems parsing the CLOB column that the specification is defined. The maximum characters to convert from CLOB to Varchar seems to be around 4000, but the maximum report is 1+ million characters. I could manually create 500+ columns using substring, but then need a way to concatenate those columns together without losing any characters.

    If anyone has any better thoughts on how to accomplish this, I would greatly appreciate it.

    Thanks in advance,
    Adam.

       



    ------------------------------
    Adam McIlravey
    ------------------------------


  • 2.  RE: Export all Report XMLs - CA 11.1.7

    IBM Champion
    Posted Thu May 18, 2023 03:43 PM

    Almost ten years ago I wrote a solution for this: https://cognospaul.com/2014/03/11/export-report-xmls-file-system-updated/ It's based on T-SQL, but the logic for creating and stitching the substring columns is handled in the VBA. 

    I also have two solutions in the premium version of CogBox that might help

    The first is a context menu item on folders and packages to download all contained specs:

     It will loop through requesting every report in the folder and create a zip of the specs.

    The second is from the Object Search tool

    I'm not 100% sure how it will handle thousands of reports, I may have to modify it to split the request into multiple chunks. 



    ------------------------------
    Paul Mendelson
    ------------------------------

    Original Message


  • 3.  RE: Export all Report XMLs - CA 11.1.7

    Posted Fri May 19, 2023 08:28 AM

    Hi Adam,

    We built a solution at Senturus called the "Migration Assistant".  It scans the content store and creates a new database based on your Cognos data.  You can then analyze your reports, models, and users anyway you can think of, as well as creating a report inventory with full report XML.  Check out the link below, there is an interactive demo and video that provide more details.

    Cognos Migration Tool For Data and Platform Moves | Senturus 

    -Todd



    ------------------------------
    Todd Schuman
    ------------------------------

    Original Message


  • 4.  RE: Export all Report XMLs - CA 11.1.7

    Posted Thu May 25, 2023 04:26 PM
    Edited by Charles Blake Thu May 25, 2023 04:32 PM

    If you have access to the SDK you don't need these 3rd party tools:

    https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=developing-software-development-kit-developer

    You can take the already existing Cognos Java SDK (could use C# too), search all the reports, use it to write the report XMLs to a file, then read the search path to recreate the folder structure into a local folder. Then, just create a script to commit the files to git / Bitbucket. Then schedule it with a CI/CD tool like Github Actions, Azure Devops, Jenkins, etc. I'm not a Java developer by trade but was able to figure it out. If it sounds like I've done this before, I have but with the FM packages...100% possible to do but would require getting familiar with building command line Java apps from the Java SDK

    The new REST API can do some of this but in my experience it can't query more than 1 thing easily because IMO IBM didn't design it right, like you navigate it using Store IDs but all of the API responses give you the SearchPath of other related items, so it's not possible to traverse the folder structure easily unless you already have a list of all the report store IDs


    ------------------------------
    Charles Blake
    ------------------------------

    Original Message


  • 5.  RE: Export all Report XMLs - CA 11.1.7

    Posted Fri May 26, 2023 07:23 AM
    You could try this in your content store query to split up the command. Maybe you need an extra order by column to ensure the XML result is in the correct order.
     
    REPORT_SPEC as (
    select 
     CMID
    ,SPEC_VAR 
    from 
    (select *
     from (
    -- split in pieces of 3000
    select cmid, dbms_lob.substr(spec,3000,1) as SPEC_VAR from cmobjprops7 where 1=1 and spec is not null union
    select cmid, dbms_lob.substr(spec,3000,3001)    from cmobjprops7 where 1=1 and spec is not null union
    select cmid, dbms_lob.substr(spec,3000,6001)    from cmobjprops7 where 1=1 and spec is not null union
    ...
    ...
    select cmid, dbms_lob.substr(spec,3000,497001)  from cmobjprops7 where 1=1 and spec is not null union
    select cmid, dbms_lob.substr(spec,3000,500001)  from cmobjprops7 where 1=1 and spec is not null 
    )
     where 1 = 1
     and SPEC_VAR is not null
     )
    )
     
    You could also create a export deployment with the reports. Open the zip file and you will find the report specifications in the file 'package1,2,3.xml' files.

    Then you still have to create script to split this xml files into separate files.



    ------------------------------
    Thomas van der Meer
    ------------------------------

    Original Message


  • 6.  RE: Export all Report XMLs - CA 11.1.7

    Posted Fri May 26, 2023 08:59 AM

    We ended up solving this in the following manner:

    1. Create a Content Store export of the specific folders containing the reports we needed.
    2. Extract the Package.xml file from within the export.zip file that was created on the Content Manager server.
    3. Using a C# script, parse the package.xml file to create individual, specific report.xml files and save them off to a directory.

    In our parsing there were some object type that we could not parse out, but these were such a small percentage that we can handle these manaually and get the .xml thru Cognos Connection.

    Thanks again for everyone's help on this matter,
    Adam.



    ------------------------------
    Adam McIlravey
    ------------------------------

    Original Message