I'm creating an input control in my webi report.
I would like to filter a specific column based on the text filled in the input control. This search shoul be partial and could contain number or chars.
In order to do that I follow these steps:
Create a Dimension variable v_Search Text =""
Create an Input Control on that variable v_Search Text, should be of type Simple Selection with input as Entry Field, leave the other properties as default and click OK.
Create a new variable v_Filter for Searched Text =If(Pos(Upper([Dept Name]);Upper([v_Search Text])) > 0) Then 1 Else 0
Display the value v_Filter for Searched Text of the variable in a new column.
Create the filter on the report/tab as v_Filter for Searched Text = 1 and click OK.
If the value filled in the input control is a string (for instance 'slg') it's working but I get an issue if the value in the input control is a number (for instance '2012'). #error is appearing in the column.
Coud you please help me with that ?
I'm sharing the solution found:
I am going to take a different approach and use the Match() function.
Note this is very different than the Matches pattern query filter operator. The key difference is the Matches() function uses the percent sign (%) as a wildcard whereas the Matches pattern query filter operator uses an asterisk (*) as a wildcard.
First, create a Var Search Text variable and build an Entry Field input control based on that. Next, create a Var Search Text Match variable defined as follows…
=Match([Dept Name]; [Var Search Text])
If you want all occurrences of “2021” then put “2021” in for your input control value…
If you only want the values that begin with “2021” then put “2021*” in for your input control value…
Finally, put add a Var Search Text Match = 1 filter to your table and that should be all.