How to make a table visual have the headers on the left side?

[fernie]

Hello folks!

We have a certain table visual that displays a header row with one row for multiple metrics, like such:

We are wondering if there’s a way to “flip” the table so that it becomes a vertical table, with the headers as a column on the left side, and the metrics on the right side.

Thank you

[taddwood]

Hi Fernie,

This capability isn’t available or supported by default in Arcadia, but you can use our Custom Style feature to modify the table visual and make it a vertical table:

Here’s an example of what the output looks like after applying the Custom Style:

To create the Custom Style, you need to add the following CSS and Javascript

CSS:

.simple-table {
}
.simple-table th,
.simple-table td {
  padding:4px 8px;
  font-size:12px;
}
.simple-table thead th {
  text-transform:uppercase;
  font-size:11px;
}

JS:

return {
    version: "1",
    disableDraw: function () {
        return true;
    },
    supported: {
        visualType: "table"
    },
    afterDraw: function () {
        var data = arcapi.dataResult();
        var columns = data.columns(); // all data columns
        var rows = data.rows(); // all data rows
        var $chart = $("#" + arcapi.chartId()); // the div for drawing the chart
        var $table = $('<table class="table table-width-100 table-striped table-firstcolumn header-align-left table-totals dataTable" id="ver_table' + arcapi.chartId() + '">'); // the div for drawing the chart
        var dimIdx = data.dim_start_idx();
        var aggStartIdx = data.agg_start_idx();
        var aggEndIdx = data.agg_end_idx();
        var $header = $("<thead>");
       
        var $headerRow = $("<tr role='row' class='odd' >");
        var $body = $("<tbody>");
        var tableRows = {};
        
        if (dimIdx === data.dim_end_idx()) {
            
            if ($("#ver_table" + arcapi.chartId()).length > 0 ){
                $("#ver_table" + arcapi.chartId()).remove();
                
            }
           
            $chart.append($table);
            $table.append($body);
            rows.forEach(function(columnValues, colIdx) {
                columnValues.forEach(function(rowValue, rowIdx){
                    if (rowIdx >= aggStartIdx && rowIdx <= aggEndIdx ) {
                        var $row;
                        if (tableRows[rowIdx]) {
                            $row = tableRows[rowIdx];
                        } else {
                            $row = $("<tr>");
                            tableRows[rowIdx] = $row;
                            $body.append($row);
                            $row.append(makeHeaderCell(columns[rowIdx].colname()));
                        }
                        $row.append(makeCell(rowValue));
                    }
                });
                $headerRow.append(makeHeaderCell(columnValues[dimIdx]))
            });
            
        } else {
            console.log("Only 1 dimension allowed");
        }
        
        function makeHeaderCell(cellValue) {
            return "<th>" + cellValue + "</th>"
        }
        function makeCell(cellValue) {
            return "<td>" + cellValue + "</td>"
        }
    },
};

Thank you,
Tadd Wood

[fernie]

Wow Tadd! This is great!
Thank you so much for the help!

[fernie]

Hi Tadd, I’m a newbie when it comes to web development

Do you know how I can get rid of the vertical scrollbar on the right? The widget is big enough to fit all fields but it still shows up. I added simple-table to the widget’s class and added to the .simple-table the property in the CSS overflow-y: hidden with no luck.

Any ideas?

[taddwood]

Fernie,

Try adding adding the following :

::-webkit-scrollbar { 
    display: none; 
}

You can add this at the Visual level:

Or at the Dashboard level if you want the scroll bar to disappear across more visuals (I think):

[fernie]

Thank you again Tadd.
While that resolved the issue of having the scrollbar, the widget itself is still moving as if there was a scrollbar.
Please see the attached gif and you will see that at first I’m scrolling the whole page (you can tell by the piece of a graph that I have on the left side moving as I scroll), but when I mouse over the widget, the widget moves as if there was a scrollbar there.

Any idea of what could be going on?

Thank you once again for the time

[taddwood]

Fernie,

What browser are you using and what version of Arcadia?

[fernie]

Chrome 69 and Arcadia 4.2.0

[shaun]

Fernie - can you try applying the following CSS as shown in the below screenshot. See if that helps.

============= CUSTOM CSS =================

.striped .dash-viz-wrapper {
overflow: auto !important;
padding: 0px !important;;
}
.striped.arc-viz-container
{
margin: 0px !important;
overflow: unset;
}
===========================================

[fernie]

I’m using table-condensed and table-hover, so instead of “striped” I used “condensed”.
It worked! Wow! Thank you guys for the help and support.

[fernie]

One thing to note is that when I have two of those widgets (which is what the dashboard I’m creating will have), under each other, they don’t line up and also only one shows the data, the other one is blank.

Please see gif attached.
When I double-click on the widget that has no data, it then displays the data. But the other widget stops showing data. And now there’s no border, so it’s hard to line up with the graph widgets on their left side.

[taddwood]

