Tuesday, November 11, 2008

How to use the "Range Prompt" - some undocumented features

The TextField prompt has an interesting feature: it can be used to get two values instead of one by setting its ``Range'' property to YES.

This is handy when you want to filter by a range that has a lower and/or an upper value.

The extra that the prompt gives you is that you can have open intervals ie. cases where the user only specifies and upper or a lower value.

e.g. filter lines where some ratio is
  • less than 80%
  • beteen 60% and 80%
  • greater than 80%

It saves you from having to do some sort of javascript voodoo with radio buttons etc.

The trick is that you want to set the prompt as optional (= not required) because otherwise it would be just two textfields, which is not any better than using two textfield from the first place.

Now... since the prompt is optional the user does not have to input values.
If the user leaves both the lower and the upper values unspecified... the prompt does not return anything.
And this is a problem...
...because you are trying to use the prompt in an expression using the in_range operator that looks something like this

filter(my_dimension, my_measure in_range ?pRange?)

and this blows up!
After the prompt is evalued it becomes the following expression:

filter(my_dimension, my_measure in_range )

...that's right, the prompt did not return absolutely anything

To fix this you need to use the long form of the prompt macro and set the datatype to ``range''.
This is undocumented as of 8.3 ... but it works

to fix the expression specify a default range that the prompt should return when the user left both lower and upper values unspecified. e.g. can be an open range starting with 0

filter(my_dimension, my_measure in_range #prompt('pRange', 'range', '{0:}')#)