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.  Pass Report Name to SQL

    Posted 11/25/19 08:45 AM
    Hello - I'm looking for a token/macro to insert the name of the currently executing report into the SQL sent to the database.
    I will be using this in the Framework Manager to provide a filter on a relational (Oracle) database table.

    Thanks for any suggestions.
    Opher

    v: Cognos BI 11.0.13

    ------------------------------
    Opher Banarie
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Pass Report Name to SQL

    Posted 11/26/19 12:54 PM

    Hi,

    Look at Cognos Administration Guide, page 134. Section Using application context in Dynamic SQL

    https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ug_cra.doc/ug_cra.pdf

    It will work for you.


    Regards.
    Jeam Coelho



    ------------------------------
    JEAM COELHO
    ------------------------------



  • 3.  RE: Pass Report Name to SQL

    Posted 11/26/19 05:21 PM
    Thank you, Jeam. I hadn't seen this before.
    The document appears to apply for creating comments in the SQL for logging purposes.
    I'm trying to get the Report Name into the actual executed SQL to filter a table.
    Not sure this approach will work. I'll have to investigate further.
    Thanks again,
    Opher

    ------------------------------
    Opher Banarie
    ------------------------------

    Original Message


  • 4.  RE: Pass Report Name to SQL
    Best Answer

    Posted 11/27/19 01:02 PM
    Hi Opher,

    You can use:  the sessionParameter $report
    e.g.  #sq($report)#
    to select a particular table or query subject you could use
    # case $report
    when 'abc' then 'table_1'
    else 'table_2'
    end
    #

    // Henk

    ​​

    ------------------------------
    HENK CAZEMIER
    ------------------------------

    Original Message


  • 5.  RE: Pass Report Name to SQL

    Posted 11/27/19 01:25 PM
    Thanks, Henk. I'll look into that.

    Opher

    ------------------------------
    Opher Banarie
    ------------------------------

    Original Message


  • 6.  RE: Pass Report Name to SQL

    Posted 11/28/19 11:27 AM
    Edited by System Admin 01/20/23 04:12 PM
    There is a function ReportName( ) which is available inside Report Studio, you can use this function to create a data item.

    ------------------------------
    satish attili
    ------------------------------

    Original Message


  • 7.  RE: Pass Report Name to SQL

    Posted 11/29/19 04:19 PM

    Hello Satish - ReportName() is a layout function to display the report name in the body of the report output. I don't believe it can be used in the SQL for filtering.
    Regards,

    Opher



    ------------------------------
    Opher Banarie
    ------------------------------

    Original Message


  • 8.  RE: Pass Report Name to SQL

    Posted 11/29/19 06:59 PM

    See chapter 4 http://www.redbooks.ibm.com/redbooks/pdfs/sg248121.pdf

    Discusses various items re macros and session variables. Material should be in the information center as well.



    ------------------------------
    NIGEL CAMPBELL
    ------------------------------

    Original Message


  • 9.  RE: Pass Report Name to SQL

    Posted 12/02/19 09:07 AM
    Not sure if this will meet your needs.

    We have  added two custom 'audit' query items to all our reports as shown below:
    1. ReportRunBy: With logic as #sq($account.personalInfo.userName)# +' - '+ #sq($account.personalInfo.givenName)# +' '+ #sq($account.personalInfo.surname)#
    2. ReportName: With logic as 'Report's name' (The reason for this is, we use report views and our customers  copy them and name the report views with their own custom name in their 'My Content')
    Then in the report page, on the list properties, we add the two Audit columns in. When a user executes the report, the SQL will contain both info.

    List Properties Menu


    Hope this helps

    Thanks

    ------------------------------
    Sanish John
    ------------------------------



Related Content