making prompt optional

[pooja]

how can we make prompt "Optional", if prompt is coming from direct SQL)

ex- (#promptmany('USER_NAME' , 'CHAR')# )

we tried on properties...but we found only REQUIRED = YES/NO, but not "Optional"

please share any thoughts

thank you in advance

[pooja]

 ???

[CognosPaul]

Adding a default value to the prompt macro will make it optional.

You could, for instance, make your filter something like this:

#prompt('Country'
,'string'
,' '
,'table.country = ')#

This will return a space (' ') if nothing is entered, or will return table.country = 'testValue'

[pooja]

@Paul, thank you

what is this table.country ..??

[CognosPaul]

The table.country was an example.

If you're using direct SQL instead of going through framework you just need to place the prompt macro into the SQL box.

As an example your direct SQL might be like this:

Code Select Expand

select * from SalesByCountriesView

If you want the user to be able to filter a specific view you could change the SQL to:

Code Select Expand

select * from SalesByCountriesView where #prompt('Country','string','1=1','SalesByCountriesView.country=')#

On run time Cognos will give the user an optional textbox prompt. Should the user not select anything the following SQL will be run:

Code Select Expand

select * from SalesByCountriesView where 1=1 Nothing will be filtered as 1 always equals 1. If the user does enter a country, Borogravia for example, the SQL will look like:

Code Select Expand

select * from SalesByCountriesView where SalesByCountriesView.country='Borogravia'

[pooja]

thank you So much