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?
Noel
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]
UNION ALL
SELECT ‘SAP Test55XX’
UNION ALL
SELECT ‘SAP Test57’
UNION ALL
SELECT ‘SAP.2021.XXX’
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.
Noel
@nscheaffer Thank you very much for this solution. It’s working now perfectly
