Saturday, August 14, 2010

IF THEN ELSE and CASE in FILTER

You can't directly use the case statement in a filter expression inorder to change the filter condition according to the prompt selection by a user.To acheive the result it can be done in two ways one is we can use a nested case expression

Methode 1:
case when ?p_param?=1 then
    filter1
else case when ?p_param?=2 then
    filter 2
end
end

Methode 2:

The same result can be acheived by using if else if .
eg:

if (?p_param? = 'CURRENT DAY')

then ([gosales].[Sales].[Current
Day]=[gosales].[Daily Calendar].[Current Day])

else if (?p_param? = 'WTD')
then ([gosales].[Sales].[WTD]
=[gosales].[Daily Calendar].[WTD])

else if (?p_param? = 'PTD')
then ([gosales].[Sales].[PTD]
=[gosales].[Daily Calendar].[PTD])

else if (?p_param? = 'YTD')
then ([gosales].[Sales].[YTD]
=[gosales].[Daily Calendar].[YTD])

else (0)

No comments:

Post a Comment