WEBI Search (pattern matches) for Input Control

building reports webintelligence
1

Working on a “Pattern Matches” search work around since WEBI doesn’t provide at the report level. Please keep that in mind – no report query or Universe solutions please. Just on report vertical block view.
Bottom line: I need to filter the report whether alpha-numeric or numeric is input.

I created a variable called it Search Term. The variable logic is =”” (just to hold an input value)
I created a variable called it Search Matches. The variable logic is =If Pos([State Num];[Search Term]) > 0 Then “Yes” Else “No”
I projected both of these variables into an existing vertical table creating two new columns.

I created an Input control called “State Num Search (Contains)” based on Search Term variable as a text input box field.

Vert table ex…
State Num | … many more columns | Search Term | Search Matches |
B2136G44
C2136G44
B2136F23

When you enter a search value in the Input Control text entry box and press “OK”, the value is populated on every row in the Search Term column. The Search Matches column evaluates the Search Term row value against the State Num column row value and returns a simple Yes/No logical for each row. Then filtering the report on the Search Matches column to display only “YES” produces the final filter. Both added search columns in the vertical report are then hidden.

The problem: The State Num column is a Char dimension from the Universe. When I enter B2136 in the Input Control text entry box as an alpha numeric, the search filter returns the correct records. When I enter 2136, the Search Term column shows the entered value as 2,136 numeric and the search fails comparing a numeric value to a char field and Search Matches column returns “#ERROR” and all records are displayed with no filtering. The Search Term variable automatically changes data type on the fly based on the entered value causing the error. I’ve tried several different methods to force the Search Term when numeric to text with FormatNumber, but no luck as it returns an error… probably something simple. Also tried IF/Then with isError logic to trap and process the error and isNumber to determine data type and process accordingly with no luck. In the end, the solution needs to tie back or merge to a single column that can be filtered. Any reference to a column that is in error - return the same error. Sounds simple…but got two black eyes from it. LOL! I see why BOE never included it. Maybe another solution?
Working in Rich Client 14.0.7 Build 1147.

datawizard (BOB member since 2015-01-20)

2

This doesn’t strip the commas? (I realize you have tried iterations of this before):

=FormatNumber([SearchValue];“0”)

It’s too bad there isn’t a ToChar() function available.

Another option is to create another column/dimension with a padded character (=“S” + [STATE]) then hide it from view. Then when you match the search criteria, you can have the padded character (keep in mind to include it every time), or strip it with a Right() function.

joel.maxuel :canada: (BOB member since 2015-01-14)

3

[quote:e70443fbb6=“joel.maxuel”] Another option is to create another column/dimension with a padded character (=“S” + [STATE]) then hide it from view. Then when you match the search criteria, you can have the padded character (keep in mind to include it every time), or strip it with a Right() function.
[/quote]

Thanks Joel… can’t wait to try it. Sounds like it should work. I’ll let you know.
As you know, when using query filter prompts, there is a pattern matches selection. But it is broke too. The prompt itself also does on the fly data typing based on input. I set a query filter on State Num and refreshed. When the prompt appeared I entered 2136 and it barked back for wrong data type - totally useless. If it was a spreadsheet problem I’d blame Bill Gates. Not sure who to pin this one on. It’s a text field search - can’t they get that right? Ok… getting down from my bandwagon.

datawizard (BOB member since 2015-01-20)

4

Ok, Joel this is how the solution turned out. The suggestion for padding numerical inputs worked great. Remember that the real problem was due to the possibility of a user entering either a alpha-numeric or numeric search input. If it would have been a purely text search such as names or places, the design is much simpler. I shortened the variable names for those that manipulate the search term (ST) and search matches (SM). My real data report (other than the example in my initial post above) had a requirement where the most consecutive numeric digits that would need to be handled was 7 (1234567 or 1,234,567). So, variable ST5 below specifically addresses this occurrence. You would need to alter based on the max number of digits a user would expect to enter. Here goes - same design as above but with these variables. ***Notes

ST1 ="" ***variable assigned to input control text entry box.

ST2 =“A”+[ST1] *** Pads input with “A”

ST3 =If IsNumber([ST1]) Then “Yes” Else “No” ***Checks data type entered.

ST4 =Length([ST2])-1 *** Returns input length minus pad.

ST5 =If [ST3]=“Yes” And [ST4]=5 Then (Substr([ST2];2;1) + Right([ST2];3)) *** Removes comma from 4 digit number

   ElseIf [ST3]="Yes" And [ST4]=6 Then (Substr([ST2];2;2) + Right([ST2];3))    *** Removes comma from 5 digit number

   ElseIf [ST3]="Yes" And [ST4]=7 Then (Substr([ST2];2;3) + Right([ST2];3))   *** Removes comma from 6 digit number

   ElseIf [ST3]="Yes" And [ST4]=9 Then (Substr([ST2];2;1) + Substr([ST2];4;3) + Right([ST2];3))   *** Removes two commas from 7 digit number

   Else Right([ST2];[ST4])       ***Include all if 3 or less digits or all text string (alpha or alphanumeric) input

ST6 =If Pos(Upper([State Num]);Upper([ST5])) > 0 Then “Yes” Else “No” ***checks text entry pattern match in target field (State Num) Upper removes case sensitivity.

SM1 =If [ST4]=0 Then “Yes” Else [ST6] *** ST4=0 input length forces no filter (sets all to Yes) or filter per ST6.

Only ST5, ST6, and SM1 need to be projected into the vertical table - then filter report for “Yes” in column SM1. Then hide all three columns.

So, the above works good. I later condensed two variables into one, but left all here as it is easier to explain. Here is what is returned across the variables with say the input control box entry of 2136.

ST1 = 2,136 (numeric)
ST2 = A2,136 (string)
ST3 = Yes
ST4 = 5
ST5 = 2136 (string)
ST6 = Yes (pattern match to data in State Num)
SM1 = Yes

So, this works good for searches on mixed data types… for example license plate numbers. Where your search term input may be alpha, alpha-numeric, or numeric.

Enjoy!

datawizard (BOB member since 2015-01-20)