show generated sql on report page

[sanjeev]

Hi,

Here i have a requirement to show the Generated SQL / Report SQL on top of List in report page in cognos report studio.
I have gone through forums and links. I see one to view generated SQL from tools > genereated SQL
But my need is to show or get that generated native sql on to report page.
Please advise.

Thanks
Sanjeev

[BigChris]

This might seem a daft question, but why on earth does the end user need to see the SQL that created the report?

[hespora]

one possible reason (I've looked into the same before) is consistency of data.

My org has about ~30 people with cognos adhoc report designing access. You'd be amazed how often it happens that I get an adhoc report's 6 month old output from the recipient asking me to update that with current figures, and I cannot even replicate the exact figures in that output, simply because the person who did the original query made a non-obvious or even dumb decision.

[CognosPaul]

I answered this earlier:http://www.cognoise.com/index.php?topic=33071.0

TLDR:

The audit table cogipf_nativequery contains the full query if you have logging set up for it. Set up a connection to the audit store database.

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)#

[dougp]

Rather than clutter the report with something that will only confuse users, how about just a date and time the report was run?  Then the admin or report developer can just look up the SQL in the Audit database.

[MFGF]

Rather than clutter the report with something that will only confuse users, how about just a date and time the report was run?  Then the admin or report developer can just look up the SQL in the Audit database.

That sounds like common sense to me :)

MF.