Cognos Analytics

 View Only
Expand all | Collapse all

Dashboard - Drill Through Details via SQL ?

  • 1.  Dashboard - Drill Through Details via SQL ?

    Posted Mon June 01, 2020 10:27 AM
    Hello All,

    Please let me know if a Dashboard Drill Through be built based on the SQL Query passed as a parameter (or) from a Database Table with Select Table Name, Column Names and Filter conditions passed as parameters? 

    Thank You

    ------------------------------
    Harun Y
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Dashboard - Drill Through Details via SQL ?

    Posted Thu June 04, 2020 11:48 AM
    Edited by System Fri January 20, 2023 04:50 PM
    Please let me know if anyone has any idea or if my question is not clear enough?

    Appreciate your response. Thank You.

    ------------------------------
    Harun Y
    ------------------------------



  • 3.  RE: Dashboard - Drill Through Details via SQL ?

    Posted Sat August 01, 2020 08:42 PM
    Edited by System Fri January 20, 2023 04:46 PM
    Please let me know if anyone has any idea or if my question is not clear enough?

    "Please let me know if a Dashboard or Report Drill Through be built based on the SQL Query passed as a parameter (or) from a Database Table with Select Table Name, Column Names and Filter conditions passed as parameters? 

    ------------------------------
    Harun Y
    ------------------------------



  • 4.  RE: Dashboard - Drill Through Details via SQL ?

    Posted Wed August 05, 2020 12:20 PM
    Hi,

    Your question is not clear.

    What are you looking for?

    Parameters are sent to target drill thru report dynamically, based on user selections.

    And drill thru specifications are salved on report spec (XML).

    ------------------------------
    JEAM COELHO
    Cognos Solution Architect

    Linkedin: https://www.linkedin.com/in/jeamcoelho/
    ------------------------------



  • 5.  RE: Dashboard - Drill Through Details via SQL ?

    Posted Wed August 05, 2020 01:06 PM
    Edited by System Fri January 20, 2023 04:45 PM
    Thank You @JEAM COELHO​ for the response. Let me elaborate 

    I want to build a List Report (which will be used as the target drill thru Report) which should display the results based on certain parameters that I pass from a Parent Report.

    I am looking for the options for the List Report to work in either of these ways

    1. I want to pass the Database Table Name, Column Names, Date Filter condition as parameters to the List Report via the Parent Report 

    Parameters: Database Table Name, Filter Condition, Database Columns 
    As an Example : Database Table Name = "Customer"
                               Filter Condition: "ActiveDate='2020-08-05'
                                Database Ciolumns: ColumnA, ColumnB, ColumnC

    I want the List report to display the results from the Table with a SQL Equivalent of:
    SELECT * FROM Customer WHERE ActiveDate='2020-08-05'
                   {OR}
    SELECT ColumnA, ColumnB, ColumnC FROM Customer WHERE ActiveDate='2020-08-05'

    2. I can pass the SQL Query and Date Value via Parent Report to the Drill Thru Report (List Report)

    Parameters: Database_SQL_Statement, Date_Filter_Value
    As an Example : Database_SQL_Statement = "SELECT ColumnA, ColumnB, ColumnC FROM Customer WHERE ActiveDate="
                               Date_Filter_Value="'2020-08-05"

    I want the List report to display the results from the Table with a SQL Equivalent of:

    SELECT ColumnA, ColumnB, ColumnC FROM Customer WHERE ActiveDate='2020-08-05'

    3. I can pass the whole SQL Query as Parameter via Parent Report to the Drill Thru Report (List Report)

    Parameters: Database_SQL_Statement
    As an Example : Database_SQL_Statement = "SELECT ColumnA, ColumnB, ColumnC FROM Customer WHERE ActiveDate='2020-08-05'"
                              
    I want the List report to display the results from the Table with a SQL Equivalent of:

    SELECT ColumnA, ColumnB, ColumnC FROM Customer WHERE ActiveDate='2020-08-05'


    It does not have to be a List Report I basically want the users to see the detailed results either through List Report Report or any other way possible.

    Hopefully I am clear this time.

    Thank You

    ------------------------------
    Harun Y
    ------------------------------



  • 6.  RE: Dashboard - Drill Through Details via SQL ?

    Posted Wed August 05, 2020 03:17 PM
    So, you wanna a completely customizable report target! Okay!

    In your Source (parent) report, you can create a data item and add as a property in a list, for example, that has your SQL code. 
    You can write a SQL concatenating text with data items values in a calculated data item....

    Then, pass this that item in a drill thru parameter.

    So, in target report, create a SQL query that receives/reads this parameter as SQL Code. You may achieve this using query macros, like this:

    #prompt('ParameterName' , 'token' )#​

    ​Note that for using single quotes for filters in query macros, you may use several single quotes like ''''2020-07-07-07''''.

    ------------------------------
    JEAM COELHO
    Cognos Solution Architect

    Linkedin: https://www.linkedin.com/in/jeamcoelho/
    ------------------------------



  • 7.  RE: Dashboard - Drill Through Details via SQL ?

    Posted Thu August 06, 2020 02:37 AM
    Thank You @JEAM COELHO I will try this and will let you know.​​​​

    ------------------------------
    Harun Y
    ------------------------------



  • 8.  RE: Dashboard - Drill Through Details via SQL ?

    Posted Fri August 07, 2020 02:36 AM
    Hello @JEAM COELHO​ I am trying to use the Query Macro as you have mentioned passing the SQL Statement as a Parameter but if I do not have the Column Names listed in the List Report I do not see any data in the report. In my case I cannot have the Column Names in the List Report as the Column Names are not Static across multiple tables. Any thoughts ?

    Thank You

    ------------------------------
    Harun Y
    ------------------------------



  • 9.  RE: Dashboard - Drill Through Details via SQL ?

    Posted Fri August 07, 2020 07:01 PM
    Hi!

    So, you should configure a static number of columns and set a optional parameter in macro.
    For example

    #prompt('ParameterName' , 'token' , 'select column1 ,  column2 ,  column3 from anyTable')#​​

    This way, you will always have 3 columns (alias) in your query and may add them to a list, crosstab, etc.

    Database fields that are referenced by theses columns can be dynamic in SQL.

    I have never have a requirement like this. I guess that you can achieve your goal with multiple drills or conditional styles too.

    ------------------------------
    JEAM COELHO
    Cognos Solution Architect

    LinkedIn: https://www.linkedin.com/in/jeamcoelho/
    ------------------------------



  • 10.  RE: Dashboard - Drill Through Details via SQL ?

    Posted Fri August 07, 2020 07:46 PM
    Got it. I cannot have static number of Columns as each SQL Statement or the Table Name may have different Column Names with variable number of columns. SQL Statement1/Table 1 can have 10 columns and SQL Statement2/Table2 can have 20 columns totally different Column Names. I cannot have static Column Names.

    As you have mentioned currently I am achieving this by having 100's of Tables within one List Report and with Conditional Block and Style Variables. But the issue is as the Parent Report only support one Drill Through Report (I don't think I can have a parameterized Drill Through Report where I can atleast create multiple Drill Through List Reports and group the tables within multiple List Reports for convenience) I have to have all the Tables (either 100 or 200 etc) within one list report which would become clumsy and every time a new detailed table is added would require changes to the List report would end up too much maintenance. Also I am not sure how many tables can one List Report can accommodate.

    I thought my requirement is a simple one - basically to take the table name as parameter and do a SELECT * FROM Detailed_Table_Name and show the results for each summary item where every summary item will have a detailed table or SQL Query associated to it.

    ------------------------------
    Harun Y
    ------------------------------



  • 11.  RE: Dashboard - Drill Through Details via SQL ?

    Posted Fri August 07, 2020 08:01 PM
    Edited by System Fri January 20, 2023 04:44 PM
    "Parent Report only support one Drill Through Report"
    You can define several drill thru paths in the sambe data item, text item, etc., as far as you need. I don't know if it is the case.

    Your requirement is to pass a table from one report do another and show all columns of this table in a list report? Do I understand in the right way?

    ------------------------------
    JEAM COELHO
    Cognos Solution Architect

    LinkedIn: https://www.linkedin.com/in/jeamcoelho/
    ------------------------------



  • 12.  RE: Dashboard - Drill Through Details via SQL ?

    Posted Fri August 07, 2020 08:07 PM
    Yes that is correct requirement is to pass a table from one report to another and show the data within the tables with all the columns of this table in a list report.

    ------------------------------
    Harun Y
    ------------------------------



  • 13.  RE: Dashboard - Drill Through Details via SQL ?

    Posted Fri August 07, 2020 08:14 PM
    When you say several drill through paths all of them can only go to one single Drill Through Report right (in my case List Report).

    ------------------------------
    Harun Y
    ------------------------------