Fernie,

I made a small tweak in the JS code to fix the issue. Retry it and let me know if both tables now show up.

Thank you,
Tadd Wood

[ankitamod]

I tried using this code but I am having difficulties in creating multiple columns in the transposed table -

1. I used 2 columns in dimension, but the header is not appearing on columns
2. Unable to retain the data type in transposed table for ex - currency sign is getting lost

[shaun]

@ankitamod The Javascript / custom style in the above example completely overwrites the existing out of the box arcadia table.
So you will have to perform the display formatting within the javascript itself. The out of the box display format is no longer applicable here.

Also, the javascript is only for series of measures. If you want to use dimensional values you will have to tweak the javascript to fit your needs.

Hope that helps.

[ankitamod]

Thanks Shaun… Can you give a sample on how to read dimensional values in the table?

[shaun]

@ankitamod We provide an JS API to obtain the results of the query (the entire matrix of rows/columns). You can refer to following article for example:

Using custom style / extension JS to read visual results

When using custom styles to write JS you usually need a way to read the results of the query. Arcadia provides JS API to read query results and you can find the more information here: http://documentation.arcadiadata.com/4.5.0.0/#pages/topics/arcapi-cust-exten-js-apis.html#arcapi-cust-exten-js-apis-dataresult-functions In the below example we have created a simple KPI tabular visual, and using the arcapi.dataResult() function we will obtain the query results and send out the value as a parame…

[Purnesh]

I have used your above Java Script, when i’m using the custom style, i’m unable to apply display format.

for example if the number is 1000 then it should come 1K like that. At the column level and at the dataset level, i’m applying but the conversion is not happening.

[taddwood]

@Purnesh since this Custom Style completely overrides the Visual settings, your display formats won’t be passed through. You’ll need to customize this style further to intercept the values in your table and modify the types using the d3 format library.

[Purnesh]

Hi Tadd,

Thank you for the suggestion, I’m not an expert of JS but as you have suggested i could intercept numerical columns and currently i can get required result by running the below code.

Currently when i run this code i’m facing an issue w.r.t String columns, for example if my output contains numerical columns along with String columns. The below code is converting the numerical columns, but it is failing w.r.t String columns (unable to pass string values - for example if string column contains the value abc, in the output for that string column i’m getting NaN instead of abc). Can you please look into it and let me know.

return {
version: “1”,
disableDraw: function () {
return true;
},
supported: {
visualType: “table”
},
afterDraw: function () {
var data = arcapi.dataResult();
var columns = data.columns(); // all data columns
var rows = data.rows(); // all data rows
var chart = ("#" + arcapi.chartId()); // the div for drawing the chart
var table = (’

’); // the div for drawing the chart
var dimIdx = data.dim_start_idx();
var aggStartIdx = data.agg_start_idx();
var aggEndIdx = data.agg_end_idx();
var header = ("");

    var $headerRow = $("<tr role='row' class='odd' >");
    var $body = $("<tbody>");
    var tableRows = {};
    
    if (dimIdx === data.dim_end_idx()) {
        
        if ($("#ver_table" + arcapi.chartId()).length > 0 ){
            $("#ver_table" + arcapi.chartId()).remove();
            
        }
       
        $chart.append($table);
        $table.append($body);
        rows.forEach(function(columnValues, colIdx) {
            columnValues.forEach(function(rowValue, rowIdx){
                if (rowIdx >= aggStartIdx && rowIdx <= aggEndIdx ) {
                    var $row;
                    if (tableRows[rowIdx]) {
                        $row = tableRows[rowIdx];
                    } else {
                        $row = $("<tr>");
                        tableRows[rowIdx] = $row;
                        $body.append($row);
                        $row.append(makeHeaderCell(columns[rowIdx].colname()));
                    }
                    $row.append(makeCell(rowValue));
                }
            });
            $headerRow.append(makeHeaderCell(columnValues[dimIdx]))
        });
        
    } else {
        console.log("Only 1 dimension allowed");
    }
    
    function makeHeaderCell(cellValue) {
        return "<th>" + cellValue + "</th>"
    }
    function makeCell(cellValue) {
     
      var thousand = 1000;
      var million = 1000000;
      var billion = 1000000000;
      var trillion = 1000000000000;
      
         if (cellValue < thousand) {
             return String(cellValue);   
          }

          if (cellValue >= thousand && cellValue <= million) {
            return  Math.round(cellValue/thousand) + 'K';   
           }

            if (cellValue >= million && cellValue <= billion) {
              return Math.round(cellValue/million) + 'M';   
            }

            if (cellValue >= billion && cellValue <= trillion) {
            return Math.round(cellValue/billion) + 'B';   
            }
            
        else {
          return Math.round(cellValue/trillion) + 'T';   
             }

}
},
};

[Karthik]

Hi @ankitamod,
I’m facing issue in pivoting dimension column into row. is there a solution/custom script for the same?

Thank you.