Excel and TM1 worksheet expressions supported by WebWORQ include:
- Cell references, e.g. =B$22, =Sheet5!$K$6.
- Expressions built up of combinations of the following subset of predefined Excel functions and operators:
Operators: +, -, *, &, =, <, >, >=, <=, <>
Logical Functions: TRUE, FALSE, NOT, AND, OR, IF
TM1 Functions: DBR, DBS, DBSS, DBRW, DBRA, DFRST, DIMNM, DIMIX, DIMSIZ, DNEXT, DNLEV, DTYPE, ELCOMP, ELCOMPN, ELLEV, ELPAR, ELPARN, SUBNM, SUBSIZ
String Functions: CONCATENATE, FIND, LEFT, LEN, LOWER, MID, PROPER, RIGHT, SUBSTITUTE, TEXT, TRIM, UPPER
Time Functions: DATEVALUE, DAY, HOUR, MINUTE, MONTH, NOW, SECOND, TIME, TIMEVALUE, TODAY, WEEKDAY, YEAR
Math Functions: ABS, AVERAGE, CEILING, FLOOR, MAX, MIN, MOD, PERCENTILE, ROUND, SUM
Lookup and Reference Functions: COLUMN, HYPERLINK, ROW, VLOOKUP
Error Checking Functions: ISERR, ISERROR, ISNA, NA
Other Excel Functions: VALUE
WebWORQ & BoostWORQ Functions:
ExpandCount, ExpandMaxCount, ooCubeRights, ooDimensionRights, ooElementRights, ooIsOnWeb, ooGetClientNameOnServer, ooQueryData
Example: =DBSS($B9*Calculation!C7,$B$1,$C$4,$C$5,$C$6,$A9,C$8)
Special notes
- WebWORQ does not support the evaluation of cross-worksheet formula references. For example, a formula on a sheet named “Sheet1” that references a cell on a sheet named “Sheet2”:
- =Sheet2!$A$4
- Not all Excel cells referenced by a function need to be in the Range to Publish. For details on the parameters and behaviors of these functions please refer to Excel and TM1 documentation.
- Although the Excel VLOOKUP function is supported, we recommend that it be used sparingly. As a matter of “best practice” we strongly advocate the use of TM1 dimension attributes combined with the DBRA function for lookup purposes in spreadsheets. In the long run, use of attributes leads to easier maintenance and a more robust system.