Creating A "Select All" Condition For A Multi-Select Filter!

[intapiuser]

Often, users will have a large number of filter values selected in a multi-select filter, and rather than having to deselect or 'x' out every filter value, you may want to quickly check the full, unfiltered data. 

This is something you can do in Sisense for Cloud Data Teams using a couple SQL ticks. We'll walk through how to do this using an NBA dashboard I built out for fun (using a CSV from early in the season - keep in mind it's not updated)

Let's go through what it looks like! Here's the dashboard, completely unfiltered (note the values which appear on the charts so you can compare to below screenshots).

And this is how it looks after I've filtered to only include the Warriors, Rockets, Raptors, and Celtics (note the filterbar at the top as well as the change in scale/values):

I've implemented a special filter value called All!: 

When the 'All!' value is selected, it doesn't matter what else is selected or displayed - this value performs a "Select All" operation.

In other words, the 'All!' value overrides everything else and the charts will display every result. Notice on this next picture both the filterbar, and the fact that the chart data is exactly the same as the first picture which indicates the filter successfully returns all values:

So how is this done? 

First, let's go over the filter used to create the 'nba_team' filter. 

select distinct team_name
from [nba_shots]
order by 1

Pretty straightforward. We're going to now add an 'All' value to this query using a union:

select 'All!' union all
select distinct team_name from [nba_shots]
order by 1

Now here's the trick - we're going to look for this value in the SQL itself using the STRPOS function as well as some CASE WHEN logic in the WHERE clause. 

Here's a picture of my full query for the "Shot Types" chart:  

Like other filters, we implement the query logic into the WHERE clause. In this case the NBA filter is a direct replacement filter so we'll call it in square brackets, with single quotes: '[nba_team]'   

where
case when strpos('[nba_team|All!]', 'All!') > 0 then 1 = 1
  else [team_name=nba_team]
end

Let's break this down. The Strpos searches for the term 'All!' in the list of filter values provided - if it finds a match, the query evaluates 1=1; this will return every values. On the other hand, if the 'All!' filter is not selected, then we use a regular [team_name=nba_team] filter. 

Here is what the query evaluates to and what is sent to the underlying Redshift database: 

case when
strpos('All!, Golden State Warriors, Boston Celtics, Toronto Raptors, Houston Rockets', 'All!') > 0
then 1 = 1

else (team_name IN ('All!','Golden State Warriors','Boston Celtics','Toronto Raptors','Houston Rockets'))
end

You may be wondering why this filter was called 'All!' instead of 'all'. The reason is that strpos() searches through all values and within the entire string(s), and we don't want a hypothetical filter value of the Allentown Tigers to trigger the 'All' response. The name doesn't matter, it could be called '!All' or '!All!' - I just wanted for it to appear at the top of my list, which is sorted alphabetically. 

But What If I'm Matching on ID Instead?

Good, you should be matching on ID whenever possible. 

Let's say my filter query instead looked like 

select distinct team_id, team_name
from [nba_shots]
order by 1

and we match on the team_id. 

Our All! filter will look like the following (since we're matching on ID, you can simply call the display value 'All') : 

select -1 as team_id, 'All' as team_name
union all
select distinct team_id, team_name
from [nba_shots]
order by 1

And your WHERE clause will now look like this:

where
case when '[nba_team|-1]'= '-1' then 1 = 1
  else [team_name=nba_team]
end

Hope this guide helps you create your own user-friendly Select All filters!

If you have any questions about the methods used here, let me know in the comments! 

[amritbhgt]

i have a blox widget with multiple check boxes , i am trying to create a check box which will select all the other check boxes.

$('#chk_all').on('click', function(){
if(this.checked) {
$('.chk_brand_items .checkbox').each(function(){
this.checked = true;
});
}else{
$('.chk_brand_items .checkbox').each(function(){
this.checked = false;
});
}
});

{

"type": "Input.Toggle",

"title": "Select All",

"id": "chk_all",

"valueOn": "true",

"valueOff": "false",

"value": "false",

"style": {

"text-align": "center"

}

},

{

"type": "Input.ChoiceSet",

"id": "chk_brand_items ",

"class": "chk_brand_items ",

"layout": "vertical",

"isMultiSelect": true,

"value": "",

"style": {

"padding": "4px 0 0 5px"

},

"choices": [

{

"title": "Brand",

"value": "Brand"

},

{

"title": "Brand 1",

"value": "Brand 1"

},