Sorting in Value prompt

[kiran04]

Hi,
I have a requirement where I have a column called 'age' and I'm using this 'age' column in the value prompt.
The data in this column are like
<5
5-14
15-24
24-45
45-64
64+
ALL

so here in the value prompt I need to display the value prompt as in this below order

ALL
<5
5-14
5-24
24-45
45-64
64+

So, how can I achieve this. Any suggestions or comments are welcome.

thanks,
KWAS04

[bdbits]

Create an additional data item (probably best in FM for reuse) called maybe "age_sortkey". Define an expression for it, for example you could use if/then or case expressions where you look at the value of 'age'. The expression should produce an integer corresponding to the desired position in the list. Set "Sorting..." on the value prompt to use age_sortkey.

If this is a dim and thus has a small rowcount and is easy for you to update, or the table is populated via an ETL process, you could actually put "age_sortkey" as a column in the database, then reference that in the value prompt "Sorting..." property.

[CoolP]

You can either do it in FM as suggested above or if this selections are only for a particular report  and the number of selections are limited, you can achieve this by using 'static choices' property of the value prompt.


Thanks
Prasan

[kiran04]

Thanks for the suggestions,  I tried by taking a dataitem and used a case statement where it produces integer values and then I sorted the dataitem. It worked for me.

Thank you bdbits , CoolP.


KWAS04