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.
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
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
can u plz share how to make the macros in sql as an optional filter..
Srik
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.
Sorry I was not using the Tabular SQL.
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'))#)
thanks for sharing the infor..Could u plz update the status of the topic..
Srik