I. Overview
1) A multi-sheet report is a combination of multiple independent reports in a form similar to Excel sheets, so as to enable such functions as editing multiple reports at the same time, storing data to the database, and batch printing or exporting.
2) When we need to enter data in a number of reports and it is inappropriate to place these reports on a page, we can use a multi-sheet report for data entry.
3) In multi-sheet data entry, in contrast to single-sheet data entry, data entry attributes should be set for each sheet separately; in other words, the design of each sheet page should be bound to a built-in SQL submission. Moreover, we can choose to submit the current sheet only or submit all sheets.
4) Learn more about it by reading Data Entry Attributes, Web Attributes, [Tutorial] Row-based Form and Insert Report Widgets.
5) Effects that can be realized:
Simple multi-sheet data entry
Rename and adjust the position of a sheet
Submit the current sheet only
Clear editing traces in the current sheet
Cross-sheet calculation
| You will learn |
|---|
|
II. Simple multi-sheet data entry
1. Prepare data
1) Create a new DB Query [ds1] and select [FRDemo] as the database. The query statement is as follows:
SELECT * FROM ORDERSJOIN CUSTOMERON ORDERS.CUSTOMERID=CUSTOMER.CUSTOMERIDWHERE ORDERID='${ID}'
Here, a dataset parameter ${ID} is set.
2) Create a new DB Query [ds2] and select [FRDemo] as the dataset. The query statement is as follows:
SELECT ORDERSDETAIL.*, PRODUCTNAME FROM ORDERSDETAILJOIN ProductsON ORDERSDETAIL.PRODUCTID=Products.PRODUCTIDwhere ORDERID='${ID}'
Here, a dataset parameter ${ID} is also set.
2. Design the page
1) Parameter pane
Click on the parameter pane edit icon
and the parameter ID in the right-hand pane. A parameter query widget will be automatically generated for ID in the parameter pane.
Select Drop-down Box as the widget type.
Select the drop-down box widget, click [Attributes] in the right-hand pane, and then [Advanced]>[Data Dictionary] to set the range of parameter values. The type is by default Database Table. Select [FRDemo] as [Database] and [ORDERS] as [Database Table]. The field ORDERID will be automatically selected as the actual value and the display value below. Click OK.
Select all of the widgets in the parameter pane, move them to the right and adjust the typesetting. After the adjustment, quit the parameter pane editing interface.
2) sheet1-order
Title: merge the cell range A1: F1 and input ='Order '+$ID+' Info'.
Field name: input the following in cells A2, C2, E2, A4, C4 and E4 respectively: Customer ID, Customer Name, Customer City, Order Amount, Employee Name and Ship Method.
Field value: drag the following fields in the dataset ds1 into cells B2, D2, F2, B4, D4 and F4: CUSTOMERID, CUSTOMERNAME, CITY, AMOUNT, EMPID and SHIPMETHOD.
For the cell D4, make employee ID directly display the name of an employee: click [Cell Attributes]>[Display] in the right-hand pane, select [Data Dictionary], choose [Database Table] as the type and [FRDemo] as the database, input EMPLOYEE in the database table textbox and select the corresponding table. Select [EMPID] as the actual value and [EMPNAME] as the display value.
3) sheet2-order details
Click on the New Sheet icon at the bottom to create a new sheet, which is automatically named sheet2.
Title: merge cells A1~E1 and input ='Order ' + $ID + ' Detail'.
Field name: input the following in cells A2~E2 respectively: Product ID, Product Name, Quantity, Unit Price and Total Price. Merge cells A4~D4 and input Subtotal.
Field value: drag the following fields in the dataset ds2 into cells A3~D3: PRODUCTID, PRODUCTNAME, QUANTITY and PRICE. Input =C3*D3 in the cell E3 and =sum(E3) in the cell E4.
3. Make the page more visually appealing
1) sheet1-order
Column width: select all of columns A~F and set the column width to 28mm.
Row height: select all of rows 1~4 and set the row height to 10mm.
Title: set font to Arial, font size to 12 and center alignment.
Field name: for cells A2, C2, E2, A4, C4 and E4, set font to Arial, font size to 10 and left alignment. Set background color to #99ccff, font color to white and use bold fonts.
Field value: for cells B2, D2, F2, B4, D4 and F4, set font to Arial, font size to 9 and center alignment. Set bottom borders for them and set the color to #99ccff.
The effect is shown below:
2) sheet2-order details
Column width: select all of columns A~E and set the column width to 28mm.
Row height: select rows 1, 2 and 4 and set the row height to 10mm. Set the row height of row 3 to 8mm.
Title: set font to Arial, font size to 12 and center alignment.
Field name: for cells A2~E2 and A4, set font to Arial, font size to 10 and center alignment. Set background color to #99ccff, font color to white and use bold fonts.
Field value: for cells A3~E3 and E4, set font to Arial and font size to 9. For cells A3 and B3, set left alignment; for cells C3, D3, E3 and E4, set right alignment.
The effect is shown below:
4. Set data entry attributes
1) sheet1-order
Add a widget: select the cell for data entry, e.g., F4 SHIPMETHOD. Click [Select Widget] in the right-hand pane and select [Drop-down Box]. Click Data Dictionary and set the range of values: select [Database Table] as the type, [FRDemo] as the database, ORDERS as the database table, and SHIPMETHOD as the actual value and the display value.
Create new data entry attributes: click [Template]>[Data Entry Attributes] and select [Submit] to create a new built-in SQL.
Edit data entry fields: select [Smart Submit] as the submit type, [FRDemo] as the database and ORDERS the database table. Click [Smart Add Fields] and select ORDERID and SHIPMETHOD. Check the key in front of ORDERID to make it the key of data entry.
Add cells: double-click the value corresponding to ORDERID to bring out a dialogue box, select [Parameters] as the type and choose the parameter ${ID} corresponding to ORDERID. Double-click the value corresponding to SHIPMETHOD to bring out a dialogue box, select [Cell] as the type and choose the corresponding cell F4.
2) sheet2-order details
Select sheet2
Add a widget: select the cell for data entry, e.g., C3 Quantity. Click [Select Widget] in the right-hand pane and select [Digital Widget]. Uncheck [Allow Negative].
Create new data entry attributes: click [Template]>[Data Entry Attributes], select [Submit] to create a new built-in SQL.
Edit data entry fields: select [Smart Submit] as the type, [FRDemo] as the database and ORDERSDETAIL as the database table. Click [Smart Add Fields] and select ORDERID, PRODUCTID and QUANTITY. Check the key in front of ORDERID and PRODUCTID to make the two primary keys of data entry.
Add cells: double-click the value corresponding to ORDERID, select [Parameters] as the type and choose the parameter ${ID} corresponding to ORDERID. Double-click the value corresponding to PRODUCTID, select [Cell] as the type and choose the corresponding cell A3. Double-click the value corresponding to QUANTITY, select [Cell] as the type and choose the corresponding cell C3.
when using multi-sheet data entry, you should set data entry attributes for each sheet separately. In other words, you should execute the above-mentioned steps in the design of each sheet page and bind the design to a built-in SQL SUBMIT statement.
5. Set Web attributes
Click [Template]>[Web Attributes], select [Data Entry Settings], choose [Individually set for the template] and [Center] as the report display location.
6. Preview the effect in Data Entry Preview mode
1) Save the template as Multi-sheet.cpt. Click Data Entry Preview to view the effect.
2) Select an ID and click Query. You can see that both sheet1 and sheet2 display the order information with respect to this ID.
3) In sheet1, click the delivery company after Ship Method to choose from different delivery companies. In sheet2, click the quantity of a product to modify it. Click Submit and a message indicating success will appear.
4) Refresh the page and re-query the order. You can see that relevant information has been modified.
Template 1
III. Rename and adjust the position of a sheet
1. Rename a sheet
1) Take the template Multi-sheet.cpt in Ⅱ. Simple multi-sheet for example. Right-click the sheet label and click [Rename] to rename the label.
2) Sheet name also supports dynamic parameter transfer, i.e., using a formula containing parameters for similar cells. For example, we can modify the name of sheet1 to =$ID+" Info"and that of sheet2 to =$ID+" Detail".
3) Preview the effect in Data Entry Preview mode:
if a parameter is transferred via URL, then the parameter cannot be directly used in the sheet name formula. You have to first put the parameter in a certain cell, for example, make the cell A5 in sheet1=$ID and then modify the name of sheet1 to =A5+" Info".
2. Move sheet labels to the top
1) Generally, sheet labels are by default located on the bottom toolbar in the preview mode.
2) If you wish to display sheet labels on the top of the report content, you can execute the following settings: click [Template]>[Web Attributes] and then [Data Entry Settings], select [Individually set for the template], check [Top] as [sheet label page display position] and click OK.
3) Preview the effect in Data Entry Preview mode
Template 2
IV. Submit the current sheet separately and clear editing traces
1. Submit the current sheet only
1) Generally, in multi-sheet data entry, all sheets will be submitted by default. If you wish to submit the current sheet only, you may execute the following settings: click the menu bar, [Template]>[Web Attributes] and then [Data Entry Settings], select [Individually set for the template], check [Use Toolbar], click on the button next to Top Toolbar to enter the toolbar editing interface.
2) Click on the Submit button, check [Submit Current Sheet Only] and click [OK] to save the settings.
3) Preview the effect in Data Entry Preview mode: query Order 10002, modify the delivery company in sheet1 from US Mail to DHL and the quantity of the first product in sheet2 from 5 to 10, return to sheet1 and click Submit. Refresh the report, re-query Order 10002 and you can see that the delivery company in sheet1 has been modified to DHL while the quantity in sheet2 remains unchanged.
2. Clear editing traces in the current sheet
1) If you wish to clear editing traces in the current sheet without affecting other sheets, e.g., to re-edit the current sheet after multiple editing errors are found, you may add a refresh button to the page.
2) In sheet1, select the cell A6, click [Select Widget]>[Button]>[Event] in the right-hand pane, click Add Event and select [Click] to add a click event to the button.
3) Click the event to edit it, select [JavaScript] as the type and input the following JavaScript in the input box below:
contentPane.reloadCurLGPPane();
4) Click the [Attribute] tab and type [Refresh] as the button name.
5) Preview the effect in Data Entry Preview mode: query Order 10002 and modify the quantity of the first product in sheet2 from 5 to 10. Return to sheet1 and modify the delivery company from DHL to Emery. Click the Refresh button and you can that the editing record with respect to the delivery company in sheet1 has been cleared and the delivery company has been reverted to DHL while the modified quantity in sheet2 remains.
Template 3
V. Cross-sheet operations
1. Cross-sheet calculation
1) Still take the above-mentioned template Multi-sheet.cpt for example. If you wish to fetch in a sheet the values of some cells from another sheet, you may achieve this by using the expression 'sheetname'!cellname. In this expression, sheetname represents label name, expressed as a string enclosed in single quotation marks; cellname is the cell identifier. The two is connected by an English exclamation mark.
2) In sheet1, right-click the cell B4 and select [Clear]>[Content].
3) In the cell B4, type the formula ='sheet2'!E4, which means the value of the cell B4 in sheet1 is identical with the value of the cell E4 in sheet2. When editing the formula, you must check [Preserve formula in data entry].
4) Preview the effect in Data Entry Preview mode: query Order 10002 and you can see that the amount in sheet1 equals to the subtotal in sheet2. Modify the product quantity in sheet2 to change the subtotal from 51,000 to 123,000. Return to sheet1 and you can find that the amount has changed to 123,000.
2. Cross-sheet submission validation
1) In multi-sheet data entry, you may need to set certain constraints on data entered in the current sheet on the basis of the data in other sheets before submission. This is called cross-sheet validation. For instance, the current template contains two sheets and you cannot submit it unless a certain value in sheet2 is smaller than a certain value in sheet1.
2) Take the above-mentioned Multi-sheet.cpt for example. To make the subtotal smaller than the original order amount in the database, you may execute the following: drag the field amount in ds1 into the cell B6 in sheet1 as the validation criteria.
3) Click [Template]>[Data Entry Attributes], switch to the [Data Validation] tab and add a [Built-in Verify].
4) Click [+] to create new validation content, double-click the input field below Validation Formula and input 'sheet2'!E4<='sheet1'!B6, input ='Subtotal exceeds Amount' in the input field below Validation Error Information.
In the case of cross-sheet submission validation, uncheck [Submit Current Sheet Only] of the Submit button (see IV – 1), or validation before cross-sheet submission will be invalid.
5) Preview the effect in Data Entry Preview mode: query Order 10002, scale up the quantity in sheet2, click Data Validation or Submit and you can see a message showing “Subtotal exceeds Amount!”. Return to sheet2, scale down the quantity, click Submit and you will see a message indicating success.
