Row Expression

1 Usages of Row Expression

JavaScript expression can be data values when manufacture/trim/calculate data or generate chart.

2 Edit Row Expression

  1. If the script is blank, then return empty string.

  2. It can include any valid elments which Nashorn can parse(ECMAScript 5.1).

  3. It should be a value finally.

  4. It can include following placeholders which are regarded as variables in the script:

    #{TableRowNumber}
    #{DataRowNumber}
    #{<column_name>}
    #{<column_name>- <statistic-name>}

  5. All valid placeholders are listed in left.

  6. To save the script, click "Edit" button.

  7. Hover or click button "Examples" to paste example codes.

  8. Hover or click button "Histories" to paste codes in histories.

3 Calculate Row Expression

When MyBox evaluates the expression:

  1. Placeholders are replaced with internal variables which are mapped as actual values of each data row.

  2. Statistic values are calculated by all data.

  3. When handles all pages, script fails when it includes "#{TableRowNumber}" .

4 Examples

row expression meaning
#{DataRowNumber} % 2 == 0 data row number is even
#{TableRowNumber} % 2 == 1 odd rows in current page
Math.abs(#{numberColumn1}) + Math.PI * Math.sqrt(#{numberColumn2}) calculation
#{numberColumn1} - #{numberColumn2-Mean} difference between value of "numberColumn1" and mean of "numberColumn2" 
if ( #{stringColumn} != null ) 
    #{stringColumn}.length
else
    -1
 length of "stringColumn" 
if ( #{stringColumn} != null ) 
    #{stringColumn}.replace(/hello/ig,'Hello')
else
    null
 replace all "hello"(case-insensitive) as "Hello" in "stringColumn" 
if ( #{stringColumn} != null ) 
    #{stringColumn}.toLowerCase()
else
    null
 lower case of value of "vstringColumn1" 
if ( #{stringColumn} != null ) 
    #{stringColumn}.split(',')
else
    null
 split value of "stringColumn" by comma 
function formatDate(date) {
     var y = date.getFullYear();
     var m = date.getMonth() + 1;
     m = m < 10 ? ('0' + m) : m;
     var d = date.getDate();
     d = d < 10 ? ('0' + d) : d;
     var h =date.getHours();
     h = h < 10 ? ('0' + h) : h;
     var M =date.getMinutes();
     M = M < 10 ? ('0' + M) : M;
     var s =date.getSeconds();
     s = s < 10 ? ('0' + s) : s;
     return y + '-' + m + '-' + d + ' ' + h + ':' + M + ':' + s;
}
if (#{dateColumn} != null)
   formatDate(new Date(#{dateColumn}));
else
   null;
 format value of "dateColumn" 
if ( #{dateColumn} != null ) 
    new Date(#{dateColumn}).getFullYear()
else
    null
 year of value of "dateColumn"

Row Filter

1 Usages of Row Filter

"Row Filter" is special "Row Expression", and can be condition to filter data rows:

  1. When script is empty, all rows are passed without filtering.

  2. The script should be boolean value("true" or "false") finally.
    User can choose to pass the rows by true or by false.

  3. If the script's value is not true nor false, like errors of script, the row is not passed.

2 Edit Row Filter

  1. Can set maximum rows to take.

  2. Can be saved in tree.

3 Exmaples

row filter meaning
#{DataRowNumber} % 2 == 0 data row number is even
#{TableRowNumber} % 2 == 1 odd rows in current page
!isNaN(#{someColumn} - 1) value of "someColumn" is number
Math.abs(#{numberColumn}) > 7 value of "numberColumn" is larger than 7
#{numberColumn1} - #{numberColumn2} < 100 difference between values of "numberColumn1" and "numberColumn2" is less than 100
#{numberColumn1} < #{numberColumn2-Mean} value of "numberColumn1" is less than mean of column "numberColumn2"
#{stringColumn} == '' value of "stringColumn" is empty
#{stringColumn} != null value of "stringColumn" is not null 
if ( #{stringColumn} != null ) 
    #{stringColumn}.length > 9
else
    undefined
 length of value of "stringColumn" is larger than 9 
if ( #{stringColumn} != null ) 
    #{stringColumn}.search(/Hello/ig) >= 0
else
    undefined
 value of "stringColumn" contains "Hello"(case-insensitive) 
if ( #{stringColumn} != null ) 
    #{stringColumn}.startsWith('Hello')
else
    undefined
 value of "stringColumn" starts with "Hello" 
if ( #{stringColumn} != null ) 
    var array = [ 'A', 'B', 'C']; array.includes(#{stringColumn})
else
    undefined
 value of "stringColumn" is one of "A", "B", "C" 
if ( #{dateColumn} != null ) 
    #{dateColumn}.startsWith('2016-05-19 09')
else
    undefined
 month of "dateColumn" is '2016-05-19 09' 
if ( #{dateColumn} != null ) 
    new Date(#{dateColumn}).getTime() > new Date('2016/05/19 09:23:12').getTime()
else
    undefined
 value of "dateColumn" is later than '2016/05/19 09:23:12' 
if ( #{dateColumn} != null ) 
    new Date(#{dateColumn}).getDay() == 0
else
    undefined
 value of "dateColumn" is Sunday