Not applying the else part from filter in select statement

[DRL]

Hi,

I have a query in report studio with numerous filter based on value prompts. These value prompts have the values such as 1,2, or 3(defaulting to ALL). I don't want the filter clause applied to the main sql query if the user defaults to ALL

I have tried following but it does not give me any results back

If (?promp1? = 1) then (table.columnA = 1)
else if (?promp1? = 2) then (table.columnA = 2)
else
(1<>1)

Any suggestion would be very helpful.

Thanks,
DRL

[Arsenal]

hmmm
might have to do static choices and have a choice saying All in the use and display values and then set default choice to All
filter should be something like (?prompt?='All' AND (1=1)) or (columnA=?prompt?)

might also want to consider a prompt macro

[MFGF]

Hi,

I have a query in report studio with numerous filter based on value prompts. These value prompts have the values such as 1,2, or 3(defaulting to ALL). I don't want the filter clause applied to the main sql query if the user defaults to ALL

I have tried following but it does not give me any results back

If (?promp1? = 1) then (table.columnA = 1)
else if (?promp1? = 2) then (table.columnA = 2)
else
(1<>1)

Any suggestion would be very helpful.

Thanks,
DRL

Assuming the final condition relates to the ALL selection, this will always return a false for each row, so will result in nothing being displayed.

Try changing it to
else
(1=1)

MF.

[mvjcognos]

try this..............
case
when (?promp1? = 1) then (table.columnA = 1)
when (?promp1? = 2) then (table.columnA = 2)
else
(1=1) 
end