Cross Sheet Linkage

1. Application scenarios

Data can be transmitted between reports through parameters or hyperlinks, but values cannot be transmitted between sheets in the same template through parameters and hyperlinks.

So how do you get the value of a cell in one sheet from another sheet? How do you access data across sheets? 

As you can see in the figure below, the Order Cost in sheet1 is calculated by participating cells in sheet2, and when the sheet2 data changes, the Order Cost also change.

2. Solution

You can use the formula =sheetname!cellname to implement access data cross-sheet. For example, getting A2 data of sheet2 in sheet1, you can add the formula: =sheet2!A2

If sheetname contains the short term, underscores, and other special characters will need to add single quotes to sheetname, formula should be written as: ='sheetname'!cellname

1. Data preparation

1) Create two new datasets, ds1 for sheet1 and ds2 for sheet2, SQL statement as follows:

ds1: SELECT * FROM ORDERS

ds2: SELECT * FROM ORDERSDETAIL

2) Add a template parameter and set the default value to 10001, as shown below:

3) Bind the widget to this parameter in the parameter pane, select the Drop-down Box widget, and set the Data Dictionary to the ORDERID field in the ORDERS table.

2. Prepare sheet2

1) Create a sheet2 and design the sheet2 as shown in the following figure. Drag and drop the fields in ds2 to the corresponding cells. Double-click A2 to set the Filter which binding with template parameter.

Add formula SUM(C2*D2) to E2, add Drop-down Box widget to A2, add Text Field widget to cell B2~D2, set the Left Parent Cell of cell E2 to A2, as shown below:

2) Set Data Entry Attributes to sheet2, as shown below:

3. Realize cross sheet linkage

1) Design sheet1 as shown below, drag and drop the fields in ds1 to the corresponding cells, double-click A2 to set the Filter which binding with template parameter.

Add formula to C2: sheet2!E2+B2, namely add E2 in sheet2 with B2 in the sheet1, then set the Left Parent Cell of cell C2 to A2.

2) Set Data Entry Attributes to sheet1, as shown below:

4. Preview effect

Save the template and click Data Entry Preview, then modify the related cells in sheet2. The Order Cost in sheet1 will also change, as shown in Section I.1.

Note: Mobile terminal is not supported.

Please refer to the completed template: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Form\Cross_Sheet_Linkage.cpt

Click to download the template: Cross_Sheet_Linkage.cpt