Scale Trellis Chart Intelligently for Better Visualization and Data Discovery

Trellis Chart Type is an excellent way to create multiple charts dynamically. Design Studio scales value axis automatically, but default scaling is not always optimal. With the help of BIAL script value axis can be scaled intelligently improving visualization and making data discovery easier.

Below is an example of Trellis Chart with default value axis scaling

The same Trellis Chart with intelligent value axis scaling looks way better

So how intelligent scaling is working? Simply put, BIAL finds the lowest and the highest values, then adds a small margin to the highest value and subtracts small margin from the lowest value. Finally BIAL sets value axis min value and max value to adjusted lowest value and adjusted highest value respectively.

There are two ways to find the highest and the lowest values:

1. Loop through all data points at front-end
2. Leverage back-end

Loop through all data points at front-end to find the highest and the lowest values

This approach is the easiest since it does not require back-end changes, but performance can be not optimal if data volume is big

And here is the BIAL script itself:

var value = 0.0;
var value_max = 0.0;
var value_min = 0.0;
var month = DS_1.getMembers("0CALMONTH", 999);
var distr_chan = DS_1.getMembers("0DISTR_CHAN", 999);
// loop through data points and find the highest value
month.forEach(function(element1, index1) {
	distr_chan.forEach(function(element2, index2) {
    	value = DS_1.getData("003N82SVY07W68E7H4C243EGM",{"0CALMONTH":element1.internalKey,"0DISTR_CHAN":element2.internalKey}).value;
    	if ( value_max < value ) {
    		value_max = value;
    	}    	
    	value = DS_1.getData("003N82SVY07W68E7H4C243852",{"0CALMONTH":element1.internalKey,"0DISTR_CHAN":element2.internalKey}).value;
    	if ( value_max < value ) {
    		value_max = value;
    	} 	    	
	});
});
// loop through data points and find the lowest value
value_min = value_max;
month.forEach(function(element1, index1) {
	distr_chan.forEach(function(element2, index2) {
    	value = DS_1.getData("003N82SVY07W68E7H4C243EGM",{"0CALMONTH":element1.internalKey,"0DISTR_CHAN":element2.internalKey}).value;
    	if ( value_min > value ) {
    		value_min = value;
    	}    	
    	value = DS_1.getData("003N82SVY07W68E7H4C243852",{"0CALMONTH":element1.internalKey,"0DISTR_CHAN":element2.internalKey}).value;
    	if ( value_min > value ) {
    		value_min = value;
    	} 	    	
	});
});
// set margin above highest value and below lowest value
value =  ( value_max - value_min ) * 0.05;
value_min = value_min - value;
value_max = value_max + value;
// scale value axis
CHART.setAxisScaling(InfoChartAxisScaling.AXIS_1, value_min, value_max);

Leverage back-end to find the highest and the lowest values

This approach requires BW Query changes (maximum and minimum key figures with aggregation type maximum and minimum respectively), but provides the best performance. Also additional DataSource is required in Design Studio application for maximum and minimum key figures (it is required to maintain the same drill down as in chart DataSource).

And here is the BIAL script itself:

// Remove drill down
var dimension = DS_1_MIN_MAX.getDimensions(Axis.ROWS);
dimension.forEach(function(element, index) {
	DS_1_MIN_MAX.removeDimension(element);
});
dimension = DS_1_MIN_MAX.getDimensions(Axis.COLUMNS);
dimension.forEach(function(element, index) {
	DS_1_MIN_MAX.removeDimension(element);
});
// Add drill down the same as in Chart
dimension = DS_1.getDimensions(Axis.ROWS);
dimension.forEach(function(element, index) {
	DS_1_MIN_MAX.moveDimensionToRows(element);
});
dimension = DS_1.getDimensions(Axis.COLUMNS);
dimension.forEach(function(element, index) {
	DS_1_MIN_MAX.moveDimensionToColumns(element);
});

// Find Min and Max
var value = 0.0;
var value_max = 0.0;
var value_min = 0.0;
value = DS_1_MIN_MAX.getData("003N82SVY07UN2VK3YS3UOWP5",{}).value;
if ( value_max < value ) {
	value_max = value;
}
value = DS_1_MIN_MAX.getData("003N82SVY07UN2VK5SFAP8W3T",{}).value;
if ( value_max < value ) {
	value_max = value;
}
value_min = value_max;
value = DS_1_MIN_MAX.getData("003N82SVY07UN2VK3YS3UOQDL",{}).value;
if ( value_min > value ) {
	value_min = value;
}
value = DS_1_MIN_MAX.getData("003N82SVY07UN2VK5SFAP8PS9",{}).value;
if ( value_min > value ) {
	value_min = value;
}
// set margin above highest value and below lowest value
value =  ( value_max - value_min ) * 0.05;
value_min = value_min - value;
value_max = value_max + value;
// scale value axis
CHART.setAxisScaling(InfoChartAxisScaling.AXIS_1, value_min, value_max);