Static Choices with Value Prompt

[actcognosuser]

Hello All,

I have a question about using Static Choices. I have a value prompt which displays the order status.
Say (Pending,Inprocess,Shipped). But i need to add another static option for display (i.e cancelled which is not in the database).

So i created 4 Static Choices for the Value Prompt
Use Value         Display Value
1                      Pending
2                      In Process
3                      Shipped
4                      Cancelled.

So these choices appear in my prompt. But how do i filter the data in my Order query based on these choices?
[OrderStatus]=?Status? dos not filter any data.

I am not sure what the case statement should result in , since Cancelled status is not available for now in the database.

[OrderStatus]=?case when(

[hespora]

hi there,

I'm not certain about your requirement at all. You have a field in your database containing order status. Apparently, this field can have values 1, 2 or 3, meaning pending, in process, or shipped.

If you want to filter on a 4th option called cancelled, which orders exactly are you wanting to display in terms of the field named above?

[actcognosuser]

Thank you for your response Hespora.

The values 1 , 2 or 3 are the use values that I assigned.For now there are no cancelled orders which is why
the database does not have status='Cancelled'. But when user selects Cancelled in the check box prompt it should be blank but show values if other order statuses are selected.

Did I confuse you? I am having trouble with the filter .After defining the static choices how do I pass them to the query?

[hespora]

You pass the parameter by using the ?parameter? syntax.

What does your database have as an exact value if an order is, let's say, pending? Is it really an integer 1, is it a text "1", or is it a text that says "Pending"? Because the way you have setup the static choices, your filter will look for an integer 1.

[actcognosuser]

Hello Hespora,

The database has order status as Pending, InProcess etc.

So filter OrderStatus in ?Paramter? does not give any results even when i assign the use and display value to be string instead of 1,2,3,4. What am i Missing?

[actcognosuser]

This is the filter I am applying with Static Choices.
Syntax error and also not sure of the missing status.



[Order Status] in  (case when (?Order? = 'PENDING' )
then  ([Order Status]='PENDING')
when(?Order?='INPROCESS')
then ([Order Status]='INPROCESS')
when(?Order?='SHIPPED')
then ([Order Status]='SHIPPED')
when (?Order = 'CANCELLED')
then ('') --------What value does it apply to the parameter since the database does not have a Status = 'CanCELLED'
end
)

[hespora]

Hi act,


with the way you've setup your static choices, your parameter will never have a value "Pending". Your Parameter has a value "1" instead.

just put your display values into the use values, leave the display values blank, and then ditch the entire case when. Your Filter should be no more, no less than:

Code Select Expand

[Order Status] in ?Order?

[actcognosuser]

Hello Hespora,

Thank you being so patient with my questions.

I changes the USe Value to be same as Status .

So when i have Order STATUS in ?Order? it is blank.

[actcognosuser]

Thank You So much to help resolve this Hespora.

It works now.

[hespora]

wonderful, glad I could help! :)