This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

How to view SQL query behind a chart / dashboard in Cognos / Flexera analytics?

SyedByhaqiEMT
By
Level 4

Hello,

I created a chart in a dashboard; I need to know the SQL query behind the reported values in the dashboard. How to see the query? 

Thank you in advance.

 

‎Jan 07, 2024 06:13 AM

0 Kudos
(3) Replies

MurrayPeters
By
Level 7

Hi,

If your Cognos is hosted on prem then you could always use SQL Server Profiler to capture what queries are run when a dashboard is run.  Otherwise you can only really view the generated SQL when authoring reports in Report Studio and not dashboards (in the dashboard designer).

If the Cognos version is 11.1.7 or later then you could do the following:

  1.  Open your dashboard in the dashboard editor
  2.  Create a blank report using the same data model you selected for the dashboard
  3.  Select your chart in the Dashboard editor & press Ctrl-C to "copy" the chart (Cognos should indicate that you have selected 1 Asset for copying).
  4. Go to the blank report and paste what you've selected.   This should result in the chart appearing and also a Query created that will be used to generate the data.
  5. Got to the Query editor and open the "Generated SQL" property for the query.
    This should display the SQL that will be executed for the query similar to shown below:

Cognos Generated SQL.png

regards,
Murray

‎Jan 07, 2024 04:25 PM

SyedByhaqiEMT
By
Level 4

Hi, 

1. The Cognos version being used is 11.0.13. I still tried to copy the dashboard, however, nothing is being actually copied, and Cognos is also not indicating that anything has been really copied. Is there any other way to see the generated query for the dashboard in version 11.0.13. 

2. Regarding accessing SQL Server Profiler, where exactly could we access the SQL Server Profiler; is it on the Flexera Analytics Server on which Cognos is installed on. 

Thank you for your response. 

Regards, 

Syed 

‎Jan 08, 2024 12:03 AM

0 Kudos

MurrayPeters
By
Level 7

Hi Syed,

Cognos 11.0.13 did not allow copy/paste between Dashboard and Reports, so you won't be able to do that.  If you (re)construct the same chart manually tho in Report Studio using the same data model you should then be able to look at the generated SQL for the query that is used to animate the chart.

Regarding SQL profiler - that is a component of Microsoft SQL Server not Cognos, so it's typically accessed from a machine that has the SQL Server Mgt Studio (SSMS) installed.  If you have access to it you would then start a profiling session connecting to the SQL Server that is hosting the Flexera databases.

‎Jan 08, 2024 08:45 PM