Forum Discussion

cartercjb's avatar
cartercjb
ETL
12-20-2021

Custom Pivot Table Formatting Script

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 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!

Blox
Code-first Analytics
dashboards & reporting

8 Replies

Replies have been turned off for this discussion
    • Ophir_Buchman's avatar
      Ophir_Buchman
      Data Integration
      12-20-2021

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

      • cartercjb's avatar
        cartercjb
        ETL
        12-20-2021

        Ophir_Buchman 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.

         

    • cartercjb's avatar
      cartercjb
      ETL
      12-20-2021

      Thank you, Ophir_Buchman .  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);
})

       

  • Ophir_Buchman's avatar
    Ophir_Buchman
    Data Integration
    12-20-2021

    Hi cartercjb ,

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

    Thanks,
    Ophir

  • Ophir_Buchman's avatar
    Ophir_Buchman
    Data Integration
    12-21-2021

    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