requirement :where ( PROJ = (SELECT NAME FROM SOURCE.MGMT WHERE NUM = '<P31>' and BUS_PARTNER = '<BP>') )
To do this in cognos:
I have taken Query 1:SELECT NAME FROM SOURCE.MGMT WHERE NUM = '<P31>' and PARTNER = '<BP>'
Filter usage :option for NUM='<P31>' and Required for PARTNER='<BP>'
Now in Query 2 which is main query for List report , I have taken filter usage as optional again and PROJ=[Query1].[Name].
SQL generated expected as below when NUM parameter is not entered :
select * from table where date='2016-01-01' and week='2016-07-01' and what ever users enter in prompt page which are all optional.
SQL generated is:
select * from table where date='2016-01-01' and week='2016-07-01' and PROJ=(select NAME from SOURCE.MGMT where PARTNER='<BP>'.
which is not correct.
Here what should I do to skip the PROJ=....filter in where clause all together when no NUM is entered by users.
Any javascript or something that I can use for this purpose?
Any suggestions please