Report expressions in Reportnet SQL

[trevwoody]

Hello all. We're trying to find a way to identify troublesome queries in Teradata through the SQL generated. I've built a report with a text column that identifies in string form hardcoded who has run it. A more flexible approach would be to use the report expression function for ReportName() or ReportPath(). Unfortunately these are only available when designing the page layout rather than in query layout. Can anyone suggest an approach to get these captured as text strings that can be included in a query and therefore appear in the SQL?

Thanks

 

[misscognos]

here is an idea you can try.. i have not tested it and it seems a little convoluted adn i can't guarantee that it will work. this also assumes you are using cognos 8 Reportnet/BI server. i'm not certain if you are or not.

1. have a prompt page.
2. set the default value of the prompt set to a report expression to determine whatever you need to determine.
3. have the prompt hidden if you like and autosubmit it.
4. set a data item to ?parametername?. this will get the value into the query and you can do what you want with it.

Step 2 cannot be done prior to Cognos 8.