Hi,
I am using a text box prompt in my report. But the problem is the field which is being filtered is Case sensitive.
How can i convert the user input in any case(UPPER or LOWER) So that the field is filrtered and i get data in my report??
Currently(with text box prompt) I am getting no data when I am entering 'ABC' rather than 'abc'.
I know I can go with select and search prompt but as it is a existing report ,I would like to know if I can do this using text box prompt only.
Regards,
Joker
Upper([DataField]) = upper(?FilterText?)
Quote from: BigChris on 27 Oct 2014 05:00:18 AM
Upper([DataField]) = upper(?FilterText?)
Hi BigChris,
I used this but I am unable to use multiselect with this.
I tried using this expression also:
UPPER([DATAITEM]) in (UPPER(?parameter?))
OR
[DATAITEM] in (?parameter?)
but no help.
Regards,
Joker
Hello Joker,
A text box prompt can be used to supply a single value only. If your data item that is being filtered is [Country] for example, then entering a value of US in the text box prompt will evaluate to [Country] in ('US'). If you enter a value of US,CANADA, the filter will evaulate to [Country]in ('US,CANADA') which will not yield any results as there won't be a country named US,CANADA in your data. What you really want the filter to evaulate to is [Country] in ('US','CANADA').NOTICE each separate value within its own quotation mark pair, separated by commas.
If you want to use a text box prompt to supply multiple values, then you will have to ask your user to enter a comma separated list. Eg: US,CANADA. Then change your filter expression as such:
[Country] in (#csv(split(',',prompt('p_Country','token')))#)
Cognos810