In some cases you may need to calculate the data you want to display from existing query columns. To do this, modify the formula by placing '=' in front of the expression string.
For example, consider the formula table described previously (Referencing a Query Column). To merge the 'state' and the 'zip' fields into a single cell, separated by a comma (e.g., NJ, 08901), use the following formula:
q['=state + ", " + zip'];
Referencing a Query Column With Field Filtering
You can filter out records of a field (column) based on the values of other fields in the result set. To do this, use '@' as the delimiter between the column name and the filtering expression and ':' to introduce the values to filter.
For example, consider the formula table described previously (Referencing a Query Column). To extract all the companies within a certain state (NJ), you can adapt the formula as follows:
q['company_name@state:NJ'];
To filter based on multiple fields, use ';' as the delimiter between the filtering expressions. For example, to find all the companies within a certain city (New Brunswick) and state (NJ), adapt the formula as follows:
q['company_name@state:NJ;city:New Brunswick'];
If the filtering expression is based on a derived field, place '=' in front of the expression. For example, to find all the companies within a certain 'state, zip' pair (see Deriving a Result Set from Query Columns), adapt the formula as follows:
q['company_name@=state + ", " + zip:NJ, 08854'];
 |
View a 2-minute demonstration of InetSoft's easy, agile, and robust BI software. |
Referencing a Column With Expression Filtering
You can filter out the records of a column based on a conditional expression. Use ''?” as the delimiter between the filtering expression and the column name.
Note: Although Expression Filtering can achieve the same result as Field Filtering, use Field Filtering wherever possible, since it is better optimized.
For example, consider the table described previously (Referencing a Query Column). To extract all the customers whose customerIDs are between 20 and 30, adapt the formula as follows:
q['company_name?customer_id > 20 && customer_id < 30'];
Referencing a Column with Positional (Index) Filtering
You can filter out records of a column based on a range of row indices. For example, consider the table described previously (Referencing a Query Column). To extract the first five records from the 'company_name' column, adapt the formula as follows:
q['[0,company_name]:[4, company_name]'];
An asterisk '*' in place of the row index represents the last row in the result set.