Cognos Analytics

 View Only
  • 1.  Dashboard query help

    Posted Fri March 29, 2019 03:14 PM
    We are using Cognos Analytics v11.1.1.  We are trying to create a visualization on a dashboard (using a Framework Manager model) and running into problems because we don't have control over the query generated by the dashboard.  In a report, I was able to write four query items to create the desired visualization result.   Looking at the SQL generated, I can see six Common Table Expressions (WITH) statments were generated as part of the SQL query.  For the dashboard, we wrote some calculations hoping we could get the same result, however the generated SQL only does INNER joins and we end up with a query that won't return the result we want.  In dashboarding, is there anyway to write our own SQL for a visualization?

    ------------------------------
    Missy Miller
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Dashboard query help

    Posted Fri March 29, 2019 03:40 PM
    Hi Missy,
    Dashboarding doesn't allow the definition of queries, and thus doesn't allow queries with user-defined SQL either. You could create a Data Module and supply the desired SQL in it and base your dashboard on that Data Module.
    // Henk

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



  • 3.  RE: Dashboard query help

    Posted Fri March 29, 2019 04:05 PM
    In order to supply the SQL within the Data Module, does the data model have to use a Data Server as a source?  Or can a package be used?

    Missy

    ------------------------------
    Missy Miller
    ------------------------------



  • 4.  RE: Dashboard query help

    Posted Fri March 29, 2019 04:27 PM
    Yes, the data source needs to be enabled for web-based modeling.
    You do not have to build a complete Data Module on top of it, just a Data Module that has this data source as one of it's sources. However you do have to import the metadata for the particular data source. This can be an empty set of tables if you use Native SQL, but needs to contain at least the tables in the sql if you're using Cognos SQL.
    It is not possible to model on top of the FM model and specify the SQL.

    // Henk

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