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.  How can Cognos users run reports created via SQL

    Posted 08/13/20 12:17 PM

    I've had a couple of situations when I needed to use SQL to fulfill the requirements of a Cognos query.  See snapshot.  I can run scheduled reports using this method with no problem, but Cognos users (people who do not have report creation authority) cannot run these reports.  They get an authorization error.  I have seen comments on the web that say it is possible, but have not been able to find the report options/authorizations to allow users to run reports created this way.  Does anyone know how to accomplish this?

     

    Does this section under Properties->Report->Advanced->Run have anything to do with it?

     

    Jerome P Roberts

    IT Specialist

    Philips Oral Healthcare - Los Angeles

    jerome.roberts@philips.com

    Description: <a href=image002.jpg@01CF4423.BA6A3360">

     


    The information contained in this message may be confidential and legally protected under applicable law. The message is intended solely for the addressee(s). If you are not the intended recipient, you are hereby notified that any use, forwarding, dissemination, or reproduction of this message is strictly prohibited and may be unlawful. If you are not the intended recipient, please contact the sender by return e-mail and destroy all copies of the original message.

    #CognosAnalyticswithWatson


  • 2.  RE: How can Cognos users run reports created via SQL

    Posted 08/13/20 01:41 PM
    @jerome roberts

    You have to go to Capabilities and give user/group ​(Execute and Traverse) permission for User Defined SQL

    Cognos Admin/Security/Capabilities/Report Studio/User Defined SQL



  • 3.  RE: How can Cognos users run reports created via SQL

    Posted 08/13/20 01:55 PM

    Thanks.  So that would be for every Cognos group I have or at least the group that the user is in that needs to run the report?  Or maybe create a special group for that and add those users to that group too?

     

    Jerome P Roberts

    IT Specialist

    Philips Oral Healthcare - Los Angeles

    jerome.roberts@philips.com

    Description: <a href=image002.jpg@01CF4423.BA6A3360">

     


    The information contained in this message may be confidential and legally protected under applicable law. The message is intended solely for the addressee(s). If you are not the intended recipient, you are hereby notified that any use, forwarding, dissemination, or reproduction of this message is strictly prohibited and may be unlawful. If you are not the intended recipient, please contact the sender by return e-mail and destroy all copies of the original message.



    Original Message


  • 4.  RE: How can Cognos users run reports created via SQL

    Posted 08/13/20 01:59 PM
    If you want all cognos users to have this capability (which is fine), you can simply assign it to the All Authenticated Users group, you do not have to add every single group.
    Original Message


  • 5.  RE: How can Cognos users run reports created via SQL

    Posted 08/13/20 02:07 PM

    Ah!  Thanks.

     

    Jerome P Roberts

    IT Specialist

    Philips Oral Healthcare - Los Angeles

    jerome.roberts@philips.com

    Description: <a href=image002.jpg@01CF4423.BA6A3360">

     


    The information contained in this message may be confidential and legally protected under applicable law. The message is intended solely for the addressee(s). If you are not the intended recipient, you are hereby notified that any use, forwarding, dissemination, or reproduction of this message is strictly prohibited and may be unlawful. If you are not the intended recipient, please contact the sender by return e-mail and destroy all copies of the original message.



    Original Message


  • 6.  RE: How can Cognos users run reports created via SQL

    Posted 08/14/20 11:09 AM
    I would suggest to embed Native SQLs in one Data module and use it for all reporting so that same SQL can be reused across reports. Then you don't need to enable capability at Report level.



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

    Original Message


  • 7.  RE: How can Cognos users run reports created via SQL

    Posted 08/14/20 11:28 AM

    I don't understand that suggestion, 'embed Native SQLs in one Data module and use it for all reporting so that same SQL can be reused across reports.'  How do you use same SQL across all reports?  Can you refer me to a doc or something?

     

    Jerome P Roberts

    IT Specialist

    Philips Oral Healthcare - Los Angeles

    jerome.roberts@philips.com

    Description: <a href=image002.jpg@01CF4423.BA6A3360">

     


    The information contained in this message may be confidential and legally protected under applicable law. The message is intended solely for the addressee(s). If you are not the intended recipient, you are hereby notified that any use, forwarding, dissemination, or reproduction of this message is strictly prohibited and may be unlawful. If you are not the intended recipient, please contact the sender by return e-mail and destroy all copies of the original message.



    Original Message


  • 8.  RE: How can Cognos users run reports created via SQL

    Posted 08/14/20 03:04 PM
    • Create a Data Module with required Data Server and Packages as Source
    • Create query Subject using "Native SQL" and put Native SQL in it. Repeat it for all Native SQL queries you have in report
    • In Report, point it to Data Module instead of Package
    • Remap Native SQL based queries to Native SQL Query Subjects created in Data Module
    • Now if same query is needed in other report, you can just copy Query from report to other report.

    Hope this helps.

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

    Original Message


  • 9.  RE: How can Cognos users run reports created via SQL

    Posted 08/14/20 12:00 PM

    Why don't any of the options under properties->report->advance options

     

    Run

        With user credentials

        With owners capabilities

        As ....my name.....

     

    Work?

     

    I checked the Permission tab under properties, and it already shows Everyone with Read, Execute, Traverse.  It kind of looks like having Write permission might be the key.

     

    Jerome P Roberts

    IT Specialist

    Philips Oral Healthcare - Los Angeles

    jerome.roberts@philips.com

    Description: <a href=image002.jpg@01CF4423.BA6A3360">

     


    The information contained in this message may be confidential and legally protected under applicable law. The message is intended solely for the addressee(s). If you are not the intended recipient, you are hereby notified that any use, forwarding, dissemination, or reproduction of this message is strictly prohibited and may be unlawful. If you are not the intended recipient, please contact the sender by return e-mail and destroy all copies of the original message.





Related Content