Cognos Analytics

Cognos Analytics

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

 View Only

  • 1.  Data Module Table using SQL

    Posted Mon April 27, 2020 12:29 PM
    Hi All

    Is anyone able to use "Cognos SQL" in "Table using SQL" interface?  I don't see any option to fetch existing data module objects here.


    Thanks for any inputs.

    ------------------------------
    Buddhi Gupta
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Data Module Table using SQL

    Posted Mon April 27, 2020 01:10 PM
    Hi @Buddhi Gupta,

    If you are in 11.1.5 or onwards, you can click on "Show query information" in the context menu for a table you are interested in using which will allow you to see the SQL behind it. You can copy/paste this and use it as a starting point when you ​create a Table using SQL. There is no link between this new table created using SQL and the existing table, they are two independent tables using the same SQL query. This will allow you to reuse tables that already exist in your data module.

    ------------------------------
    MOHAMMED OMAR KHAN
    Offering Manager
    IBM Cognos Analytics
    ------------------------------

    Original Message


  • 3.  RE: Data Module Table using SQL

    Posted Tue April 28, 2020 02:18 PM
    Hi
    I think my question was not clear enough. When I am inside Window "Table Using SQL", there are 3 options (similar to FM)
    > using Cognos SQL
    > Native SQL
    > Pass through

    I am not sure how can I use Cognos SQL option while using SQL and existing objects.

    Thanks!

    ​

    ------------------------------
    Buddhi Gupta
    ------------------------------

    Original Message


  • 4.  RE: Data Module Table using SQL

    Posted Wed April 29, 2020 06:45 PM
    My reply is similar to Mohammed's:

    > using Cognos SQL  - I use this when grabbing the SQL Cognos creates.  Cognos automation uses a lot of with statements and the code is more complicated.  I often use this when pulling data from one data mart into a report based on another data mart.  ie: HR information to Financials information   For example, I'll write a Cognos report grabbing the information I need from HR.  Copy the Cognos SQL to the buffer from " Show Query Information".  Open the other Financials report and paste the HR SQL Cognos wrote.  
    > Native SQL - I use this when writing my own SQL using Toad or some other tool to query the data tables directly.  This code tends to be much more concise. 
    > Pass through - I've never used this feature, so I can't speak to this.  

    ------------------------------
    Jeanie Krieger
    Business Analyst
    California State University, Sacramento
    Sacramento CA
    ------------------------------

    Original Message


  • 5.  RE: Data Module Table using SQL

    Posted Wed April 29, 2020 09:57 PM
    Hopefully the screenshots will help:


    As Jeanie pointed out, once you hit click Show query information on the table you want, you can see it's Cognos SQL which can be copied:

    You can then paste that in Table using SQL and start using it right away:


    ------------------------------
    MOHAMMED OMAR KHAN
    Offering Manager
    IBM Cognos Analytics
    ------------------------------

    Original Message


Related Content