Online Editing Formula Reference

Overview

GuanReport is an extended chart type embedded in Guandata BI. This document describes formulas used in online editing mode. In addition to built-in Excel formulas, GuanReport supports two formula groups: native formulas and derived formulas.

Syntax Requirements

Online editing formulas always start with =G_.

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

Entering Formulas

In online editing mode, formulas are configurable. Enter =G_ in a cell to show the formula list. Keyword search is supported.

After a function is selected, the editor shows syntax help. When the cursor is in a parameter, the corresponding parameter description is highlighted. You can also click Formula Help to view details.

Dynamic Properties

Some formulas display configurable dynamic properties in the right panel, such as expansion direction, grouping, copy style, interval columns, or no extra rows. Other formulas have no configurable options and show No configuration.

Dynamic properties are also unavailable for invalid functions, normal text, and built-in Excel formulas.

Native Formulas

Vertical Expansion

Syntax

=G_(viewName,fieldName)

Use vertical expansion to fill values from a field downward. Configure the expansion direction as Vertical in dynamic properties.

Horizontal Expansion

Syntax

=G_(viewName,fieldName)

Use horizontal expansion to fill values from a field to the right. Configure the expansion direction as Horizontal.

Group Data

Syntax

=G_(viewName,fieldName)

Use the grouping option to merge, repeat, or keep grouped values normal.

Group Option	Description
normal	Display each row normally.
merge	Merge cells with the same group value.
repeat	Repeat the group value on each row.

When using group data formulas, sort the grouping field first. If sorting is not available, use derived formulas instead.

Inter-cell Calculation

Inter-cell formulas insert Excel-style calculation logic into cells.

Syntax

=G_CELLCALC({columnIndex}{rowIndex} + operator + {columnIndex}{rowIndex})
=G_CELLCALCEXP({columnIndex}{rowIndex} + operator + {columnIndex}{rowIndex})

• G_CELLCALC applies only to the current formula cell.
• G_CELLCALCEXP applies to all cells in the current column after expansion.

Coordinate parameters

Parameter	Description
Column index	Can be an absolute column such as A or B, or a relative column. c means current column. Positive integers such as {1} move right; negative integers such as {-1} move left.
Row index	r means current row. Positive integers such as {1} move down; negative integers such as {-1} move up.

Example formulas:

=G_CELLCALC(C{r}/E{r})
=G_CELLCALCEXP({c}{r}/{c}{-1})

Subtotal

Syntax

=G_SUBTOTAL(functionCode,viewName,range1|range2...,[label]:[labelPosition])

functionCode follows common Excel aggregate codes. For example, 9 means sum. Use this formula when subtotal rows are needed for static columns.

Copy Cell Style

Use the copy cell style dynamic property to copy the style of the formula cell to expanded cells. This is commonly used with vertical or horizontal expansion.

Alternately Insert Row Data

When a formula needs to insert data on alternating rows, combine interval and no-extra-row options in dynamic properties. This is useful for templates that already contain fixed rows and need data inserted between them.

Derived Formulas

Derived formulas are used to build pivot-table-like reports. They are usually used together.

Derived Table

When native formulas directly expand fields from the original view, they do not deduplicate or aggregate values. To build a pivot table, first create a derived table that sorts and deduplicates combined fields.

Syntax

=G_DT(viewName,field1|field2...,derivedFieldName)

The derived table stores unique combinations from the selected fields. Other derived formulas are based on this derived table.

Vertical Expansion Based on a Derived Table

Syntax

=G_EXPFIELD(viewName,field1|field2...,derivedFieldName,mergeCellCount)

This formula selects fields from a view, sorts and deduplicates them, stores the result in a derived table, and expands one derived field vertically. It supports grouping and copying cell styles.

Parameter	Description
viewName	Source view name.
`field1	field2...`
derivedFieldName	Field from the derived table to expand.
mergeCellCount	Number of cells to merge when needed.

Horizontal Expansion

Metric Names Below, Comparison Items Above

Use two formulas together.

Formula 1

=G_EXPFIELD(viewName,field1|field2...,derivedFieldName,mergeCellCount)

This expands comparison items such as year or month horizontally and can merge cells according to the number of metrics.

Formula 2

=G_EXPMETRIC(viewName,field1|field2...,metricName1|metricName2...)

This expands metric names such as sales amount and profit under each comparison item.

Both formulas must use the same derived table.

Metric Names Above, Comparison Items Below

When metric names are above and comparison items are below, write multiple formulas for multiple metrics and use them together with G_LOOKUPEXP.

Value Lookup and Aggregation

Use lookup formulas to search data in the original view and return values that match specified conditions. The results can then be aggregated.

Without Comparison Items

=G_LOOKUP(functionCode,searchView,targetField1|targetField2...,conditionField:conditionValuePosition)

Use this when the table does not need comparison items and the lookup expands only one result column.

With Comparison Items

=G_LOOKUPEXP(functionCode,viewName,field1|field2...,searchView,targetField1|targetField2...,conditionField:conditionValuePosition)

Use this when result columns need to expand dynamically according to comparison items.

Parameter	Description
functionCode	Aggregation function code, such as 9 for sum.
viewName	Derived table view name.
`field1	field2...`
searchView	Original view to search.
`targetField1	targetField2...`
conditionField:conditionValuePosition	Lookup condition and the cell position containing the condition value.

Subtotal Calculation

Use this formula when a dynamically expanded column requires subtotal calculation.

=G_SUBTOTALEXP(functionCode,viewName,rangeField1|rangeField2...,derivedView,derivedField1|derivedField2...,metricCount,[label]:[labelPosition])

For static columns, use the native subtotal formula instead.

Column Grand Total Calculation

Use this formula when a dynamically expanded column requires column total calculation.

=G_GRANDTOTAL(viewName,field1|field2...,metricCount,range)

The range parameter is the cell range to calculate. If a subtotal formula is used, enter the range from the G_LOOKUPEXP cell to the subtotal formula cell. If no subtotal formula is used, enter the G_LOOKUPEXP cell range.

Row Total Calculation

Use this formula when dynamically expanded columns require row total calculation.

=G_COLTOTAL(viewName,field1|field2...,startPosition,metricIntervalColumns,horizontalOffset)

Parameter	Description
startPosition	Starting cell position for calculation.
metricIntervalColumns	Number of interval columns between metrics.
horizontalOffset	Horizontal offset used when locating expanded cells.

Formula Combination Example

A common report compares cities in selected regions across multiple years and calculates subtotal and total values for metrics such as tax-included price and retail price.

Recommended formula design:

1. Create a GuanReport card and select the source dataset.
2. Prepare data views with region, city, year, and metric fields.
3. Design fixed headers in the template.
4. Use G_DT and G_EXPFIELD for vertical expansion.
5. Use G_EXPFIELD and G_EXPMETRIC for horizontal expansion.
6. Use G_LOOKUPEXP to query and aggregate values.
7. Use G_SUBTOTALEXP for subtotal rows.
8. Use G_GRANDTOTAL and G_COLTOTAL for column or row totals.
9. Preview and save the report after verifying layout and values.

Download template: Custom Formula Combination.xlsx