requirement :where ( PROJ = (SELECT NAME FROM SOURCE.MGMT WHERE NUM = ‘’ and BUS_PARTNER = ‘’) )
To do this in cognos:
I have taken Query 1:SELECT NAME FROM SOURCE.MGMT WHERE NUM = ‘’ and PARTNER = ‘‘
Filter usage :option for NUM=’’ and Required for PARTNER=’'
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=’’.
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 suggestions please