Financial Reporting - Customizing Pivot Layout
Financial reporting sometimes lends itself toward displaying data neatly within a table, rather than with advanced visualizations. Industry standards for several use cases - such as a balance sheet - are well established which requires severing end users a clean looking table with a particular format.
Attached is an example of using the Sisense pivot with javascript to transform the table to a format more familiar to finance departments. Below you will find the JS code behind the tables.
Note on the data: The data used in the attached example can be found at sec.gov. The data was set up with 'Table' as the table name and Label as the line item. The Labels roll up into Subcategory, which again roll up into Category (i.e. Label 'Inventories' rolls into Subcategory 'Current Assets' which rolls into Category 'Assets').
// Set background to white and remove boarders
widget.transformPivot( {},
function setCellBackground(metadata, cell) {
cell.style = cell.style || {};
cell.style.borderColor = '#FFFFFF';
cell.style.background = '#FFFFFF';
});
// Bold and underline subtotals row
widget.transformPivot( {
type: ['subtotal']
},
function setCellBorder(metadata, cell) {
cell.style = cell.style || {};
cell.style.borderColor = '#ABB0B4 #FFFFFF #ABB0B4 #FFFFFF';
cell.style.fontWeight = 'bold';
});
// Hide titles
// Hide 'Category' Title
widget.transformPivot( {
type: ['member'], rows: [{
dim: '[Table.Category]',
members: ['Category']
}]},
function setHideCategory(metadata, cell) {
cell.style = cell.style || {};
cell.style.color = '#FFFFFF';
});
// Hide 'Subcaregory' title
widget.transformPivot( {
type: ['member'], rows: [{
dim: '[Table.Subcategory]',
members: ['Subcategory']
}]},
function setHideSubcategory(metadata, cell) {
cell.style = cell.style || {};
cell.style.color = '#FFFFFF';
});
// Hide 'Label' title
widget.transformPivot( {
type: ['member'], rows: [{
dim: '[Table.Label]', members: ['Label']
}]},
function setHideLabel(metadata, cell) {
cell.style = cell.style || {};
cell.style.color = '#FFFFFF';
});
