Cognos Analytics

 View Only

  • 1.  SQL in cognos

    Posted Fri November 06, 2020 03:41 PM
    Hi all;

    I am new to cognos. I was just wondering if you can use SQL to directly generate a report in cognos, if so how. I have tried writing a simple sql statement such as select * from table_name but it gives me an error. Hence I will appreciate if someone can explain to me how to use sql to create a report in cognos.

    ------------------------------
    oba oba
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: SQL in cognos

    Posted Mon November 09, 2020 08:08 AM

    Here the IBM documentation on what can be used:

    https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ca_mdlg.doc/c_ca_mdlg_sql.html



    ------------------------------
    Dave Chirayath
    ------------------------------

    Original Message


  • 3.  RE: SQL in cognos

    Posted Mon November 09, 2020 07:06 PM
    Edited by System Fri January 20, 2023 04:38 PM
    I believe these instructions are what you are looking for:

    https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ug_cr_rptstd.doc/t_cr_rptstd_wrkdat_build_report_using_your_own_sql_mdx_rel.html

    Be aware that there are many negatives to bypassing the Cognos metadata layer in this fashion, but I find it useful when reporting across multiple schemas which are not completely represented in our models, when using more esoteric vendor-specific functions and syntax, and occasionally just for development speed to get an ad hoc report out the door. Realize that the code you create will not be portable, and may not be supportable by those who rely on a user-friendly metadata interface.

    ------------------------------
    Michael Walter
    Business Analyst
    TX HHSC
    Austin
    ------------------------------

    Original Message


  • 4.  RE: SQL in cognos

    Posted Tue November 10, 2020 06:57 AM

    All – in most Cognos shops, we use one named user to connect to a database server/instance.  Allowing a user to write custom SQL will also expose other schema data, which may otherwise be restricted.  Additionally, you will also have to grant the capability to all users to be allowed to run reports with custom SQL.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Peter Chan

    613-295--0428

     




    Original Message


  • 5.  RE: SQL in cognos

    Posted Tue November 10, 2020 08:45 AM
    knowing the error that you are getting would be really useful.

    we allow our users to use custom sql. we are an ms sql shop and the sql login used by the data source is a db_datareader. create a new report. attach a package. even if you don't use the package all reports must have at least one package. go to tools and select custom query. for the query/sql (cannot remember) select your data source and then passthrough. SELECT * works just fine. there are permissions that you must have to allow custom sql in cognos. the article is below.

    https://community.ibm.com/community/user/businessanalytics/communities/community-home/digestviewer/viewthread?MessageKey=c393a578-eaf3-43a3-82ab-c751e51de0c6&CommunityKey=6b10df83-0b3c-4f92-8b1f-1fd80d0e7e58&tab=digestviewer#bmc393a578-eaf3-43a3-82ab-c751e51de0c6

    ​

    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message