Skip to main content

Formula Mapping Between Editing Modes

Formula Syntax Requirements

Online Editing Mode Formulas Always Start with =G_

  • Formula parameters start with an English left parenthesis ( and end with ).
  • Multiple parameters are separated by English commas ,.
  • Do not add spaces between parameters.
  • Field names must not contain English parentheses ( or ).

Local Editing Mode Formulas Always Start with &

Local editing mode formulas are written in Excel templates and start with the English ampersand symbol &.

Formula Mapping Between Modes

Local Editing Mode FormulaOnline Editing Mode FormulaConfigurable Options in Online Editing Mode
&=view.field=G_(view,field)Expansion direction, grouping, copy cell style, skip columns, and no extra rows.
&==(DynamicFormula)=G_CELLCALC(formula)No configuration.
&=&=(RepeatDynamicFormula)=G_CELLCALCEXP(formula)Expansion direction, copy cell style, skip columns, and no extra rows. In vertical expansion mode, also supports custom interval expansion view, interval fields separated by `
`&=[DTsourceView!sourceField].sourceField``=G_DT(view,field1
&&=expand_field(<derivedTable.derivedField>,<derivedTable.derivedFieldPosition>)`=G_EXPFIELD(view,field1field2...,derivedField,mergeCellCount)`
&&=expand_metric(<derivedTable>,<fixedTextNames>)`=G_EXPMETRIC(view,field1field2...,textName1
&&=guanlookupN(<derivedTable>,<searchView>,<targetMetric>,<conditionField>,<conditionValuePosition>,<derivedField>,<derivedFieldPosition>)`=G_LOOKUPEXP(functionCode,view,field1field2...,searchView,targetField1
&&=guanlookupN([],<searchView>,<targetMetric>,<conditionField>)`=G_LOOKUP(functionCode,searchView,targetField1targetField2...,conditionField:conditionValuePosition)`
&=subtotalN:view.field`=G_SUBTOTAL(functionCode,view,range1range2...,[label]:[labelPosition])`
&&=subtotalN(derivedTable.derivedField(Label:<label>,LabelPosition:<labelPosition>),<derivedTable>,N,copystyle)`=G_SUBTOTALEXP(functionCode,view,rangeField1rangeField2...,derivedView,derivedField1
&&=grandtotal9(<derivedTable>,<range>)`=G_GRANDTOTAL(view,field1field2...,metricCount,range)`
&&=columnTotal9(<derivedTable>,<derivedFieldPosition>,<metricIntervalColumns>,<horizontalOffset>)`=G_COLTOTAL(view,field1field2...,startPosition,metricIntervalColumns,horizontalOffset)`

Migration Notes

  • Online editing mode is recommended when users need field drag-and-drop, formula prompts, and visual configuration panels.
  • Local editing mode is recommended when users need richer native Excel formatting or already have mature Excel templates.
  • When a local template is imported into online editing mode, GuanReport tries to convert supported local formulas into online formulas automatically.
  • After conversion, always preview the report and verify expansion direction, grouping, subtotal, grand total, and inter-cell calculation results.
  • If a formula cannot be converted automatically, rewrite it according to the mapping table above.