Chinese-style Reports (Original Complex Reports) FAQ

1. Why does G_LOOKUP function match 0 data when there is actual data?

Reason 1: When matching data in View 2 according to fields in View 1, it may be because the field types of matching fields in the two views are inconsistent.

Solution: Keep the same field types consistent in different views. Converting types requires creating new calculated fields using functions to truly take effect.

? Case 1 (click to expand)

Formula: =G_LOOKUP(9,details,available_quantity,material_code

,warehouse

)

Solution:

Convert the material code in View 1 to a text type field using functions, and the data can be matched normally.

Reason 2: The query condition in the G_LOOKUP formula uses double/float format numbers.

Solution: It's not recommended to use double/float format numbers as dimensions and query conditions, because there are precision issues and they may not match correctly. If needed for scenarios, it's recommended to use functions to limit decimal places and convert to text format.

Reason 3: The query condition in the LOOKUP formula uses aggregated calculation results from numeric columns. Query conditions in G_LOOKUP/G_LOOKUPEXP functions currently only support using fields from dimension columns.

Solution: Put numeric fields used as query conditions into dimension columns. If calculations are needed for numeric values, it's recommended to calculate them in ETL or use window functions in cards.

?Case 2 (click to expand)

Using advanced calculations-ranking function to calculate the TOP 10 sales quantity for each region and each product category, then use "Ranking" as one of the query conditions in the template.

Formula: =G_LOOKUPEXP(9,view1,region,view1,product_name,product_category

,ranking

,region:C1)

Solution: Calculate the ranking using window functions in the data preparation stage, and put "Ranking" into the dimension column.

2. Why does the calculation result show 0 when using sum() to find column totals with data present?

Reason: The field referenced by the current column is not a numeric type (such as text type), so it cannot be summed.

Solution: Use functions in the view to convert the field to numeric type and put it in the numeric column.

3. Why does a column's data format change back to automatic format after saving, and the displayed numbers appear in scientific notation?

Reason: The field being summed in the G_LOOKUP formula is in text format. Summing text and setting format results in inaccurate calculations, and the format also won't take effect; when numeric length is too long, Excel components will automatically display in scientific notation.

Solution: Use functions in the view to convert field format!!! For example cast([field] as double)

4. Can 0 or null values in complex reports be replaced with other symbols?

Answer: Version 4.8 and above: In the report preview interface, click to expand "Special Values" on the right, you can configure multiple replacement rules. Pay attention to the replacement rules (you can click the icon after adding to view). Version 4.8 and below: Only supports null value replacement.

? Example (click to expand)

5. How to set thousand separators for data?

Local editing mode: Set the format in Excel and upload the template.

Online editing mode:\

1. Select the cells/formulas that need to be set;\
2. In the header, select "Custom Format"-"More Number Formats", select the desired number format, click OK.

?Step example

Note:1) In online editing mode, numeric values with thousand separator format currently don't support displaying as currency format "¥..." simultaneously;
2) The thousand separator function is available in version 4.2.0 and above.

6. Why does complex report data display #DIV/0?

Reason: When the denominator is 0, an error occurs and returns #DIV/0!\ Solution: You can use Excel's built-in if(iserror()) function to replace. For example: =G_CELLCALCEXP(if(iserror((B{r}-C{r})/B{r}),"--",(B{r}-C{r})/B{r}))

7. Why doesn't increasing or decreasing decimal places from the toolbar take effect?

Reason: The default cell format is "Automatic", which doesn't support adjusting decimal places at this time.

Solution: Manually change to a format with decimal points (such as number, percentage, currency) before adjusting decimal places.

8. What does derived table mean?

Answer: Derived table (i.e., Dynamic Table), which selects fields for vertical expansion from the original table to derive a new table and automatically sorts and deduplicates. Default ascending order, and sorting method cannot be adjusted. Equivalent to putting fields in the dimension column in cards.

Comparison of report previews generated by native formulas and derived formulas:

9. Why don't subtotal formulas take effect?

Possible reason 1: The cells where the range fields referenced by the subtotal are located don't have "Grouping" method set in dynamic properties.

Solution: Set the grouping method for cells where subtotal range fields are located. There are 3 grouping methods, commonly used is "Merge Cells".

【Actual scenario case】

Here the subtotal range field is "Material Code", that is, sum by "Material Code" grouping, so the "Material Code" dynamic property of cell A3 must check "Grouping".

10. Why are there more columns than expected after horizontal expansion?

Reason 1: When multiple row headers expand simultaneously, or multiple column data expand alternately, "Don't add extra rows and columns" in dynamic properties is not checked, causing multiple cell formulas to expand repeatedly.

