Drill To Buttons

our userss have complained that having to teach people how to right click and find the drill feature, they'd instead like a button at the top of the visual where they can click and it applies the drill function. i'm sure there is a jaql query or implementation we could do for this??

liamcameron

Posted 2 years ago·Last reply 1 year ago

7 comments

Ido Darnell

·1 year ago

Hi All liamcameron gwolfe HQ_Dev_Prod and everyone else!

tl;dr - QBeeQ have a powerful Widget Controller plugin which can solve the initial request above and improve end user UX to do more on their own

End uses are expecting to do more with their analytics, the "data appetites" of these viewers which we build our dashboards for are constantly growing, and the UX expectations too.

This is something that has come up often with our customers' end users, they want to have more flexibility to do more on their own, to be masters of their own adhoc analytics, while reducing their dependance on the data builders, the dashboard designers.

This is the main reason why we created our powerful Self Service Analytics PowerUp for Sisense.

One of the more powerful, yet simplistic, plugins available their is our Widget Controller which allows designers to add new sets of controls to any widget, native visuals, tables, maps, or any of our powerful Advanced Data Viz PowerUp plugins.

Specifically, it allows for adding dimensions and measure controls to widgets, for this conversation I believe what can solve the original question is our Dimension Switcher. It basically allows viewers to completely switch the entire dimension to any other, from within the UI of the widget, without having to go into the widget editor, and without having to request for a change iteration from the dashboard designers, something that can take a while.

The great thing about it is that it';s right there in the widget UI so no hidden UI or need for enablement or training. Plus the designers have complete control over what dimensions and measures to allow for use, so the viewers are not fully exposed to the entire data schema (oy vey..)

All our plugins are no-code and super easy to set up, no need for long scripts, or to maintain them, for multiple dashboards, widgets etc, just a few clicks and viola!

Compared to other solutions available out there, our Widget Controller includes a dimension switcher, measure switcher, break by switcher, chart type switcher ALL IN ONE! (other solutions require 4 or more plugins to achieve these functionalities)

Feel free to reach out for more info or if we can help in any way!

Don't hesitate to reach out!

Ido from QBeeQ

QBeeQ - Gold Sisense Implementation and Development Partner

Feel free to subscribe to our newsletter to stay up to date with what we're up to

info@qbeeq.io

Assaf Hanina

·2 years ago

hey liamcameron and gwolfe ,

Based on the screenshot provided, it appears that the goal is to identify the widgets that have the drill hierarchies feature, allowing end users to utilize this functionality.
Having an Indication of Drill can be set up Using the Script Below.

Generally, the Drill Hierarchy feature offers two options:

Automatically including the hierarchy when adding dimensions.
Selecting the hierarchy at the time of adding dimensions.

See Related Screenshot

Script Overview

Check all the Panels for Each widget for Hierarchies/activeHierarchies element.
For Automatically including the hierarchy - the element exist only after selecting a hierarchy, otherwise the script will not indicate that the widget can be drilled.
Steps to Follow:
- Add the dimension column
- save the widget
- right click on hierarchy and click on drill
- Hierarchies/activeHierarchies element will be available and the Icon will appear.
For Selected Hierarchies
- Add the Dimension Column
- Select the Hierarchy
- Hierarchy element will be available
Include an icon indicating drill functionality in the widget title:
- The icon is placed before the widget title without overwriting it.
- The icon can be replaced in the drill_icon variable
- The icon position can be adjusted.
Add a guide for the end user to the widget description:
- The description is initially predefined but can be customized in the additional_text variable
- Append the information to the existing widget description.

Implementation

Open the Dashboard Options.
Navigate to Edit Script.
Paste the script provided below.
Refresh the dashboard to apply the changes.

Dashboard Script

/* update the following variable */ 

var drill_icon = '<span class="lia-unicode-emoji" title=":downwards_button:">🔽</span> '; /* replace with the icon */ 
var additional_text = ' Right Click to Drill Hierarchies'; /* replace with the widget description */ 


