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 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.
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.
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]
More Articles About Dashboarding
Best Practices for Agile BI - It seems this is one of the most discussed topics these days, and we certainly heart that from our clients, but there remains a lot of confusion as to what exactly it is and today we have synthesized the research. There are several aspects of agile software design that kept coming up. Some thought agile BI referred to applying agile principles, and there are a bunch of agile development principles applicable to BI environments, things like scrum, extreme Programming, and so forth. Well, others thought that it should mean the ability of BI to help an organization become more adaptable, especially to the needs of the end-user...
Efficient Report Writing Secrets - Let's say you want to remove physical location and replace that with accounting location. You could simply open this report definition, make that change, save it under a different name, and now you have two different reports. If I move on to a vendor list by name, that's a pretty standard report there. The vendor profit and loss report is another one with some groupings and sub totals, again with all of the filters...
How Can You Control The Impact Of These Visualization Tools On The Data Sources? - That's a good question. So there are a set of visualizations tools that don't write to the source system. And ours is unique because it offers that option. Traditionally visualization vendors have not written anything to the data sources. They just pulled from the source systems, and all of the analysis slicing and dicing is done offline from the source systems. We can run in mode, too, to protect the source's performance and intergrity. You can export lists out. You can create metrics, and you can prevent changes to anything in the source system. But with our solution, you also have the ability to write back into the source system. You may want to update sales or add new data to the source system and not depend on ETL tools to interface with the source system database...
Implementation of Agile Dashboards - To address these challenges, Harmonix Records partnered with a technology provider specializing in agile project management solutions. The label implemented a suite of agile dashboards tailored to their needs. Key features of the implementation included: Centralized Data Integration: The dashboards integrated data from various sources, including streaming platforms, social media, sales reports, and marketing analytics...
Making Data Usable For Self-Service Analytics - Those are two technologies that that were kind of born with Hadoop, and that's where you were seeing the largest variety of data. In that variety, you have to find a way of making the data really usable for broad analytics use cases. It depends on the shape of the data, whether it's a nested files or something else. And so you saw technology as Trifacta and Paxata that are really born around leveraging the Hadoop platform to do that data shaping and processing right on there...
Try the Best Manufacturing Dashboard Solution - Are you looking for the best manufacturing dashboard solutions? Since 1996 InetSoft has been making dashboard software that is easy to deploy and easy to use. Build self-service oriented dashboards quickly. View a demo and read customer reviews from some of the 5,000+ happy customers...