Solution: Except for the last horizontal expansion formula, other formulas' dynamic properties need to check "Don't add extra rows and columns". For details, please refer to Explanation and Usage Scenarios of Don't Add Extra Rows and Columns.

Reason 2: Improper use of derived tables in derived formulas, mainly in 2 aspects: 1) Formulas in different cells of the same column don't use the same derived table, causing inconsistent column numbers expanded by each formula; 2) Incorrectly using double numeric values, timestamp date-time and other fields as derived table fields, causing too many expanded columns.

Solution:1) All formulas in the same column use the same derived table; 2) Use text string, integer int type fields as derived table fields in derived formulas.

Reason 3: Using derived formulas where horizontal expansion is not needed.

Solution: Use formulas without derived tables, or manually create a derived table with only one value and use it.

11. How to prevent row totals (G_COLTOTAL) and column totals (G_GRANDTOTAL) from expanding horizontally?

Solution 1: Row totals (G_COLTOTAL) and column totals (G_GRANDTOTAL) formulas must reference derived tables. When horizontal expansion is not needed, you can create a fixed field in any view with unlimited content, then drag it to the dimension column; then use this view and field as the derived table in the total formula. Refer to the figure below. This method applies to all derived formulas (such as G_LOOKUPEXP).

Solution 2: Don't use total formulas, use sum() and other formulas instead. Please refer to Different Calculation Methods for Complex Report Column Totals.

12. Why can't freeze panes freeze to expanded rows and columns in complex reports?

Reason: Freeze panes and freeze rows/columns are physical freezes, the freeze range is fixed and cannot dynamically change with row/column expansion.

Solution: It's recommended to only freeze the header part.

13. After page filtering, some cells in complex reports display error value #NAME, normal without filtering, preview page also normal, what's the reason?

Case:

Reason: Left dimension vertical dynamic expansion, as filter conditions change, the number of rows is not fixed, but in this case the cell calculation formula =G_CELLCALCEXP({c}{1}+{c}{2}+{c}{3}+{c}{4}+{c}{5}) requires exactly 5 rows of vertical expansion to calculate accurately. Once the number of rows is inconsistent, calculation results will be inaccurate or unable to calculate and display.

Solution: When dimensions are fixed, it's recommended to use fixed headers; when dimensions are not fixed, it's recommended to modify the summation method or modify the display style. For more summation calculation methods, please refer to Different Calculation Methods for Complex Report Column Totals.

14. Why are row total (G_COLTOTAL) calculation results incorrect with multiple horizontal expansions?

Reason: Row totals (G_COLTOTAL) and column totals (G_GRANDTOTAL) formulas need to specify calculation ranges. During the first dynamic expansion, the initial position of the calculation range is correct, and the calculation range after dynamic expansion is also accurate; but starting from the second expansion, the range to be calculated has already changed dynamically, but the system cannot recognize the changed range, still calculating according to the range in the formula, causing calculation errors.

Solution:

1) Merge multiple similar expansions into one expansion, and put all row totals at the end. For details, please refer to Complex Report Row Total Implementation Method.

Case 1: (click to expand)

2) If row totals need to follow after each expansion separately, or if multiple expansions have inconsistent column numbers, then starting from the second expansion, you cannot use row total (G_COLTOTAL) and column total (G_GRANDTOTAL) formulas. It's recommended to use G_LOOKUP/G_LOOKUPEXP/SUM() to calculate row totals and column totals.

Case 2: (click to expand)

Solution:

15. How do complex reports jump to other dashboard pages with parameters?

Answer: Although complex report cards themselves don't support 【Jump】 settings, you can achieve jumping to target pages carrying current dimension information by setting 【Display as Hyperlink】 for dimension fields.

Solution:

1) Ensure the target page has filters that can receive parameters; copy the target page link and filter id;

2) In the data preparation interface of complex reports, set 【Display as Hyperlink】 for dimension fields to be set for jumping, and concatenate the target dashboard page URL with filter conditions; drag this field into the complex report. For parameter concatenation methods, please refer to Add Condition Parameters to Page URL.

3）If you need to carry multiple dimension information to jump, such as carrying both [Region] and [Product Category] dimension information to jump to the target page, it's recommended to create a new calculated field in the view, concatenate the 2 filter ids and options together (you can also concatenate the entire URL directly). Refer to the figure below.

16. Why can't templates with many formulas be saved after editing again?

Possible reason: Frontend has a 10MB limit on template size, exceeding which cannot be saved.

Solution: Template limit changed to 30M in version 5.9, you can upgrade to 5.9; optimize template design, try not to write too many formulas, occupying too many cells.