dashboard.on("refreshend", function(se, ev) {
    // Get all widgets from the dashboard
    var widgets = se.widgets.$$widgets;
    // Iterate through each widget
    widgets.forEach(function(widget) {
        // Get the widget's metadata panels and other necessary properties
        var widgets_metadata = widget.metadata.panels;
        var widget_oid = widget.oid;
 
        // Iterate through each panel in the widget's metadata
        widgets_metadata.forEach(function(panel) {
            // Iterate through each item in the panel
            panel.items.forEach(function(item) {
 
// Check if the item has hierarchies
                if (item.hierarchies || item.activeHierarchy) {
                    $(`widget[widgetid="${widget_oid}"] widget-header .app-icon--general-info-circle`).css('color', 'red');
                    $(`widget[widgetid="${widget_oid}"] widget-header .app-icon--general-info-circle`).closest('.widget-toolbar-btn').css('opacity', '1');
 
                    // Adding widget description and Icon to the title
                    if (!widget.title.includes(drill_icon)) {
                        widget.title = drill_icon + widget.title;
                    }
                    if (!widget.desc.includes(additional_text)) {
                        widget.desc += additional_text;
                    }
                } 
            });
        });
    });
});

Best Regards

HQ_Dev_Prod

·1 year ago

Is it possible for this to be automatic? Meaning, If I click on a bar in a chart to create the filter, can it also automatically drill into the next level in the hierarchy without me having to right click into the drill down?

Assaf Hanina

·1 year ago

hey HQ_Dev_Prod ,

The Script Doesn't support it and Natively it's not supported.
it might be possible using Script but required additional development and Checks.
Additionally, not sure if that's the best user experience to Filter and Drill at the Same time as it's missing the Primary Category.

Alternatively, Consider having Blox Action to Add Secondary Dimension which can behave as Drill and can provide more User Experience. Please find a community example for Pivot table.

Best Regards

Tri Anthony

·2 years ago

Hi liamcameron and gwolfe,

Since drilling is typically performed on a single data point (by right clicking the data point), or several data points (by highlighting / dragging and dropping using the left mouse button) of interest, having "drill" buttons at the top of the widget will not achieve the same effect as the out-of-the-box drill feature.

Let's say we have a widget that shows Revenue by Age Range and the users can drill into one of these four columns: Year, Gender, Country, and Product Category. A typical drill flow would involve selecting a specific age range of interest, such as "35-44", and then drilling down into another dimension, like countries.

If the drill options were added as buttons at the top of the widget (like shown in the screenshot below), how would users choose which data point to drill from? Would the drilling be performed on all age ranges? If so, this is effectively the same as replacing the widget's category column from Age Range to the selected drill column.

If the requirement here is to just add additional granularity into the widget by adding and switching a second category (or Break By column), it can be achieved with a widget script. See examples below.

Gender as the second category:

Year as the second category:

You can find below the widget script for this example. If you'd like to reuse this script, you can update the drillOptionXTitle and drillOptionXJAQL constants with your own titles and JAQLs. Note that you need to manually add a second category (could be one of the drilling options) to the widget first before applying the script.

I also attached an example dashboard below. Note that our community site doesn't currently support .dash file attachments, so please change the file extension from .txt to .dash before you import the dashboard.

//Define the drilling options' title in a list of constants like shown below
const drillOption1Title = 'Year'
const drillOption2Title = 'Gender'
const drillOption3Title = 'Country'
const drillOption4Title = 'Category'

//Define the drilling options' JAQL in a list of constants like shown below
const drillOption1JAQL = {
    "table": "Commerce",
    "column": "Date",
    "dim": "[Commerce.Date (Calendar)]",
    "datatype": "datetime",
    "merged": true,
    "level": "years",
    "title": "Years in Date"
}

const drillOption2JAQL = {
    "table": "Commerce",
    "column": "Gender",
    "dim": "[Commerce.Gender]",
    "datatype": "text",
    "merged": true,
    "title": "Gender"
}

const drillOption3JAQL = {
    "table": "Country",
    "column": "Country",
    "dim": "[Country.Country]",
    "datatype": "text",
    "merged": true,
    "title": "Country"
}

const drillOption4JAQL = {
    "table": "Category",
    "column": "Category",
    "dim": "[Category.Category]",
    "datatype": "text",
    "merged": true,
    "title": "Category"
}

//Set margin for the buttons
widget.on('processresult', function(se, ev){	
	ev.result.chart.marginTop = 90;
});

var selectedDrillOption = 'None';

