Successfully!

Error!

Charts Refering Cell Data Across Sheets

  • Last update:  2021-12-29
  • I. Overview

    1. Problem description

    When creating ordinary reports, multiple sheet tabs are often added, and different data can be viewed by turning the pages when displaying on the front end.

    For example: There is a data table on the sheet1 tab, and a new sheet2 tab is added, and a column chart is created based on the calculation results of the table in the sheet1 tab. So how do you refer to cell data across sheets?


    2. Solutions

    When referencing data in other sheet pages, the formula adopts the format of'sheet name'! cell, for example:'sheet1'! A1, which refers to the data in cell A1 in the sheet1 tab page.

    II. Example

    1. Prepare data

    Open the built-in template: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\GettingStartedEN.cpt, as the first tab page, named Sales. As shown below:

    1.png


    2. New sheet page

    Delete the column chart of the first tab page, and add a new tab page at the same time, named Column Chart. As shown below:

    2.png


    3. Insert chart

    In the column chart tab page, merge a range of cells, select the merged cell A1, and insert a column chart, as shown in the figure below:

    3.png


    4. Binding data

    Select the column chart, click Cell Element> Data in the property panel, select Cell Data as the data source, and set the Category Name, Series and Value respectively, as shown below:

    • Category name: Insert the formula "Sales"! B4, which means to refer to cell B4 on the sales tab page.

    • Series : Insert the formula "Sales"! C3, which means refer to cell C3 on the sales tab page.

    • Value: Insert the formula "Sales"! C4, which means refer to cell C4 on the Sales tab page.

    4.png


    5. Effect preview

    1) PC 

    Save the report, click Data Analysis Preview, the effect is shown in the figure below:

    1.gif


    2) Mobile

    2.gif

    III. Template download

    See the completed template: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Primary\Chart\Charts Refering Cell Data Across Sheets.cpt

    Click to download the template: 

    Charts Refering Cell Data Across Sheets.cpt 

    Attachment List


    Theme: Chart
    • Helpful
    • Not helpful
    • Only read

    Doc Feedback