Cognos Analytics

Cognos Analytics

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


#Product
#Analytics
#CognosAnalytics
#Analyticstools
#TechXchange Partner
#TechXchange Session
#TechXchange Presenter
#AI
 View Only

  • 1.  SQL in cognos

    Posted 11/06/20 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 11/09/20 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
    ------------------------------



  • 3.  RE: SQL in cognos

    Posted 11/09/20 07:06 PM
    Edited by System Admin 01/20/23 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
    ------------------------------



  • 4.  RE: SQL in cognos

    Posted 11/10/20 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 11/10/20 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
    ------------------------------



Related Content