Supported Excel and TM1 worksheet functions by WebWORQ

April 19, 2018

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.

Subscribe to QueBIT Notifications

Can’t find what you’re looking for? →

    Contact Help Desk

    This form collects your contact information so that we can correspond with you. For more information on how we are committed to protecting and respecting your privacy, please review our Privacy Policy.

    I consent to QueBIT collecting my contact information.