OlapGrid is used when creating new analysis items or data items and defining their data values.
- New Item Creation : Planned Ratio = Actual / Sales Plan
Formula Editor
① Calculated Field ② Formula Editing Function Display Panel ③ Formula Input Panel ④ Function Help Panel
1) Provided Information
- A list of fields, functions, and operators for defining calculation formulas
- Define calculation formulas in the Formula panel
- Help is provided for the selected item (field, function, operator)
2) Formula Editor User Guide
- [ Popup Menu ] - [ Create calculated field ]
- [ Popup Menu ] - [ Custom Items ] - [ Filter Equations ]
- [ Popup Menu ] - [ Custom Items ] - [ Aggregation Formula ]
- [ Popup Menu ] - [ Design ] - [ Field ] - [ Formula ]
- [ Popup Menu ] - [ Design ] - [ Field ] - [ Header Visibility Formula ]
- [ Popup Menu ] - [ Design ] - [ Style ] - [ Conditional Formatting ]
Using Formula Editor via Popup Menu
Formula Editor Function - Aggregate Functions
| Category | Name | Description | Return Type | Usage |
|---|---|---|---|---|
Aggregate | Average | Returns the average of the specified field. | Double | Average([MEASURE]) Average(100, 120, 100, 120) Average( Sum([MEASURE]), 120, 100, 120) |
Sum | Returns the total (sum) of the specified field. | Double | SUM([Sales]) SUM( SUM([Sales]) + MAX([Difference]) ) | |
Count | Returns the count of the specified field. | Double | Count([MEASURE]) Count, 120, 100, 120) Count( Sum([MEASURE]), 120, 100, 120) | |
Max | Returns the maximum value. Supports numeric and string values. For strings, the last value in ascending order is returned. | Object | Max([Sales]) Max([FIELD_NAME]) Max("Hello", "Abc", "Old", "ZIndex") Max( Max([FIELD_NAME]), 120, 100, 120) | |
Min | Returns the minimum value. Supports numeric and string values. For strings, the first value in ascending order is returned. | Object | Min([Sales]) Min([FIELD_NAME]) Min("Hello", "Abc", "Old", "ZIndex") Min( Max([FIELD_NAME]), 120, 100, 120) | |
DistinctCount | Returns the count of unique (distinct) values from the field in the dataset. | Double | DistinctCount([Product Group]) | |
Condition | IF | Performs a conditional check. Returns trueValue if the condition is true, otherwise returns falseValue. | Object | IF( 1 > 2 , "1 is greater than 2" , "1 is less than 2") |
SWITCH | Sequentially evaluates conditions and returns the value of the first true condition. A default value is returned if all are false. | Object | Switch( ToString(getRecordValue([PRODUCT])) == "BW1000" , Sum([H_VAL]) , ToString(getRecordValue([PRODUCT])) == "BW2000" , Sum([Y_VAL]) , Sum([Q_VAL]) ) |
Formula Editor Function - Olap Function
Category | Name | Description | Return Type | Usage |
|---|---|---|---|---|
Olap | Area | Returns the area where the field is placed. (Row:1,Column:2,Filter:3,Data:4) | Integer | Area([COMPANY]) |
| AreaIndex | Returns the index of the field in its area. (Index value starts at 0) | Integer | AreaIndex([COMPANY]) | |
AreaIsRow | Returns whether the field is in the Row area. | Boolean | AreaIsRow([COMPANY]) IF( AreaIsRow([COMPANY]) , SUM([Units]) , 0) | |
AreaIsColumn | Returns whether the field is in the Column area. | Boolean | AreaIsColumn([COMPANY]) | |
AreaIsDate | Returns whether the field is in the Date area. | Boolean | AreaIsDate([COMPANY]) | |
AreaIsFilter | Returns whether the field is in the Filter area. | Boolean | AreaIsFilter([COMPANY]) | |
AreaIsHidden | Returns whether the field is hidden. | Boolean | AreaIsHidden([COMPANY]) | |
getRecordValue | Returns the first value from the cell's record. | Object | getRecordValue([FIELD_NAME]) getRecordValue([COMPANY]) | |
HeaderText | Returns the display text of the header(row/column field). When the field mentioned is not in the header area return blank. | String | HeaderText([FIELD_NAME]) HeaderText([COMPANY]) | |
ForAll | Allows access to upper group totals by excluding dimensions. | Record Array | Sum([Units]) / ForAll("[Company];[Locale]", "[Units]", false) | |
ForEach | Adds dimensions for lower group totals. | Record Array | Average(ForEach("[Locale]", "[Avg. Units]")) | |
InList | Checks if field value is in a given list. | Boolean | InList([FIELF_NAME], "Category1", "Category2") Sum(IF(InList(Locale],”Korea”,”China”),[Units],0)) | |
Match | Matches the text pattern. *(case-sensitive) | Boolean | *a : Boolean that ends with 'a' a* : Boolean that starts with 'a' *a* : Boolean that includes 'a' | |
GetMembers | Retrieves values matching keyword. | Record Array | GetMembers([SEARCH_FIELD_NAME], string keyword, [VALUE_FIELD_NAME]) GetMembers([Locale], “Korea”,[Units], “”) | |
Rank | Ranks items by specific field. Identical values are ranked the same. Measure field names are considered String. | Integer | Rank([DIMENSION], "[MEASURE]", isTop) Rank([Locale], "[Units]", false) | |
Rankln | Automatically ranks in current layout. Identical values are ranked the same. Measure field names are considered String. | Integer | RankIn([MEASURE], "[Row=1, Column=2]", isTop) | |
| IMG | Displays image in cell. Location of the image is defined as the uploaded image's name. | Object | IF(Sum([MEASURE]) > 2000, IMG("FILENAME", 16, 16),"FILENAME") | |
GetRowFields | Returns list of fields based on the current OLAP placement in the row area. | Object | GetRowFields("[dimension1];[dimension2]") | |
GetColumnFields | Returns list of fields based on the current OLAP placement in the column area. | Object | GetColumnFields("[dimension1];[dimension2]") | |
CellValueByOffset | Gets value from cell's current offset position. e.g.) Calculate the year-over-year (YoY) change rate. *Parameter 1. offsetRow(int) : Row offset value 2. offsetColumn(int) : Column offset value | Object | IF(Columnindex()>1, CellValueByOffset(0,-1)-CellValueByOffset(0,-3),”-”) | |
RowIndex | Returns the row index of the current cell. Index starts from 0. | Integer | RowIndex() | |
ColumnIndex | Returns the column index of the current cell. Index starts from 0. | Integer | ColumnIndex() | |
GetVariationValue | Returns the secondary calculation value applied to the given field. e.g. Subtotal calculation | Object | GetVariationValue("[FIELD_NAME]", bool SubTotalSummaryType) | |
| IsHeaderTotal | Returns whether the cell is a subtotal or total value of the field. (True : Total value cell, False : Regular value cell) | Boolean | IsHeaderTotal([Field]) | |
| InDimension | Prevents duplicate application of a specific value from the “1” side in a 1:N relationship between fact tables. | Integer | InDimension("[FIELD_NAME1];[FIELD_NAME2]", [Measure]) | |
Convertor | ToString | Converts the given value to a string. You can apply numeric or date formatting, or leave out the format entirely. | String | ToString(value, [string format]) ToString(123456789, "#,##0") -> 123,456,789 ToString( 0.12 , "P2") -> "12.00 %“ ToString( 123456.789 , "#,###.###") -> "123,456.000" |
ToNumber | Converts the given value to a numeric type. | Double | ToNumber(value) ToNumber("123") -> 123 ToNumber( "123.12" , 0) -> 123.12 ToNumber( "Matrix" , 0) -> 0 | |
ToDate | Converts the given value to a date format. | DateTime | ToDateToString(value, [string format]) ToDate(new Date(), "yyyy-MM-dd") -> 2020-12-31 | |
Logical | AND | Performs logical AND on multiple conditions. | Boolean | AND(boolean, boolean, …) IF ( AND( true, 1==1 , "Korea" == "Korea") , 1 , 0) -> 1 |
OR | Performs logical OR on multiple conditions. | Boolean | OR(boolean, boolean, …) IF ( OR( 1==2 , "Korea" == "Korea") , 1 , 0) =>1 | |
InNull | Checks whether the given value is null | Boolean | IsNull(value) IsNull( 100 ) => false IsNull( "Korea" ) => false | |
IsBool | Returns whether the given value is of Boolean type. | Boolean | IsBool(value) IsBool(1) IsBool(true) | |
IsNumber | Returns true if the given value is a number. | Boolean | IsNumber(value) IsNumber( "100" ) => true IsNumber( 100 ) => true IsNumber( "Korea" ) => false | |
IsString | Returns true if the value is a string. | Boolean | IsString(value) IsString("Korea") => true IsString(null) => false | |
IsDateTime | Returns true if the value is a DateTime type. | Boolean | IsDateTime(value) IsDateTime(DATE(2013, 12,12)) => true IsDateTime( "2013-12-12" ) => false | |
Character | Left | Returns a substring from the left side of the source string with a specified length. | String | Left( "Hello Matrix", 1) -> "H“ DistinctCount( Left( [Num_Field] , 3) ) |
Right | Returns a substring from the right side of the source string with a specified length. | String | Right( "Hello Matrix", 6) -> "Matrix“ Max( Right( [Num_Field] , 1) ) | |
Len | Returns the number of characters in the string. | Integer | Len( "Hello Matrix") -> 12 Sum( Len( [Company] ) ) | |
Lower | Returns the string in lowercase. | String | Lowser( "Hello Matrix") -> "hello matrix" | |
Find | Returns the index of the search string from the source string starting at a given position. Returns -1 if not found. | String | Find("Hello Matrix", "Matrix", 0) -> 6 Find("Hello Matrix", "Kyoung", 0) -> -1 | |
Mid / Substring | Returns a substring from the source string starting at a specified position with a specified length. Mid and Substring functions are identical. | String | Mid("Hello Matrix", 0, 5) ->: "Hello" Mid("Hello Matrix", 6, 6) : "Matrix" | |
Replace / Substitute | Returns a string where all instances of oldText are replaced with newText. Replace and Substitute functions are identical. | String | Replace("Hello Matrix", "Hello", "Hi") -> "Hi Matrix" | |
Trim | Removes all leading and trailing whitespace from the string. | String | Trim( " Hello World ! ") -> "Hello World | |
Upper | Returns the string in uppercase. | String | Upper( "Hello Matrix") -> "HELLO MATRIX" | |
Numeric | ABS | Returns the absolute value of the input. | Double | ABS(Sum([YoY])) |
ACOS | Returns the arc cosine (inverse cosine) of the input. | Double | ACOS(Sum([YoY])) | |
ASIN | Returns the arc sine (inverse sine) of the input. | Double | ASIN(Sum([YoY])) | |
ATAN | Returns the arc tangent (inverse tangent) of the input. | Double | ATAN(Sum([YoY])) | |
CEIL | Returns the smallest integer greater than or equal to the input (ceiling value). | Double | CEIL([Plan Ratio]) | |
COS | Returns the cosine of the input. | Double | ACOS(Sum([YoY])) | |
EXP | Returns e raised to the power of the input. | Double | EXP(Sum([Production Ratio])) | |
FLOOR | Returns the largest integer less than or equal to the input (floor value). | Double | FLOOR(Sum([Plan Ratio])) | |
LOG | Returns the logarithm of the input to the base specified. | Double | LOG([Revenue], 2) | |
LOG10 | Returns the base-10 logarithm of the input. | Double | LOG10([Revenue]) | |
RAND | Returns a random number between 0 and the specified value. If no value is specified, the default upper limit is 1. | Double | RAND() : Number between 0 ~ 1 RAND(10) : Number between 0 ~ 10 | |
ROUND | Returns the input rounded to the specified number of decimal places. | Double | ROUND(105.6,1) à106 | |
SIN | Returns the sine of the input. | Double | SIN(Sum([YoY])) | |
TAN | Returns the tangent of the input. | Double | TAN(Sum([YoY])) | |
Date Time | NOW | Returns the current system date and time. | DateTime | NOW() -> 20201231170409448 |
TODAY | Returns the current system date. Time is set to 00:00:00. | DateTime | TODAY() -> 20201231 | |
YEAR | Returns the year from a given date. | Integer | YEAR(TODAY()) -> 2020 | |
MONTH | Returns the month from a given date. Value : 1 ~ 12 | Integer | MONTH(ToDate("2012-05-05") ) -> 5 | |
DAY | Returns the day of the month from a given date. | Integer | DAY(ToDate("2020-05-30") ) ->30 | |
WEEKDAY | Returns the day of the week as an integer constant. Sunday = 0, Monday = 1, Tuesday = 2, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6 | Integer | WEEKDAY(NOW()) WEEKDAY(ToDate("2020-08-01")) -> 6 | |
HOUR | Returns the hour from the given time. Value : 0 ~ 23 | Integer | HOUR(NOW()) HOUR(ToDate(getRecordValue([DUE_DATE]))) | |
MINUTE | Returns the minute from the given time. Value : 0 ~ 59 | Integer | MINUTE(NOW()) MINUTE(ToDate(getRecordValue([DUE_DATE]))) | |
SECOND | Returns the second from the given time. Value : 0 ~ 59 | Integer | SECOND(ToDate(getRecordValue([CREATE_DATE]))) | |
DATEADD | Returns a new date by adding a specified interval to a date. *Use time unit constants dtYear : Year dtQuarter : Quarter dtMonth : Month dtDay : Day dtHour : Hour dtMinute : Minute dtSecond : Second | DateTime | DATEADD(dtYear,1, NOW()) -> 2021 DATEADD(dtYear, 1, DATE(2020, 04, 01)) -> 2021-04-01 AM 12:00:00 DATEADD(dtMonth, -1, DATE(2020, 04, 01)) -> 2020-03-01 AM 12:00:00 | |
DATEDIFF | Returns the difference between two dates in the specified unit. *Use time unit constants | Integer | DATEDIFF(dtYear, DATE(2000, 04, 01), DATE(2015, 04, 01)) -> 15 DATEDIFF(dtMonth, DATE(2000, 04, 01), DATE(2015, 04, 01)) -> 180 DATEDIFF(dtDay, NOW(), ToDate(“2013-05-05”)) | |
DATEPART | Extracts a specific part of the date. *Use time unit constants | Integer | DATEPART(dtYear, DATE(2000, 04, 01) ) -> 2000 DATEPART(dtMonth, DATE(2000, 04, 01) ) -> 4 | |
DATE | Constructs a new date from individual parameters. 4 ~ 6 (Hour, Minute, Seconds) format is optional. | DateTime | DATE(2020,2,21) -> : 2020-02-21 DATE(2020, 04, 01, 12, 30, 12) -> 2020-04-01 12:30:12 | |
Operators | + | Add | ||
- | Subtract | |||
* | Multiply | |||
/ | Divide | |||
% | Percenage calculation (output as %) | |||
^ | Exponentiation (Power) | |||
() | Parentheses | |||
< | Less than | |||
> | Greater than | |||
<= | Less than or equal to | |||
>= | Greater than or equal to | |||
!= | Not equal | |||
! | Not | |||
== | Equal | |||
AND | And | |||
OR | Or | |||
/**/ | Multi-line comment | |||
// | Single-line comment | |||
Constant | IsRowGrandTotal | Indicates whether the data cell's row header is a grand total. | Boolean | Switch(IsRowGrandTotal, Average( [Y_VAL]) , IsColGrandTotal, Average( [Y_VAL]) , IsRowTotal, Average( [Y_VAL]) , IsColTotal , Average( [Y_VAL]) ,Sum( [Y_VAL]) ) |
IsColGrandTotal | Indicates whether the data cell's column header is a grand total. | Boolean | ||
IsRowTotal | Indicates whether the data cell's row header is a subtotal. | Boolean | ||
IsColTotal | Indicates whether the data cell's column header is a subtotal. | Boolean | ||
IsTotal | Indicates whether the data cell header is a subtotal. | Boolean | ||
IsGrandTotal | Indicates whether the data cell header is a grand total. | Boolean | ||
IsTotalOrGrandTotal | Indicates whether the data cell header is a subtotal or grand total. | Boolean | ||
CELL_VALUE | Returns the value of the data cell. | Object | ||
FILED_KEY | Returns the key value of the data field in the data cell. | String | ||
FILED_LABEL | Returns the display name of the data field in the data cell. | String | ||
| FALSE | Returns the Boolean value false. | Boolean | ||
| TRUE | Returns the Boolean value true. | |||
| NULL | Returns a null object. |
When multiple Measure fields exist, the report will repeat for each Measure field.
In cases where some Measures need to be hidden based on user requirements for specific paths, you can modify the report using Header Visibility Formula.
