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
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
Quote from: DRL on 23 Jul 2010 02:46:09 PM
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.
try this..............
case
when (?promp1? = 1) then (table.columnA = 1)
when (?promp1? = 2) then (table.columnA = 2)
else
(1=1)
end