widget.on("domready", function(w, args){
	
	chart = w.chart[0][Object.keys(w.chart[0])[0]].hc;
	
	chart.renderer.label('Drill into:</br>' + '<b>' + selectedDrillOption + '</b>', 30, 10)
         .css({
            fontSize: '14px'
         })
         .attr({
			zIndex : 10,
			height: 15,
			width: 50,
			'text-align': 'center'
         })
         .add();
	
	chart.renderer.button('None', 120, 10)
		.attr({
			zIndex : 10,
			height: 15,
			width: 50,
			'text-align': 'center'
		})
		.on('click', function() {
			selectedDrillOption = 'None';
			widget.metadata.panels[0].items[1].disabled = true;
			widget.refresh();
		})
		.add();
	
	chart.renderer.button(drillOption1Title, 200, 10)
		.attr({
			zIndex : 10,
			height: 15,
			width: 50,
			'text-align': 'center'
		})
		.on('click', function() {
			selectedDrillOption = drillOption1Title;
			widget.metadata.panels[0].items[1].disabled = false;
			widget.metadata.panels[0].items[1].jaql = drillOption1JAQL;
			widget.refresh();
		})
		.add();
		
	chart.renderer.button(drillOption2Title, 280, 10)
		.attr({
			zIndex : 10,
			height: 15,
			width: 50,
			'text-align': 'center'
		})
		.on('click', function() {
			selectedDrillOption = drillOption2Title;
			widget.metadata.panels[0].items[1].disabled = false;
			widget.metadata.panels[0].items[1].jaql = drillOption2JAQL;
			widget.refresh();
		})
		.add();
	
	chart.renderer.button(drillOption3Title, 360, 10)
		.attr({
			zIndex : 10,
			height: 15,
			width: 50,
			'text-align': 'center'
		})
		.on('click', function() {
			selectedDrillOption = drillOption3Title;
			widget.metadata.panels[0].items[1].disabled = false;
			widget.metadata.panels[0].items[1].jaql = drillOption3JAQL;
			widget.refresh();
		})
		.add();
	
	chart.renderer.button(drillOption4Title, 440, 10)
		.attr({
			zIndex : 10,
			height: 15,
			width: 50,
			'text-align': 'center'
		})
		.on('click', function() {
			selectedDrillOption = drillOption4Title;
			widget.metadata.panels[0].items[1].disabled = false;
			widget.metadata.panels[0].items[1].jaql = drillOption4JAQL;
			widget.refresh();
		})
		.add();
});

Rose Holmes-Minton

·1 year ago

I tried to modify the above code but, it didn't work.

//Define the drilling options' title in a list of constants like shown below

const drillOption1Title = 'Section Type '

const drillOption2Title = 'Program Name'

//Define the drilling options' JAQL in a list of constants like shown below

const drillOption1JAQL = {

"table": "Personnel - Board Certifications",

"column": "Board Certifications - Section Type",

"dim": "[Personnel - Board Certifications.Board Certifications - Section Type]",

"datatype": "text",

"merged": true,

"title": "Section Type"

}

const drillOption2JAQL = {

"table": "Personnel - Board Certifications",

"column": "Board Certifications - Program Name",

"dim": "[Personnel - Board Certifications.Board Certifications - Program Name]",

"datatype": "text",

"merged": true,

"title": "Program Name"

}

//Set margin for the buttons

widget.on('processresult', function(se, ev){

ev.result.chart.marginTop = 90;

});

var selectedDrillOption = 'None';

widget.on("domready", function(w, args){

chart = w.chart[0][Object.keys(w.chart[0])[0]].hc;

chart.renderer.label('Drill into:</br>' + '<b>' + selectedDrillOption + '</b>', 30, 10)

.css({

fontSize: '14px'

})

.attr({

zIndex : 10,

height: 15,

width: 50,

'text-align': 'center'

})

.add();

chart.renderer.button('None', 120, 10)

.attr({

zIndex : 10,

height: 15,

width: 50,

'text-align': 'center'

})

.on('click', function() {

selectedDrillOption = 'None';

widget.metadata.panels[0].items[1].disabled = true;

widget.refresh();

})

.add();

chart.renderer.button(drillOption1Title, 200, 10)

.attr({

zIndex : 10,

height: 15,

width: 100,

'text-align': 'center'

})

.on('click', function() {

selectedDrillOption = drillOption1Title;

widget.metadata.panels[0].items[1].disabled = false;

widget.metadata.panels[0].items[1].jaql = drillOption1JAQL;

widget.refresh();

})

.add();

chart.renderer.button(drillOption2Title, 330, 10)

.attr({

zIndex : 10,

height: 15,

width: 100,

'text-align': 'center'

})

.on('click', function() {

selectedDrillOption = drillOption2Title;

widget.metadata.panels[0].items[1].disabled = false;

widget.metadata.panels[0].items[1].jaql = drillOption2JAQL;

widget.refresh();

})

.add();

});

Garrett Wolfe

·2 years ago

I would also like to know how to do this.