Getting Report SQL during Runtime

[esar]

Hi Experts,

Is it possible to get the report SQL displayed in report viewer while running the report?

[Drisya]

In report studio, from Tools menu select 'Show generated SQL/MDX'.
You cna get the report SQL but on run time this will be changed based on your selected values in prompt.

Hope this helps.

Drisya

https://cognostips.wordpress.com/

[bdbits]

I may be wrong, but I think the idea is to display the SQL on the report. Which I am not aware of a way to do that out of the box.

[esar]

Yes.. You are right bdbits.. the intent is to get the report SQL in report output. It is possible through SDk, but i wanted to see if there is any simpler way to achieve this.

Any help would be much appreciated.

[CognosPaul]

One thought that I can't test.

The audit table cogipf_nativequery contains the full query if you have logging set up for it.

Create a new page at the end of your report, and call that with:

Code Select Expand

select * from cogipf_nativequery where COGIPF_REQUESTID = #sq($requestID)#

I can't test this at my current client because, in their infinite wisdom, they've decided not to use the native Cognos audit functionality. Let me know how it goes.

[esar]

One thought that I can't test.

The audit table cogipf_nativequery contains the full query if you have logging set up for it.

Create a new page at the end of your report, and call that with:

Code Select Expand

select * from cogipf_nativequery where COGIPF_REQUESTID = #sq($requestID)#

I can't test this at my current client because, in their infinite wisdom, they've decided not to use the native Cognos audit functionality. Let me know how it goes.

You are amazing... Blew my mind  :D ... Thank you so much... This saves so much work for me..

And Btw love your Blog... Genius

[esar]

One thought that I can't test.

The audit table cogipf_nativequery contains the full query if you have logging set up for it.

Create a new page at the end of your report, and call that with:

Code Select Expand

select * from cogipf_nativequery where COGIPF_REQUESTID = #sq($requestID)#

I can't test this at my current client because, in their infinite wisdom, they've decided not to use the native Cognos audit functionality. Let me know how it goes.

Just one thing.. the reason why i need the SQL is the give it to the users in case the query takes more time and if the data returned is huge..  So they can take the SQL and directly run it in toad or SQL developer. But in your approach, the query will have to run in the database before i can see the SQL, which kind of breaks the actual purpose.

Any way to overcome it????

[CognosPaul]

Two thoughts.

1. Try setting the query hint: "Maximum Rows Retrieved" to 1. No idea if that will still generate the query.
This won't work - it fails with a report error.

2. Add this detail filter:

Code Select Expand

'remove this filter'='before pasting in toad'
The Oracle optimizer should be smart enough to see that filter and stop query processing before it does anything heavy.

[esar]

Two thoughts.

1. Try setting the query hint: "Maximum Rows Retrieved" to 1. No idea if that will still generate the query.
2. Add this detail filter:

Code Select Expand

'remove this filter'='before pasting in toad'

1st option will throw cognos error. But second option sounds promising. I can parse the SQL using jquery and remove that filter condition in output.

[CognosPaul]

use the replace function in the select - no jQuery needed.

[esar]

use the replace function in the select - no jQuery needed.

Thank you Paul. Appreciate your timely help.