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
This might seem a daft question, but why on earth does the end user need to see the SQL that created the report?
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.
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:
select * from cogipf_nativequery where COGIPF_REQUESTID = #sq($requestID)#
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.
Quote from: dougp on 27 Oct 2017 10:11:41 AM
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.