How to use text input for multiple selections in SAS VA 7.3

[Messham]

Is it possible to use the text input control to filter a table using mutliple values? My example is an item code, and I want users to be able to search for multiple item codes with the results displayed together in the report. Currently, if I run this with one text input code, it filters down the table, but if i try multiple (spaced with space, comma or semi-colon) it doesn't work.

[PetriRoine]

Hello @Messham 

So if the end user types into Text Input control f.ex. ID10005; ID10015; ID20001 and I need to use these all to search matching item codes I would consider the following approach:

Create f.ex. 5 new calculated items and parse the string from the Text Input to these new calculated items.

Then utilize Contains() and OR functions.

Not a scalable solution, but maybe adequate?

Best regards,

Petri Roine

[Messham]

Thank you for your solution - there are possibly thousands of codes so I don't think this is a solution. But I appreciate your help 🙂

[PetriRoine]

Ok.

I'm not sure if I understood correctly as in this approach the total number of item codes is irrelevant. The key question is how many item codes would a single user search at any given time i.e. how many codes would they type into that Input Text control object. In my approach end user could search simultaneously five different codes. I guess maybe around ten would be maximum sensible amount in this approach.

If you meant that they need to enter thousands of item codes into that Input Text to be searched from the full spectrum of item codes - then yes, this is not the solution. Typing in thousands of codes is laborious, maybe they could copy-paste, but there's no sense creating that many help variables.

If however the end users most likely enter only a few item codes into that Input Text object to be searched, then this approach should do the trick.

Anyways, you definitely know the best your case, what works and what doesn't. I hope you find a fitting solution 

Best regards,

Petri

[Messham]

Maybe I misunderstood your solution.

Your solution says;

So if the end user types into Text Input control f.ex. ID10005; ID10015; ID20001 and I need to use these all to search matching item codes I would consider the following approach:

Create f.ex. 5 new calculated items and parse the string from the Text Input to these new calculated items.

What if the next user wants to search ID10004; ID101; ID20002; ID20012; ID351 - do I have to put all of the possible combinations into the solution?

[PetriRoine]

The end user can type what ever codes they want - the codes (search strings) aren't fixed.

The number of codes (search strings) user can type in the Text Input to be searched is fixed. In my example the max number to be searched was five. That's why I had five new calculated items. Actually to be 100% correct the user can type in as many codes (search strings) as they want, but the maximum number of codes (search strings) to be processed is fixed.

I propose you try this and see how it works. 

Br,

Petri

[Sam_SAS]

Petri's solution is a clever one, although things arguably should be much simpler in VA. If you could assign a multi-value parameter to a text input control, for example, it would be much easier.

To spell things out a little more:

1. Create a character parameter and assign it to a text input control. In my example I'll call it InputParm. Users can input a delimited set of inputs like "Sedan; Hybrid"
2. Create a calculated item for each input you want to support. I will create two here, to support two inputs, but you can expand on this as much as you like.
   
   a. Create an calculated item for the first input parameter, I am naming it Parm1.
   GetWord('InputParm'p, 1)
   b. Create the second calculated item, Parm2.
   GetWord('InputParm'p, 2)
   
   
3. Now, create an advanced filter to compare these values to the category item in your data:
   
   IF ( GetLength('InputParm'p) > 0 )
   RETURN ( ( LowerCase('Type'n) Contains LowerCase('Parm1'n) ) OR ( LowerCase('Type'n) Contains LowerCase('Parm2'n) ) )
   ELSE ( 1 = 1 )

You can omit the LowerCase() operator if you want the search to be case sensitive. You could also substitute '=' for Contains if you want exact matches only. This filter expression should accept 0, 1, or 2 inputs from the text input control: If you support more inputs, you can just keep adding OR clauses as needed.

No filter specified:

Two Type values specified:

Let us know if this helps,

Sam

[gizemetus]

The filter does not work when using =. how can we do for numerical data?

Thank You!

[Sam_SAS]

What do you mean when you say the filter does not work? Does the expression editor show an error message?

[gizemetus]

Hi Sam . Actually, No error Only showing warning icon and it do not filter. this is a my query. when I use "contains" correct running . STOK_ID is numeric data but I converted character data.

IF ( GetLength('InputParm'p) > 0 )
RETURN ( ( LowerCase('STOK_ID'n) = LowerCase('Parm1'n) ) OR ( LowerCase('STOK_ID'n) = LowerCase('Parm2'n) ) )
ELSE ( 1 = 1 )

[Sam_SAS]

@gizemetus, what does your expression for Parm1 look like? Are you using the Format() operator to convert the numeric to character?

If you are using Format(), this sometimes adds blanks to the start of the value, so " 4" does not match "4". Try the RemoveBlanks() operator to solve this.

[Sam_SAS]

Oops, the expression for Parm1 shouldn't matter, but the RemoveBlanks() operator still might help.

You might also try changing your stock ID variable to character in the data preparation stage.

[gizemetus]

Hi Sam,

I tried "remove blanks" function and I did. it's running report's filter. " thank you so much. Really!

[Sam_SAS]

That is great to hear! Thanks for letting us know 🙂