Optional prompt in tabular SQL ????

[cbmat]

Hello everyone,

I am trying to create an optional prompt in an tabular sql.

Based on the reportnet documentation (macros), I should be doing the following :

"If you use a space and no values are provided in the Prompt Value dialog box,
a Where clause is usually not generated."

When I issue my sql statement such as the following :

select territory_no, territory_name
from dbo.DW_Territory
where territory_name in (#promptmany('P2','string',sq(' '))#)

the system takes the space as a default value instead of recognizing that
we want no filtering at all (when we select no prompt value at run time)

Is this the proper syntax ?

Any help would be highly appreciated.

[BIsrik]

i don't feel u can make that prompt optional. If u really want that then apply the parameterized filtering outside the tabular SQL. I mean in ur Tsql just select the columns and in ur query have filter saying territory_name = ?p1? and make that optional.

Srik

[Draoued]

I tried it.
The filter can be marked as optional.

We are using an Oracle database, with Toad I can see what sql is generated and executed in the database.

cbmat, your sql is not generated a where statement in the database, but the strange thing is that Reportnet doesn't display any row in return

[BIsrik]

can u plz share how to make the macros in sql as an optional filter..

Srik

[Draoued]

I have created a basic report list.
Then in tabular model , I have dragged a filter.
In the filter I have added    territory_name in (#promptmany('P2','string',sq(' '))#)

Then if you highlight the filter, in the properties you can select Optional.

[Draoued]

Sorry I was not using the Tabular SQL.

[cbmat]

Got it from Cognos knowledge base document # 1014479.

It is basically a simulation. The reportnet documentation is wrong saying that you put a space. You must provide a default value that isn't part of the regular values from the data. This is the solution they recommend :

select territory_no, territory_name
from dbo.DW_Territory
where territory_name in (#promptmany('P2','string',sq('Not Selected'))#)
or ('Not Selected')  in (#promptmany('P2','string',sq('Not Selected'))#)

[BIsrik]

thanks for sharing the infor..Could u plz update the status of the topic..

Srik