Row Expression

1 Usages of Row Expression

When manufacture/trim/calculate data or generate chart, data values can be made by expression, in which calculation logic is implemented in JavaScript codes and data columns' names can be its parameters.

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. Interface provides button to list all valid placeholders for pasting.

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

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

  8. Click button "Script" to copy codes from tree "JavaScript".

  9. Click button "Select" to copy codes from tree "Row Expression".

  10. Click button "Save" to write current expression into tree "Row Expression".

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

Can set maximum rows to take.

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