Skip to Content
0
Former Member
Nov 25, 2011 at 10:05 AM

WebI Case Statement and @Prompt Inquiry

265 Views

Hi Folks,

I just would like to request your help in clarifying some issue i have with WebI.

I have a filter in the universe that looks like:

DateObject >= (@Prompt('Enter Year:','N','Calendar Date\Year Nbr',Mono,Constrained,Persistent,)*10000) + (substr(@Prompt('Enter Quarter:','A',{'Select Value(optional)','Q1(Jan-Mar)','Q2(Apr-Jun)','Q3(Jul-Sep)','Q4(Oct-Dec)'},Mono,Constrained,Persistent,{'Select Value(optional)'}),2,1)-1) * 300 + 101

AND DateObject <=(@Prompt('Enter Year:','N','Calendar Date\Year Nbr',Mono,Constrained,Persistent,)*10000) + (substr(@Prompt('Enter Quarter:','A',{'Select Value(optional)','Q1(Jan-Mar)','Q2(Apr-Jun)','Q3(Jul-Sep)','Q4(Oct-Dec)'},Mono,Constrained,Persistent,{'Select Value(optional)'}),2,1)) * 300 + 1

However, i have an issue with the formula since there's a possibility that the "Select Value(optional)" value is to be used in the formula. So, i was thinking of using a Case Statement instead.

DateObject >=

( CASE WHEN @Prompt('Enter Quarter:','A',{'Select Value(optional)','Q1(Jan-Mar)','Q2(Apr-Jun)','Q3(Jul-Sep)','Q4(Oct-Dec)'},Mono,Constrained,Persistent,{'Select Value(optional)'}) <> 'Select Value(optional)'

THEN (@Prompt('Enter Year:','N','Calendar Date\Year Nbr',Mono,Constrained,Persistent,)*10000) + (substr(@Prompt('Enter Quarter:','A',{'Select Value(optional)','Q1(Jan-Mar)','Q2(Apr-Jun)','Q3(Jul-Sep)','Q4(Oct-Dec)'},Mono,Constrained,Persistent, {'Select Value(optional)'}),2,1)-1) * 300 + 101

ELSE (@Prompt('Enter Year:','N','Calendar Date\Year Nbr',Mono,Constrained,Persistent,)*10000) + 0101

END)

)

AND DateObject <=

( CASE WHEN @Prompt('Enter Quarter:','A',{'Select Value(optional)','Q1(Jan-Mar)','Q2(Apr-Jun)','Q3(Jul-Sep)','Q4(Oct-Dec)'},Mono,Constrained,Persistent,{'Select Value(optional)'}) <> 'Select Value(optional)'

THEN (@Prompt('Enter Year:','N','Calendar Date\Year Nbr',Mono,Constrained,Persistent,)*10000) + (substr(@Prompt('Enter Quarter:','A',{'Select Value(optional)','Q1(Jan-Mar)','Q2(Apr-Jun)','Q3(Jul-Sep)','Q4(Oct-Dec)'},Mono,Constrained,Persistent, {'Select Value(optional)'}),2,1)) * 300 + 1

ELSE (@Prompt('Enter Year:','N','Calendar Date\Year Nbr',Mono,Constrained,Persistent,)*10000) + 1231

END

So, i'd like to get your points. Is this allowed and will this produce a correct answer? Is there any other way that i could do implement this?

Kind Regards and Many Thanks,

Mark