InetSoft Product Information: Formula Table Functions

This functions listed below are commonly used to populate cells in a formula table.

inArray

The inArray() function determines whether a value is part of an array. inArray(array, value); e.g.,

 inArray([1,2,3], $num);

This function is useful as a condition check together with the iif operator.

view demo icon
View a 2-minute demonstration of InetSoft's easy, agile, and robust BI software.
 

toArray

The toArray() function converts a tableLens object or a delimited string to a JavaScript Array.

 toArray(tableLens);
 toArray(delimString);
 e.g., var a = toArray(Table1.tableLens);
       var a = toArray('1,2,3');

You can use the returned JavaScript Array to access table data in the usual fashion: e.g., a[1][2] is the data in 2nd row, 3rd column

The rowList() function generates a list of values from a result set with column grouping, conditional filtering, and other sorting options. 

rowList(tableLens, 'conditional spec', 'options string'); 
e.g., rowList(q, 'quantity ? discount > 0','sort=asc');

Here, conditionalspec is the column name with grouping or conditional specifications, and the options string values are as follows (multiple options are separated by a comma delimiter):

• sort=asc/desc/false: Sort the values in ascending or descending order. Default is false.

• distinct=true/false: Retrieve only distinct values. Default is false.

• maxrows=num: Limit the number of elements returned.

• sortcolumn=column_name: Sort data according to a specific query column which is not returned in the list.

The variable 'q' contains the results of a query. It is usually generated in onLoad Handler script by the runQuery function, e.g., 

var q = runQuery('All Sales').

The rowList() function also adds the fields of the result set to the formula scope of the table cells, so that they can be accessed as field['colName']. Therefore, you can use rowList() to extract a portion of a query result set, and then fill out the table with other fields of the result set which correspond to the records of the extracted row.

top ranked BI
Read how InetSoft was rated as a top BI vendor in G2 Crowd's user survey-based index.
 

mapList

The mapList() function groups a list of values according to a specified mapping. The mapping array consists of hybrid alternating name-value pairs or [name array]-value pairs. 

mapList(list, mapping_array, 'options string') 
e.g., mapList(q['State'],[['AZ','CA'],'West',['NY','NJ'],'East'])

The options string values are as follows (multiple options are separated by a comma delimiter):

  • others=groupOthers/leaveOthers: Group all unmapped values in a generic group called 'Others' (groupOthers), or display each unmapped value as its own group (leaveOthers). Default is 'groupOthers'.
  • sort=asc/desc/false: Sort the values in ascending or descending order. Default is false.
  • distinct=true/false: Retrieve only distinct values. Default is false.
Read the top 10 reasons for selecting InetSoft as your BI partner.
 

toList

The toList() function is the most commonly used formula table function. It generates a unique, sorted, and grouped list from the values in a JavaScript Array or tableLens.

In most cases, you will use toList() to obtain distinct values from a query array when you create header rows/columns. For example, “toList(q['state'])” obtains a distinct list of states in ascending order from query variable “q”.

The full syntax of the function is as follows, 

toList(list, 'options string');

where options string values are as follows (multiple options are separated by a comma delimiter):

  • sort=asc/desc/false: Sort the values in ascending or descending order. Default is asc.
  • distinct=true/false: Retrieve only distinct values. Default is true.
  • date=year/quarter/month/week/day/hour/minute/second/weekday/monthname/weekdayname: Group date values according to specified period, and return the period designation.
  • rounddate=year/quarter/month/week/day/hour/minute/second/weekday/monthname/weekdayname: Group date values according to specified period, and return the rounded date value.

The rounddate option is useful when you want to group by month and year, (e.g., Jan 2005 and Jan 2006, etc.). For example, if the 'Order Date' field in a query has the following values, 

[Jan-2-2002, Feb-21-2004, Feb-25-2004, Nov-25-2005]

then the toList function with a rounddate grouping of month, 

toList(q['Order Date'], 'rounddate=month');

returns a unique list of dates containing the first day of the month of the given year: 

[Jan-1-2002, Feb-1-2004, Nov-1-2005]
Previous: Absolute Parent Group Reference