Operator and illegal error reporting

  • Last update：  2023-05-15

    • 1. Overview

    1.1 Application scenario

    The function calculation format has explained the calculation composition and basic calculation syntax of the function used in FineBI, but sometimes users will encounter the problem of writing formula errors. You can write the formula correctly according to the prompts.

    1.2 Function introduction

    This article will introduce the syntax of operators and expressions in detail to help users understand FineBI's formula verification scheme and write the required functions correctly and effectively.

    2. Operator syntax

    2.1 +

    Concept"+" operator represent addition when applied to numbers and concatenation when applied to strings.
    Support combination typeExcept date field + date field, any other type combination is supported
    Return result type

    There is a text field in the parameter, and the result is a text field

    Date + value is the date field

    If all are numeric, then it is numeric field

    Illegal prompt: "Does not support the date+date operation", as shown in the following figure:

    47.png

    2.2 -

    Concept-Operators represent subtraction when applied to numbers and negation when applied to expressions.
    Support combination type

    Two numeric type or two date type fields

    Note: numeric field - date field is not supported.

    Return result type

    Date type - date type returns a timestamp.

    Date type - numeric type. The returned result is date type.

    The rest of the returned results are numeric.

    Illegal prompt:

    • "Does not support numeric-date operation", as shown in the following figure:

    48.png

    • Other illegal subtraction: "Text cannot be used as a minus or minus", as shown in the following figure:

    49.png

    2.3 /

    Concept/ Operator represents the division of numeric values
    Support combination type

    Two numeric type parameters

    Return result type

    Value type

    Illegal prompt: "both ends of the operator / must be numeric type", as shown in the following figure:

    50.png

    2.4 *，%，^

    Concept

    The * operator represents the multiplication of numbers. 

    The % operator returns the remainder.

    The ^ operator calculates the specified power of a number

    Support combination type

    Two numeric type parameters

    Return result type

    Value type

    Illegal prompt: "both ends of the operator must be numeric type", as shown in the following figure:

    51.png

    2.5 >,<,<=,>= (compare)

    Concept

    >（greater than）

    <（less than）

    >=（greater than or equal to）

    <=（less than or equal to）

    Support combination type

    Two parameters of the same type

    Return result type

    Boolean type

    Illegal prompt: "the field types at both ends of the operator must be the same", as shown in the following figure:

    52.png

    3.  Expression syntax

    For the function expressions of FineBI, see file: Expression syntax.

    When the user make the following writing mistakes, the error message will appear.

    3.1 Incomplete operator expression

    For example, if the user writes the following expression: ABS(Total residence time)+, the error "Syntax error, missing identifier" will be reported:

    53.png

    3.2 Character error

    For example, you need to add a new column, all of which are assigned as 1. At this time, enter "1. Due to the lack of half of English double quotation marks, the following error "Character error" will appear:

    54.png

    3.3 Syntax error

    Other syntax errors.

    4. Prompt for illegal specific function type

    4.1 Text function

    Function
    		Result typeIllegal prompt

    LEN

    Value

    "LEN: does not meet the parameter requirements of (any type)"

    CHAR

    Text

    "CHAR: does not meet the parameter requirements of (number)"

    CODE

    value

    "CODE: does not meet (text) parameter requirements"

    REPLACE

    Text

    "REPLACE: does not meet the parameter requirements of (any type, any type, any type) or (any type, value, value, any type)"

    STARTWITH

    bool

    "STARTWITH: does not meet the parameter requirements of (any type, any type)"

    ENDWITH

    bool

    "ENDWITH: does not meet the parameter requirements of (any type, any type)"

    EXACT

    bool

    "EXACT: does not meet the parameter requirements of (any type, any type)"

    INDEXOF

    Text

    "INDEXOF: does not meet the parameter requirements of (any type, value)"

    REPEAT

    Text

    "REPEAT: does not meet the parameter requirements of (any type, value)"

    LEFT

    Text

    "LEFT: does not meet the parameter requirements of (any type) or (any type, value)"

    RIGHT

    Text

    "RIGHT: does not meet the parameter requirements of (any type) or (any type, value)"

    TODOUBLE

    Value

    "TODOUBLE: does not meet the parameter requirements of (any type)"

    TOINTEGER

    Value

    "TOINTEGER: does not meet the parameter requirements of (any type)"

    LOWER

    Text

    "LOWER: does not meet the parameter requirements of (text)"

    TRIM

    Text

    "TRIM: does not meet the parameter requirements of (text)"

    UPPER

    Text

    "UPPER: does not meet the parameter requirements of (text)"

    CONCATENATE

    Text

    "CONCATENATE: does not meet the parameter requirements of (any type...)"

    MID

    Text

    "MID: does not meet the parameter requirements of (any type, value, value)"

    REGEXP

    BOOL

    Real-time data: "REGEXP: does not meet the (text, text) parameter requirements"

    Extract data: "REGEXP: does not meet the (text, text) or (text, text, value) parameter requirements"

    FORMAT

    Text

    Real-time data: "FORMAT: does not meet the parameter requirements of (date, text)"

    Extract data: "FORMAT: does not meet the parameter requirements of (any type, text)"

    FIND

    Value

    "FIND: does not meet the parameter requirements of (any type, any type) or (any type, any type, value)"

    NUMTOText"NUMTO: does not meet the parameter requirements of (numerical value) or (numerical value, Boolean)"
    PROPERText"PROPER: does not meet (text) parameter requirements"
    SPLITText"SPLIT: does not meet the parameter requirements of (any type, any type)"
    SUBSTITUTEText"SUBSTITUTE: does not meet the parameter requirements of (any type, any type, any type, [number])"

    4.2 Date function

    FunctionResult type
    		Illegal prompt
    DATEDate"DATE: does not meet the parameter requirements of (value/text, value/text, value/text)"
    DATEDELTADate





    "Does not meet the parameter requirements of (date/text, value)"

    MONTHDELTATime
    YEARDELTADate
    DATEDIFValue"DATEDIF: does not meet the parameter requirements of (date/text, date/text, text)"
    DATESUBDATEValue"DATESUBDATE: does not meet the parameter requirements of (date/text, date/text, text)"
    DATETONUMBERValue"DATETONUMBER: does not meet the (date/text) parameter requirements"
    DAYVALUEValue"DAYVALUE: does not meet the (date/text) parameter requirements"
    DAYSOFMONTHValue


    "Does not meet the (date/text) parameter requirements"

    DAYSOFQUARTERValue
    DAYSOFYEARValue

    Real-time data: "DAYSOFYEAR: does not meet the (date/text) parameter requirements"

    Extract data:  "DAYSOFYEAR: does not meet the (any type...) parameter requirements"

    YEARValue





    "#Function name#: does not meet the parameter requirements of () or (date/text) or (date/text, text)"

    WEEKValue
    DAYValue
    HOURValue
    MINUTEValue
    MONTHValue
    SECONDValue
    TIMETime

    Real-time data: "TIME: does not meet the parameter requirements of (value, value, value)"

    Extract data: "TIME: does not meet the parameter requirements of (value/text, value/text, value/text)"

    TODATE

    		Time

    Real-time data:"TODATE: does not meet the parameter requirements of (any type) or (text, text)"

    Extract data: "TODATE: does not meet the parameter requirements of (any type) or (text, text) or (text, text, text)"

    NOWTime"NOW: does not meet the parameter requirements of ()"
    TODAYTime"TODAY: does not meet the parameter requirements of ()"
    WEEKDATETime"WEEKDATE: does not meet the parameter requirements of (value/text, value/text, value/text, value/text)"
    WEEKDAYValue"WEEKDAY: does not meet the (date/text) parameter requirements"
    DAYS360Value

    "DAYS360: does not meet the parameter requirements of (date/text, date/text, text/value)"

    Note: real-time data does not support this function.

    LUNARText

    "LUNAR: does not meet the parameter requirements of (value/text, value/text, value/text)"

    Note: real-time data does not support this function.

    4.3 Mathematics and trigonometric functions

    Function / formula nameReturn typeIllegal formula prompt
    PIValuePI: does not meet the parameter requirements of (value) or ()"
    INTValue

    Real-time data:"INT: does not meet the parameter requirements of (value)"

    Extract data: "INT: does not meet the parameter requirements of (text/value)"

    CEILING










    Value










    "#Function name# does not meet the parameter requirements of (value)"

    Floor
    SQRT
    LN
    SQRT
    ABS
    ACOS
    ASIN
    ATAN
    LOG10
    SIN
    TAN
    COS
    EXP
    DEGREES
    SIGN
    RADIANS
    RANDValue"RAND: does not meet the parameter requirements of ()"
    MINAny type

    Real-time data:"MIN: does not meet the requirement that all parameters are of the same type"

    Extract data: "MIN: does not meet the parameter requirements of (value...)"

    MAX
    RANDBETWEEN




    Value




    #Function name# does not meet the parameter requirements of (value,value)"

    MOD
    POWER
    ROUND
    ATAN2
    PROMOTION
    LOGValue#Function name# does not meet the parameter requirements of (value,value)"
    TRUNC

    4.4 Logic function

     Function / formula nameReturn type

    Illegal formula prompt

    ANDbool"AND: Parameter type must be boolean or number"
    ORbool"OR: Parameter type must be boolean or number"
    IF

    The first parameter is boolean type or value type (0 or 1). The second and third parameters are of the same type. If there are empty parameters in the two parameters, the non empty parameter type is returned; The two parameter types are different: return text type.

    "IF: the first parameter is not boolean or value"

    "IF: does not meet the parameter requirements of (boolean/number, parameter, parameter)"

    Note: the result type of expression in real-time data must be the same.

    SWITCHThe first non empty parameter type in the return result parameter (SWITCH function, the 2n + 1 parameter is the return parameter n > 0)

    Real-time data:"SWITCH: the result type must be the same"

    Extract data: "SWITCH: has at least 3 parameters"

    ||、&&bool

    "#Symbol#: Only two Boolean or number expressions can be connected"

    4.5 Other functions

    Function / formula nameReturn type
    Illegal formula prompt
    ISNULLbool

    "ISNULL: does not meet the parameter requirements of (any types...)"

    NVL

    Empty value (null) is excluded. If other returned result parameters are of the same type, the parameter type of the first non empty and non empty string in the returned result parameters (NVL function, all parameters are returned parameters)

    		"NVL: does not meet the requirement that all parameters are of the same type"

    4.6 Aggregate function

    Function / formula nameReturn typeIllegal formula prompt
    SUM_AGG




    Value




    "#Function name# does not meet the parameter requirements of (value)"



    AVG_AGG
    VAR_AGG
    MEDIAN_AGG
    STDEV_AGG

    PERCENTILE_AGG

    Note: only supports real-time data.

    		Value

    "PERCENTILE_AGG does not meet the parameter requirements of (value, value)"

    "PERCENTILE_AGG 's second constant value type parameter must be between 0 and 1"

    MAX_AGG

    Same parameter type


    "#Function name# does not meet the parameter requirements of (any type)"

    MIN_AGG

    APPROX_COUNT_AGG

    Note: only supports real-time data.


    Value


    "#Function name# does not meet the parameter requirements of (any type)"

    COUNT_AGG
    COUNTD_AGG

    Illegal error reporting in aggregate function combination scenario:

    Error code

    Meaning

    Examples

    Illegal prompt

    61500002

    		The indicators filtered by details do not conform to the aggregation functionABS(field), the field is filtered in detail"#Function name#: Detailed filtered indicators only support aggregate functions"

    61500003

    		Aggregate and non aggregate parameters cannot be mixedSUM_AGG(field1)+field2"#Function name#: Cannot mix aggregated and non-aggregated parameters"

    61500004

    		Aggregate functions cannot be nestedSUM_AGG(SUM_AGG(field)))"#Function name#: Aggregate functions cannot nest aggregate functions"

    4.7 Quick calculation function

    Function / formula nameReturn typeIllegal formula prompt

    SAME_PERIOD







    Value

    "SAME_PERIOD: does not meet the parameter requirements of (value) or (value, text)"

    "SAME_PERIOD: second constant text type parameter must be "Y", "M", "W" (case insensitive)"

    ACC_SUM

    "ACC_SUM: does not meet the parameter requirements of (value) or (value, value)"

    "ACC_ SUM: second constant value type parameter must be 0,1"

    PREVIOUS_PERIOD"PREVIOUS_PERIOD: does not meet the parameter requirements of (number)"
    PERIOD_ANLS

    "PERIOD_ANLS: does not meet the parameter requirements of (value) or (value, text)"

    "PERIOD_ANLS: second constant text type parameter must be "Y", "M", "W" (case insensitive)"

    PERIOD_TD

    "PERIOD_TD: does not meet the parameter requirements of (value) or (value, text)"

    "PERIOD_TD: does not meet the parameter requirements of (value) or (value, text)"

    RANK_ANLS

    "RANK_ANLS: does not meet the parameter requirements of (value) or (value, value) or (value, text) or (value, value, text)"

    "RANK_ ANLS second constant value type parameter must be 0,1"

    "RANK_ ANLS third constant text type parameter must be "asc", "desc" (case insensitive)"

    TOTAL

    "RANK_ANLS: does not meet the parameter requirements of (value) or (value, value) or (value, text) or (value, value, text)"

    "RANK_ANLS: second constant value type parameter must be 0,1"

    "RANK_ANLS: the third constant text type parameter of total must be "sum", "avg", "min", "max" (case insensitive)"

    General error message:

    Meanings

    Example

    Illegal formula prompt

    Parameter contains non aggregate function or constant, or the first parameter must be an aggregate function"

    		TOTAL(Contract amount)(「Contract amount」is a field)"The parameter in #Function name# is only an aggregate function or constant, and the first parameter must be an aggregate function"

    4.8 Control calculation granularity function

    Function / formula nameReturn typeIllegal formula prompt

    FIXED



    Value



    " #Function name# : Must contain parameter"

    " #Function name# :  the field can not be an aggregate function"

    " #Function name# : The last parameter must be an aggregate function"


    INCLUDE
    EXCLUDE

    附件列表


    主题： Advanced Data Analyis
    Previous
    Next
    • Helpful
    • Not helpful
    • Only read
    中文（简体） 中文（繁體） 日本語
    English

    滑鼠選中內容，快速回饋問題

    滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。

    不再提示

    10s後關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    How to Get Quotes：
    FineReport FineBI
    Demonstration Booking：
    FineReport FineBI
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy