VA Report Example: Use Text Input for custom search

The report requirement to be able to enter a partial search string combined with other search strings and have the filter be applied across several report objects is quite popular.

Let’s walk though how to configure this as well as cover any designer Pro Tips.

Use Parametrized Filters for Custom Searching

The first example I will walk though is how to use Text Input Control Objects at the Report Level so that the custom search condition can be applied across the report pages and objects.

Here is a table to remind you of Report and Page Level Prompt behaviors. The key take away here is that when using Control Objects with your typical Data Role assignments the Equal To operator is used for both numeric and character data items and multiple filters are combined with an AND operator.

In the example I am going to cover, we will configure an advanced Report Level filter using Text Input Controls with only Parameter Role assignments so that we can enter partial text search conditions and control which filter combination are to be applied using the IF...ELSE operator.

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

For more additional about Report Level and Page Level prompting see this article: New control prompt placement option in SAS Visual Analytics.

Here is a screenshot of the report. We will design to have three Report Level Prompts, one Page Level Prompt on Page 1 and one Page Level Prompt on Page 2 (not shown here).

Report Level Prompts

Select Year Prompt

The first prompt is the Select Year prompt. This is one of those straightforward configurations of using a Control Object and Data Role assignment. Here you can see that the Control is a Drop-down list and the Data Role Assignment is Date by Year. This means that the default behavior of a Report Level Prompt will be applied. All objects in all pages of the report will be filtered where Date by Year = 2011.

Product Lines Search String Prompt

The second prompt is for the Product Lines search string. We want the user to type in text to use in the filter. We will save this text in a parameter. First you will need to create a new character parameter.

Character parameters do not need a default value. Be sure to give your parameter a meaningful name, I used ProdLinesParameter.

Next, be sure to add the Text input to the Report Level prompt area and only set the Parameter for the Data Role assignment.

Product Description Search String Prompt

The third and last Report Level prompt is for the Product Description search string. Like the before, we want the user to type in text to use in the filter and we will save this text in a parameter.

You will need to create another new character parameter. Character parameters do not need a default value. Be sure to give your parameter a meaningful name, I used ProdDescParameter.

Next, add another Text input to the Report Level prompt area and only set the Parameter for the Data Role assignment.

Filter Definition for Report Level Search Strings

Now comes the fun piece, building the custom filter for the List Table.

We don’t need to do anything for the Select Year prompt, since this is using the standard Data Role assignment and by default, as a report level prompt, it will filter all pages and all objects for the selected Date by Year using the Equal To condition.

First, to define the filter, we must select our target object the List Table. Then using the Filters pane, click + New filter and select Advanced filter.

In the Advanced Filter Expression window, you will want to build this filter expression.

I’ve also included the text version of the expression so that you may copy it and use your own data item and parameters.

IF ( ( 'ProdLinesParameter'p IsSet ) AND ( 'ProdDescParameter'p IsSet ) )
     RETURN ( ( UpCase('Product Lines'n) Contains UpCase('ProdLinesParameter'p) ) 
            AND ( UpCase('Product Description'n) Contains UpCase('ProdDescParameter'p) ) )
ELSE (IF ( ( 'ProdLinesParameter'p IsSet ) AND NOT(('ProdDescParameter'p IsSet )) )
          RETURN ( UpCase('Product Lines'n) Contains UpCase('ProdLinesParameter'p) )
     ELSE ( UpCase('Product Description'n) Contains UpCase('ProdDescParameter'p) ) )

As you can see from this filter expression, we have a completely custom search condition that cannot be met using the standard Report Level configuration. We check to see if both parameters contain search values and then based on if that is true return case insensitive matching for both data items. Otherwise, check to see which Text Input contains a search string and then only return matching rows for that data item.

Now that you have the skeleton of how you can combine and create a custom search filter using the Text input Control Objects and Parameters, you can change the returned AND condition to OR depending on your needs.

One designer Pro Tip I want to point out is the isSet Operator. The isSet Operator was introduced in the SAS Visual Analytics 8.2 release and per SAS Documentation returns TRUE if the parameter has a value. This Operator allows us the ability to build and customize these types of advanced filter expressions.

Apply this filter across the report

Now once you have your filter expression defined, we can apply this filter across the report to other objects, so long as is uses the same data source.

To do this, we will make it a Common Filter. For additional information see Using common filters in SAS Visual Analytics: Article or YouTube.

Essentially, the Common Filter will be saved with the data source and it will be available to other objects in the report using the same data source for all of the report pages.

Pro Tip: This is why, if you want to have a custom search condition to be applied across pages of a report in this manner, then it is recommended to have the Control Objects up in the Report Level Prompt area. This way, the users will see and be able to change the search strings. If these Control Objects were placed at the Page Level Prompt area or inside a page canvas, you could still create a Common Filter and apply it across the report, but it is possible that the report viewers wouldn’t understand why a filter is being applied and where to change it.

Page Level Prompts

Page 1: Numeric Search String Prompt

On Page 1 of our report, we have already applied the custom text search string filter to the List Table. Now we want to add another filter to return Profit (Sum) if it is greater than or equal to the entered number.

One of the Pro Tips for this example is to use an aggregated measure for Profit. In our example, we are interested in the summed by group value, therefore the aggregated expression would look like this:

Sum _ByGroup_(Profit)

If you were to use the data item Profit, then the filter would be applied for each row of the data and not at the post aggregation level. By using an aggregated measure for Profit in the List Table, we will create an aggregated filter so that we can return aggregated rows where Profit is greater than or equal to $5,000, as an example.

The steps are similar to the previous examples, but instead of a character parameter, we will need to create a new numeric parameter. Numeric parameters require a minimum, maximum, and default values.

Let’s add another Text input Control Object but this time to the Page Level prompt area. Like before, only set the Parameter for the Data Role assignment.

Filter Definition for Page 1 Level Search Strings

We will select our List Table again as the target object for the filter definition. From the Filters pane, notice that we already have our Report Level search string filter applied from earlier named Prod Lines & Description Filter.

Use the + New filter menu and select the data item Profit (Sum). Pro Tip, we will need to select the actual data item since Profit (Sum) is an aggregated measure and aggregated measures do not appear for selection in the typical filter expression window.

Once you make that selection, an automatic aggregated filter is added to the Filters pane. Use the overflow menu and select Advanced edit….

Now in the Edit Filter Expression window, we can change the expression to be: In text format:

'Profit (Sum)'n >= 'Profit(Sum)Parameter'p

In visual format:

Go ahead and test the filter. For numeric Text Input controls, the easiest way to clear the value is to right-click on the text field and use the Selection menu and click Clear selection.

And be sure to save it as a Common Filter.

Page 1 Summary of Applied Filters.

So let’s take a look at the applied filters of the Page 1 List Table. Notice that both Common Filters are the custom advanced filters we are applying to the List Table using the Parameter values we are getting from the Text Input Control Objects. The only standard out-of-the-box filter being applied is the Select Year filter.

If we had additional objects on Page 1 that we wanted to apply our custom search filters to, then we would need to activate those objects and from the Filters pane add the available Common Filters.

Page 2: Numerical SKU or ID Searching Prompt

On Page 2 of our report, I wanted to offer some Pro Tips for searching for numerical SKUs or IDs.

First, if your ID data item is a numeric value, and you aren’t performing a numerical comparison such as equal to, greater than or less than, then it’s recommended that you convert it to character data item.

Create a new Calculated Item and use the Format operator. Typically, you can select the BEST format and then a width long enough to accommodate the field. It’s better to error on the slight larger side since we can remove leading and trailing blanks.

Next, when defining your filter condition, be sure to still use the UpCase as well as the RemoveBlanks Operator. Here is my example where I wanted to search for Product IDs that start with my search string.

Text version:

( UpCase(RemoveBlanks('Product ID (Char)'n, _LeadingAndTrailing_)) StartsWith UpCase('ProdIDCharParameter'p) )

And the visual version:

Page 2 Summary of Applied Filters

So let’s take a look at the applied filters of the Page 2 List Table. Notice that I’ve applied the Common Filter for the Text Input Controls at the Report Level, Prod Lines & Description Filter. The other Report Level filter is the standard out-of-the-box for Select Year.

Then unique to the Page 2 List Table is the Product ID (Char) custom search string where instead of the Contains Operator we use the StartsWith.

Conclusion

Using Text Input Control Objects and Parameters allows for extreme customization to meet your text searching requirements.

Pro Tips include:

Using the UpCase Operator if you want case insensitive searching
Create a new Calculated Data Item from numeric SKUs or IDs to be characters
Use the RemoveBlanks Operator to ensure you get expected results when using the StartsWith Operator
Save custom filters as Common Filters so that they can be applied to other objects in the report

Additional materials for using parameters in SAS Visual Analytics:

Use parameters to highlight data points in VA reports: Article | YouTube
New control prompt placement option in SAS Visual Analytics
Using common filters in SAS Visual Analytics: Article | YouTube
Using parameters in SAS Visual Analytics: Article | YouTube
Use parameters to pick two metrics in Visual Analytics Reports
Use parameters to pick your metric in Visual Analytics Reports: Article | YouTube
Using Date Parameters in your SAS Visual Analytics Reports

How to prompt for a date range in a SAS Visual Analytics report Blog Series:

Slider with Style Input of Range: Article | YouTube
Slider with Style Input of Single Value: Article | YouTube
Drop-down List: Article | YouTube
Text Input: Article | YouTube

More of my Visual Analytics related materials:

SAS VA Report Example: Add a scope to an aggregated measure: Article | YouTube
SAS VA: Key Value Object now with Lattice Category!
Build a date in SAS Visual Analytics reports: Article | YouTube
Use the Report Review pane to ensure good VA Report performance Article | YouTube
SAS Viya Jobs in a SAS Visual Analytics report
Tips for working with multiple data sources in SAS Visual Analytics: Article | YouTube
SAS Demo | The Power Behind a Hidden Data Role in SAS Visual Analytics
SAS Visual Analytics Report Example: Percent of Total – For All, For Rows, and For Columns
SAS Visual Analytics example: moving average

Find more articles from SAS Global Enablement and Learning here.