How can I create an Interactive Filter for Dashboard that has two options, Company1 or Not CompanyA

[JR42]

I would like to add an Interactive Filter to a Dashboard that allows the user to filter the reports based on if the record's Company field is CompanyA, or, not CompanyA.

If 'Is CompanyA' is selected, it should filter the reports to only show records for CompanyA.

If 'Is Not CompanyA' is selected, it should filter the reports to show records from all companies, except CompanyA.

If neither is selected (filter is not used), the reports show records for all companies, including CompanyA.

CompanyA is my company.  All the other companies are customers.  This filter would allow me to use the same reports for reporting on all tickets, internal tickets, and/or customer tickets.

If I could figure this out it would be a huge help and reduce the number of dashboard tabs and reports I'll need to create.

[Mike_R · Accepted solution]

I created this, which works per your requirements

Put this code in a dynamic content block (instructions are in the article that I sent before). This is a good starting point. just replace the sys_id with your company sys_id. You can also add some css styles/formatting to the buttons if necessary. 

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
<script>
var my_dashboardMessageHandler = new DashboardMessageHandler("my_unique_id");

function compA() {
var filter_message = {};
filter_message.id = "my_unique_id";
filter_message.table = "task";

<!-- Add your own filter query logic here -->
filter_message.filter = "company=31bea3d53790200044e0bfc8bcbe5dec"; //SysID of company to show
SNC.canvas.interactiveFilters.setDefaultValue({
id: filter_message.id,
filters: [filter_message]
}, false);
my_dashboardMessageHandler.publishFilter(filter_message.table, filter_message.filter);
}

function notComA() {
var filter_message = {};
filter_message.id = "my_unique_id";
filter_message.table = "task";

<!-- Add your own filter query logic here -->
filter_message.filter = "company!=31bea3d53790200044e0bfc8bcbe5dec^ORcompany=NULL"; //SysID of company to exclude
SNC.canvas.interactiveFilters.setDefaultValue({
id: filter_message.id,
filters: [filter_message]
}, false);
my_dashboardMessageHandler.publishFilter(filter_message.table, filter_message.filter);
}

function clearFilter() {
var filter_message = {};
filter_message.id = "my_unique_id";
filter_message.table = "task";
filter_message.filter = "";
SNC.canvas.interactiveFilters.setDefaultValue({
id: filter_message.id,
filters: [filter_message]
}, false);
my_dashboardMessageHandler.removeFilter();
}

</script>
<input id="allTasks" type="button" value="All tasks" onclick="clearFilter();" />
<input id="CompA" type="button" value="Company A" onclick="compA();" />
<input id="notCompA" type="button" value="Not Company A" onclick="notComA();" />

</j:jelly>

[JR42]

Thanks so much Mike!  There are multiple companies that contain 'CompanyA' in the name. 

Do you know how I can modify this to use 'Does not contain CompanyA' instead of using the sys_id?  Or, Can I place the sys_id of all of them into this code? 

Like:

"company=f66b14e1c611227b0166c3a0df4046ff,e30bcf3c2c24550037878c092a26a912,8c9571bb1bef5c50469eda83cd4bcb6b";

"company!=f66b14e1c611227b0166c3a0df4046ff,e30bcf3c2c24550037878c092a26a912,8c9571bb1bef5c50469eda83cd4bcb6b^ORcompany=NULL";

[JR42]

Hi Mike, I think to add the multiple sys_ids I can just use this format, instead of commas.  Do you see any issues going this route?

"company=f66b14e1c611227b0166c3a0df4046ff^ORcompany=e30bcf3c2c24550037878c092a26a912^ORcompany=8c9571bb1bef5c50469eda83cd4bcb6b";

"company!=f66b14e1c611227b0166c3a0df4046ff^company!=e30bcf3c2c24550037878c092a26a912^company!=8c9571bb1bef5c50469eda83cd4bcb6b^ORcompany=NULL";

[Mike_R]

Yes, that format looks correct

[JR42]

Hi Mike, the ^OR format is working for the 'Company A' button, but is not working for the 'Not Company A' button.  I realized the 'Not Company A' button needs to use AND.

I converted it to ^ANDcompany!=, but it is still not working.  The reports show the same numbers when 'Not Company A' is selected or when 'All tasks' is selected.  Do you have any ideas?

Here is the code that is not working:

