I was wondering if there was a way to create a prompt when you use write SQL for a query subject.
Example:
Select project
from projectTable
where project like '1234'
If it's possible, what is the syntax for creating a prompt for project?
Macros, and by extension prompts, can be inserted directly into SQL. You can do something like:
select
project
from
projectTable
where
project like #sq('%' + prompt('project','token','I am a default value!') + '%')#
and year = #timestampmask($current_timestamp,'yyyy')#
It's worth noting that I made the project a token as opposed to a string. A string prompt will return whatever is entered inside a pair of apostrophes, while a token prompt will return the value unaltered. The sq function will wrap the contents with a Single Quote.
The previous example would resolve to:
select
project
from
projectTable
where
project like '%I am a default value!%'
and year = 2010
Can this be done in Report Studio? ???
I think I could do it by creating a new query subject in FM and add macros, then publish the package. However, can it be done by a report writer?
Cognos Macros can be used in GM as well as RS
To expand a little on Cognostechie's reply. You can insert SQL directly into a report using the SQL object in the Query Explorer.
By default standard users will not be able to run reports with user defined SQL. They will receive the following error: -DEF-0326 User define SQL is not permitted for the user who has the identity of whatever.
The kb artice here (http://www-01.ibm.com/support/docview.wss?uid=swg21339780) describes the cause and provides the solution.