BusinessObjects Board

How to create a text input control based on partial text or number for filtering a report

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 ?

What version of BusinessObjects are you using? Can you provide some free-hand SQL with sample data?


Dear @nscheaffer,
I’m using SAP BI4.2 Sp8
For data used on this filter I’m using data as varchar type. These strings can start, contain and/or finalize with numbers: for instance ‘2021.SAP Test55’, ‘SAP Test55XX’, ‘SAP Test57’

There some data:

I tried to convert the variable to a string by doing v_Filter for Searched Text =If(Pos(Upper([Dept Name]);Upper(""+[v_Search Text])) > 0) Then 1 Else 0

It’s working except when the string starts by a number.

For instance with the filter ‘2021’ data as ‘2021.SAP.XXX’ is not retrieved. But data as ‘SAP.2021.XXX’ is retrieved

I mocked up some test data for use in a free-hand SQL query with SQL Server syntax here. Also here…

SELECT ‘2021.SAP Test55’ AS [Dept Name]

Which yields this…


There may be a way to get your approach to work, but 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 an asterisk (*) as a wildcard whereas the Matches pattern query filter operator uses the percent sign (%) 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.


@nscheaffer Thank you very much for this solution. It’s working now perfectly