<!-- Add your own filter query logic here -->
filter_message.filter = "company!=f66b14e1c611227b0166c3a0df4046ff^ANDcompany!=7830290f40b52500673656a355b7a49f^ANDcompany!=76fea4584f897e0080dbecee0210c783^ANDcompany!=e30bcf3c2c24550037878c092a26a912^ANDcompany!=544b781641e27d004ea6dc8785a7b890^ANDcompany!=1e919ffc2c24550037878c092a26a9df^ANDcompany!=ef4fec184f897e0080dbecee0210c731^ANDcompany!=8c9571bb1bef5c50469eda83cd4bcb6b^ANDcompany!=9ff3016197b0d51027503f400153af32^ANDcompany!=4b5325cb1b1e8110f241fe25cc4bcb66^ANDcompany!=df34ba6b1bfb4c90499233fccd4bcb75^ANDcompany!=c230290f40b52500673656a355b7a4c7^ANDcompany!=cea3269f1b176c50499233fccd4bcb69^ANDcompany!=484b781641e27d004ea6dc8785a7b88b^ANDcompany!=63eafd3f4fd58e00421faf7d0210c7a5^ORcompany=NULL"; //SysID of company to exclude

Here is the full code, updated with my sys_ids:

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
<script>
var my_dashboardMessageHandler = new DashboardMessageHandler("my_unique_id");

function compA() {
var filter_message = {};
filter_message.id = "my_unique_id";
filter_message.table = "task";

<!-- Add your own filter query logic here -->
filter_message.filter = "company=f66b14e1c611227b0166c3a0df4046ff^ORcompany=7830290f40b52500673656a355b7a49f^ORcompany=76fea4584f897e0080dbecee0210c783^ORcompany=e30bcf3c2c24550037878c092a26a912^ORcompany=544b781641e27d004ea6dc8785a7b890^ORcompany=1e919ffc2c24550037878c092a26a9df^ORcompany=ef4fec184f897e0080dbecee0210c731^ORcompany=8c9571bb1bef5c50469eda83cd4bcb6b^ORcompany=9ff3016197b0d51027503f400153af32^ORcompany=4b5325cb1b1e8110f241fe25cc4bcb66^ORcompany=df34ba6b1bfb4c90499233fccd4bcb75^ORcompany=c230290f40b52500673656a355b7a4c7^ORcompany=cea3269f1b176c50499233fccd4bcb69^ORcompany=484b781641e27d004ea6dc8785a7b88b^ORcompany=63eafd3f4fd58e00421faf7d0210c7a5"; //SysID of company to show
SNC.canvas.interactiveFilters.setDefaultValue({
id: filter_message.id,
filters: [filter_message]
}, false);
my_dashboardMessageHandler.publishFilter(filter_message.table, filter_message.filter);
}

function notComA() {
var filter_message = {};
filter_message.id = "my_unique_id";
filter_message.table = "task";

<!-- Add your own filter query logic here -->
filter_message.filter = "company!=f66b14e1c611227b0166c3a0df4046ff^ANDcompany!=7830290f40b52500673656a355b7a49f^ANDcompany!=76fea4584f897e0080dbecee0210c783^ANDcompany!=e30bcf3c2c24550037878c092a26a912^ANDcompany!=544b781641e27d004ea6dc8785a7b890^ANDcompany!=1e919ffc2c24550037878c092a26a9df^ANDcompany!=ef4fec184f897e0080dbecee0210c731^ANDcompany!=8c9571bb1bef5c50469eda83cd4bcb6b^ANDcompany!=9ff3016197b0d51027503f400153af32^ANDcompany!=4b5325cb1b1e8110f241fe25cc4bcb66^ANDcompany!=df34ba6b1bfb4c90499233fccd4bcb75^ANDcompany!=c230290f40b52500673656a355b7a4c7^ANDcompany!=cea3269f1b176c50499233fccd4bcb69^ANDcompany!=484b781641e27d004ea6dc8785a7b88b^ANDcompany!=63eafd3f4fd58e00421faf7d0210c7a5^ORcompany=NULL"; //SysID of company to exclude
SNC.canvas.interactiveFilters.setDefaultValue({
id: filter_message.id,
filters: [filter_message]
}, false);
my_dashboardMessageHandler.publishFilter(filter_message.table, filter_message.filter);
}

function clearFilter() {
var filter_message = {};
filter_message.id = "my_unique_id";
filter_message.table = "task";
filter_message.filter = "";
SNC.canvas.interactiveFilters.setDefaultValue({
id: filter_message.id,
filters: [filter_message]
}, false);
my_dashboardMessageHandler.removeFilter();
}

</script>
<input id="allTasks" type="button" value="All tasks" onclick="clearFilter();" />
<input id="CompA" type="button" value="Company A" onclick="compA();" />
<input id="notCompA" type="button" value="Not Company A" onclick="notComA();" />

</j:jelly>