cancel
Showing results for 
Search instead for 
Did you mean: 

Custom Pivot Table Formatting Script

cartercjb
9 - Travel Pro
9 - Travel Pro

Does anyone know how to update this widget script to achieve the DESIRED format I have outlined in the screenshot? Essentially, I'm looking to make each subsequent row total a different color, rather than all the same. 

current state vs. desired statecurrent state vs. desired state

CURRENT SCRIPT CODE

 

widget.on("ready",function(w, args) {
	var headerBgColor = '#a90a40'; //Header background
	var headerFontColor = '#F2F3F2'; //Header Font Color
	var footBgColor = '#44546A'; // Pivot Foot (Totals)
	var footFontColor = '#F2F3F2'; // Pivot Column (dims)
	var colFontColor = '#5690a2';
		// Style
		var $jx = $(".p-grand-total-head,.p-dim-head,.p-dim-member-head,.p-measure-head", element);
			$jx.css('font-size' , '12px');
			$jx.css('font-weight' , 'bold');
			$jx.css('color' , headerFontColor);
			$jx.css('background-color' , headerBgColor);
			$jx.css('text-align' , 'center'); 
			$jx.css('vertical-align' , 'middle'); 
		//center values and col	
			$(".p-value", element).css('text-align', 'center');
			$(".p-dim-head", element).css('text-align', 'left');
		// row totals	
		var $jx= $(".p-total-row-val,.p-total-row-head", element);
			$jx.css('font-size' , '12px');
			$jx.css('font-weight' , 'bold');
			$jx.css('color' , footFontColor);
			$jx.css('background-color' , footBgColor);
			$jx.css('border-top', 'none');		
		// col
		var $jx = $(".p-dim-member", element);
			$jx.css('font-size' , '10px');
			$jx.css('font-weight' , 'bold');
			$jx.css('color' , colFontColor);
			$jx.css('text-align' , 'left'); 		
	    var $jx = $(".p-value", element);
			$jx.css('font-size' , '10px');			
		// boarders
		var $jx = $("[fidx]", element);
			$jx.css('border-bottom', 'thin solid ' + colFontColor);
			$jx.css('border-right', 'thin solid ' + colFontColor);
})

 

 Thank you!

8 REPLIES 8

cartercjb
9 - Travel Pro
9 - Travel Pro

Ophir
Sisense Team Member
Sisense Team Member

@cartercjb - Can you please provide a screenshot of the current visualization (taken from the Sisense Web Interface)?

@Ophir thanks for your quick reply. This screenshot is the output of the code I pasted in my original question. Essentially I'm looking for the RESIDENT subtotal, PAYER subtotal, and GRAND TOTAL to all be different colors.Snag_12e49c2e.png

 

Ophir
Sisense Team Member
Sisense Team Member

Try to use the following legend:

Ophir_1-1640015750946.png

 

Thank you, @Ophir .  I made some progress - I was able to format the Grand Total header. However, the Grand Total Values are getting picked up based on the //row totals section. Is there not a object reference for .p-grand-total-row-val?

Also, where did you get that screenshot from? Do you have another reference that shows how to differentiate between sub-total rows, first level and second level? The screenshot provided only shows 1 level. 

widget.on("ready",function(w, args) {
	var headerBgColor = '#a90a40'; //Header background
	var headerFontColor = '#F2F3F2'; //Header Font Color
	var footBgColor = '#44546A'; // Pivot Foot (Totals)
	var footFontColor = '#F2F3F2'; // Pivot Column (dims)
	var colFontColor = '#5690a2';
		// Style
		var $jx = $(".p-dim-head,.p-dim-member-head,.p-measure-head", element);
			$jx.css('font-size' , '12px');
			$jx.css('font-weight' , 'bold');
			$jx.css('color' , headerFontColor);
			$jx.css('background-color' , headerBgColor);
			$jx.css('text-align' , 'center'); 
			$jx.css('vertical-align' , 'middle'); 
		// GRAND TOTAL
		var $jx = $(".p-grand-total-head,.p-grand-total-row-head,.p-grand-total-val", element);
			$jx.css('font-size' , '12px');
			$jx.css('font-weight' , 'bold');
			$jx.css('color' , headerFontColor);
			$jx.css('background-color' , headerBgColor);
			$jx.css('text-align' , 'left'); 
			$jx.css('vertical-align' , 'middle'); 
		//center values and col	
			$(".p-value", element).css('text-align', 'center');
			$(".p-dim-head", element).css('text-align', 'left');
		// row totals	
		var $jx= $(".p-total-row-val,.p-total-row-head", element);
			$jx.css('font-size' , '12px');
			$jx.css('font-weight' , 'bold');
			$jx.css('color' , footFontColor);
			$jx.css('background-color' , footBgColor);
			$jx.css('border-top', 'none');		
		// col
		var $jx = $(".p-dim-member", element);
			$jx.css('font-size' , '10px');
			$jx.css('font-weight' , 'bold');
			$jx.css('color' , colFontColor);
			$jx.css('text-align' , 'left'); 		
	    var $jx = $(".p-value", element);
			$jx.css('font-size' , '10px');			
		// boarders
		var $jx = $("[fidx]", element);
			$jx.css('border-bottom', 'thin solid ' + colFontColor);
			$jx.css('border-right', 'thin solid ' + colFontColor);
})

Snag_13133e2b.png

Ophir
Sisense Team Member
Sisense Team Member

Hi @cartercjb ,

Can I please ask you to send me a screenshot of the left pane (showing the rows/values/columns)?

Thanks,
Ophir

@Ophir

  Snag_1416d34b.png

 

Ophir
Sisense Team Member
Sisense Team Member

Hi @cartercjb 

Unfortunately, you can't use JQuery to customize "per-row" attributes (as they use the same class name.

Saying that, you can use the following code to do so...

Start off by adding the following code to your widget (prior to your code):

function getWidgetDocumentObject(widget) {
// This function determines the document object context (different when in a widget-view or a dashboard-view mode)
if (prism.activeWidget == null)
return document.querySelector('[widgetid="' + widget.oid + '"]');
else
return document;
}

Next, use a code similar to the below to control per-cell attributes:

widget.on('domready', function(widget) {

doc = getWidgetDocumentObject(widget);
document.querySelector('#pivot_').querySelectorAll('.p-total-row-val')[0].style.background = 'red'
});

Note - You can use loops (e.g., forEach) and attributes (.length) to generalize your code