Creating prompt within SQL???

[Tsunami]

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?

[CognosPaul]

Macros, and by extension prompts, can be inserted directly into SQL. You can do something like:

Code Select Expand

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:

Code Select Expand

select
   project
from
   projectTable
where
   project like '%I am a default value!%'
   and year = 2010

[Tsunami]

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?

[cognostechie]

Cognos Macros can be used in GM as well as RS

[CognosPaul]

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 describes the cause and provides the solution.