Using Text Input object to enter list of values for filtering
A customer once asked me how to use a text box to filter multiple values. He wanted to type a list of zip codes and use that list to filter other objects in the report. If he had Viya 2020.1.1 (December 2020) or later, I could have referred him to the search feature for List control objects, but he was running Viya 3.5, so we had to be creative and develop our own solution.
The solution was obviously an advanced filter applied to the object to be filtered, and the Contains operator was the fist thing that came up to my mind. In Viya 3.5, the syntax for Contains is the following:
'Parameter'p Contains 'ZipCode'n
This of course was the solution at a high level, but it didn’t take too long to realize we would need to:
- Define a separator for the values in the list.
- Take care of certain exceptions, such as when the Text Input control is empty.
- Deal with blank spaces that could appear at the beginning and/or at the end of typed zip code values.
- Deal with mixed upper and lower case characters to support all types of zip codes around the world that could contain letters.
- Refine our requirements – for example: what happens if an incomplete zip code is typed, such as 234? Should we select all zip codes that contain 234 or should it be a full match? What about the opposite – user types 12345-678 and the table contains 12345 – should they match?
Here is what we have decided:
- Using comma as the separator would work just fine, as comma is not a character used in zip codes.
- If the Text Input control is left empty, then it should have the same behavior as Visual Analytics: no filter is applied.
- Leading and trailing blanks for each of the values should be ignored, as they are not part of the zip code values being searched.
- We would standardize in upper case characters only.
- Typed values and values stored in the table should match entirely, so 12345 would not match 12345-678 and vice-versa.
To test if the input list of zip codes (a character parameter) is empty, we used the IsSet operator, and to ignore the filter if it’s empty, we used the expression 1 = 1 (which returns TRUE, as Visual Analytics doesn’t have a Boolean type):
IF ( 'Parameter'p IsSet )
RETURN (<expression>)
ELSE ( 1 = 1 )
Now we needed to work on the <expression>. To remove blanks, we used the RemoveBlanks function. We could have used the RemoveChars function instead, which is more generic and would work for other types of characters. To standardize the input, we applied the UpCase function:
UpCase(RemoveBlanks('Parameter'p, _All_))
Our <expression> then became:
UpCase(RemoveBlanks('Parameter'p, _All_)) Contains UpCase(RemoveBlanks('ZipCode'n, _All_))
Observe that the same transformations were applied to both sides, just to make sure we are comparing apples to apples.
For the full match of individual values, the operator Contains naturally takes care of cases where the user types 12345 on the list of values and the table contains a zip code like 12345-678 (it’s a no match as defined), but the opposite is not true:
- “zipcodeA,12345,zipcodeB” Contains “12345-678” --> returns FALSE (not a match): correct
- “zipcodeA,12345-678,zipcodeB” Contains “12345” --> returns TRUE (a match): incorrect
If the user types 12345-678 in the Text Input control, it would match zip code 12345, and potentially any other subset of the input value, such as those bad zip codes:
- 12345-
- 123
- 45-6
- -678
- -
- 7
- etc…
To guarantee the full match of what was being typed, we had to search for the entire value, and an easy way to do that was to include characters to mark the beginning and the end of the values in the search string. Well, it turned out that by definition, all values were already separated by comma in the input text, so it naturally became the character to indicate start and end of values. All we had to do was to include the separator in the expression, by concatenating a comma before and after the zip codes stored in the table, using the operator Concatenate twice:
Concatenate(
',',
Concatenate(
UpCase(RemoveBlanks('ZipCode'n, _All_)),
','
)
)
After concatenating comma before and after:
- “zipcodeA,12345, zipcodeB” Contains “,12345-678,” --> returns FALSE (not a match): correct
- “zipcodeA,12345-678, zipcodeB” Contains “,12345,” --> returns FALSE (not a match): correct
This seemed all we had to do, except for one thing: if user types 12345-678 as the first or the last element on the search list, or if it’s the only element in the list, it will not contain the separator character, and valid zip code values will not be found:
- “12345-678,zipcodeB” Contains “,12345-678,” --> returns FALSE (not a match): incorrect
- “zipcodeA,12345-678” Contains “,12345-678,” --> returns FALSE (not a match): incorrect
- “12345-678” Contains “,12345-678,” --> returns FALSE (not a match): incorrect
To fix that we needed to append a comma at the beginning and at the end of the entire typed input parameter string, exactly as we did before with the zip codes stored in the table:
Concatenate(
',',
Concatenate(
UpCase(RemoveBlanks('Parameter'p, _All_)),
','
)
)
After we did that:
- “,12345-678,zipcodeB,” Contains “,12345-678,” --> returns TRUE (a match): correct
- “,zipcodeA,12345-678,” Contains “,12345-678,” --> returns TRUE (a match): correct
- “,12345-678,” Contains “,12345-678,” --> returns TRUE (a match): correct
Putting everything together, this is how the filter expression looked like (before Viya 2023.06 syntax):
IF ( 'Parameter'p IsSet )
RETURN ( Concatenate(',', Concatenate(UpCase(RemoveBlanks('Parameter'p, _All_)), ','))
Contains
Concatenate(',', Concatenate(UpCase(RemoveBlanks('ZipCode'n, _All_)), ',')) )
ELSE ( 1 = 1 )
Same expression starting with Viya 2023.06 syntax (basically IsSet and Contains are functions instead of operators and the key work _All_ no longer has underscores):
IF IsSet('Parameter'p)
RETURN Contains(
Concatenate(',', Concatenate(UpCase(RemoveBlanks('Parameter'p, All)), ',')),
Concatenate(',', Concatenate(UpCase(RemoveBlanks('ZipCode'n, All)), ','))
)
ELSE ( 1 = 1 )
In our case, zip codes were already stored as characters in the source table, but if that was not the case, we could simply apply the Format function to transform them into characters, and the rest of the expression would be the same.
Obviously, as long as the requirements remain the same, this expression can be leveraged for other use cases, such as searching for car makes.
Suppose that you have a bar chart, and you want to be able to type one or more vehicle Makes separated by comma in the Text Input control object at the top to filter the chart:
Let’s take a look at how you would do that in three easy steps.
FIRST, you create a parameter with the following attributes:
- Name: list of Makes separated by comma
- Type: Character
- Multiple values: unchecked
- Current value: empty
Knowing that this parameter will carry multiple values, you may be attempted to check the box for multiple values, but a text input control object, where this parameter is going to be assigned to receive its value, does not accept multiple values – the object is a text box, and therefore it accepts only a single value, which is a string.
SECOND, you assign the parameter to the Text Input control object. Note that before Viya 2024.05, the parameter is assigned in the object’s Roles pane:
Starting in Viya 2024.05, this assignment is done in the object’s Actions pane: 
THIRD, you add the advanced filter discussed previously into the bar chart’s Filters pane – remember that the name of the parameter and the data item being searched are different in this example.
Now let’s suppose you type “Toyota,, , bmw, hoNdA ,Mercedes,Volks Wagen , xyz, Nissan SUV, Land rover”, without the quotation marks, in the Text Input object and hit Enter – you must hit Enter for the value to be assigned to the parameter. Starting from the inner most operators/functions and moving outwards, this is what happens:
- Remove all blanks (space characters) from the input parameter. You should get this: Toyota,,,bmw,hoNdA,Mercedes,VolksWagen,xyz,NissanSUV,Landrover
- Make everything upper case as a best practice for string comparison: TOYOTA,,,BMW,HONDA,MERCEDES,VOLKSWAGEN,XYZ,NISSANSUV,LANDROVER
- Concatenate a comma at the end. Note that comma is used as the separator. It must be a character or combination of characters that do not appear in the data being filtered - all possible values that Make can assume in this example. This is what you have so far: TOYOTA,,,BMW,HONDA,MERCEDES,VOLKSWAGEN,XYZ,NISSANSUV,LANDROVER,
- Concatenate a comma (or the other elected separator) at the beginning. Let’s call this result the standardized parameter: ,TOYOTA,,,BMW,HONDA,MERCEDES,VOLKSWAGEN,XYZ,NISSANSUV,LANDROVER,
- Repeat the same steps for the Make column to create the standardized Make, so you can compare apples to apples. For example, if the value of the Make being compared is Land Rover, it would become: ,LANDROVER,
- Check if the standardized parameter contains the searched standardized Make.
- Only do all of that if the parameter is set. If it’s not set, return true, meaning all Make values should be displayed, which is the default in SAS Visual Analytics.
Note that Mercedes did not show up in the bar chart because it’s stored as Mercedes-Benz in the table. In another hand, Nissan is a valid car make but Nissan SUV is not, so it was not returned. Also, because the implemented filter is not case sensitive, Honda was displayed as expected, even if typed with mixed casing in a strange way. Because spaces are eliminated, mistakenly typed Volks Wagen (two words), matched the make Volkswagen. Non-existing makes such as xyz, empty values, and extra blank spaces didn’t affect the results.
Please, let me know in the comments what you think about this solution and how or for what purpose you have used it. If you have Viya release 2020.1.1 (December 2020) or above, I’m particularly interested in knowing why you had to adopt this solution instead the List control object with search capabilities available out of the box.
