Hi All,
I created a prompt which will ask user to select month value from the list of values and i am validating it to some condition.The syntax of the prompt is datepart(month,cast(‘01’ + ‘-’ + @prompt(‘1-File Review Month’,{‘January’,‘February’,‘March’,‘April’,‘May’,‘June’,‘July’,‘August’,‘September’,‘October’,‘November’,‘December’},)+’-’ +‘2007’ as Datetime)) in (some condition)
but now my users want to include ‘ALL’ or blank value in this list so that they can run without having this month filter
if i use ‘ALL’ in @prompt it is throwing error as ALL is not the valid month
can any body please tell me how can i achieve this
Create an EXCEL file, put all the month names including ‘ALL’ as one of its option and create a ALL prompt; An example of how to create an ALL prompt is available in Designer FAQ’s… for your reference.
Send the EXCEL file to all the users who uses this report.
Before suggesting anything I would like to ask you that since there will be a single value returned (month number) by the above expression to be used in another condition. Then why do you want ‘ALL’ to be passed to prompt.
You can create a standalone object and add this to the LOV SQL
SELECT 'January'
UNION
SELECT 'January'
UNION
SELECT 'ALL'
Hi,
My requirement is.I want to prompt the user for selecting month value from the drop down list and i am converting that text month value in to numeric and validating that to the month value in the right hand side
datepart(month,cast(‘01’ + ‘-’ + @prompt(‘1-File Review Month’,{‘January’,‘February’,‘March’,‘April’,‘May’,‘June’,‘July’,‘August’,‘September’,‘October’,‘November’,‘December’},)+’-’ +‘2007’ as Datetime)) in (some condition)
For that i am creating the month prompt without having any database column reference
But my user now want to have all values in the list so that they can use them for all months
But it will be always one value rather than multiple values which should be returned by it (left side) and pass to the condition (on right)
In other words what you want the ‘ALL’ to do in this scenario.
Yes what i am dooing is i am creating a condition like based on the status
if status=act then take active month if the status is draft then draft month and for the month input i created the prompt
datepart(month,cast(‘01’ + ‘-’ + @prompt(‘1-File Review Month’,{‘January’,‘February’,‘March’,‘April’,‘May’,‘June’,‘July’,‘August’,‘September’,‘October’,‘November’,‘December’},)+’-’ +‘2007’ as Datetime)) in (select case when @Select(File review\FR Status)=‘Draft’ then @Select(File review\FR Draft Month) when @Select(File review\FR Status)=‘Finalized’ then @Select(File review\FR Finalized Month)
end )
when all is selected then it should take for all values of month
Hi,
I am creating it in designer.please see the code i posted.i am harcoding the month list.Now i want to give my user an option to select all or two or three months from the above prompt
Moving to the Semantic Layer forum for further attention.
kumar446, you are going to have a problem adding “All” to a date prompt because the datatypes don’t work. In your code you are creating a date by adding ‘01’ to the front and ‘2007’ to the back of the selection. If you want to include ALL in the list of months, since you are hard-coding it you would simply add it to the beginning (or end) of your list. But then you have to deal with the fact that “All” is not a valid month, and therefore will fail to convert to a proper date value for your comparison.
If I might make a suggestion… You have several very similar topics running. Similar, but not identical. If they were identical then we could close the extra ones and try to focus the attention on the remaining one. But since they are not identical, I would ask that you pick one and post your complete requirement there, instead of fragmenting the answers in more than one topic. Please post in your other topics which ones you would like closed and a moderator will close them for you. I know I am personally quite frustrated trying to decide which of your topics to participate in, and as a result will probably just stop answering altogether.
Hi Dave,
I searched a lot and cannot find any answer for the problem i am facing.Please eloboarate me
((case when tablea.sts=‘draft’ then cast(datepart(qq,strt_dt) as char) when table.sts=‘finalized’ then cast(datepart(qq,end_dt) as char) end) in @prompt(‘File Review Quater’,‘A’,‘class/object’,multi,))
or
(‘ALL’ in @prompt(‘File Review Quater’,‘A’,‘class/object’,multi,))
But this is not allowing me add multiple month values
I am not asking you to search. I am asking you to pick a topic and stick with it instead of posting the same (or very similar) questions all over the board.
(BOB member since 2006-01-23)
(BOB member since 2004-06-06)
(BOB member since 2005-07-18)
(BOB member since 2004-03-02)
I am asking you to pick a topic and stick with it instead of posting the same (or very similar) questions all over the board.