Open topic with navigation
After you enable table data and select the source by using the table object's Table Data property page, special Table Data Field data sources become available to help you design your table. These Table Data Field data sources are listed in the Data Sources pane of the Toolbox, and you can drag them to table cells to create a text object that is linked to the data source. For more information about how to add a Table Data Field data source to an object, refer to Creating Tables that Use Dynamic Data.
After you link a Table Data Field data source to an object, you can configure its properties by using the object's Data Sources property page. The property page includes settings that you can specify to calculate sum or other aggregate functions in the Table Data Field data source. You can also specify the range for which the function is calculated.
The following types of functions are available:
-
Sum: Calculates the total of all the values in the specified range for the Table Data Field data source. Supported for numeric values.
-
Average: Calculates the average of all the values in the specified range for the Table Data Field data source. Supported for numeric values.
-
Minimum: Determines the minimum value in the specified range for the Table Data Field data source. Supported for numeric values and date values. Also supported for text values, where it can determine the first string, as sorted by a case-sensitive, neutral language comparison.
-
Maximum: Determines the maximum value in the specified range for the Table Data Field data source. Supported for numeric values and date values. Also supported for text values, where it can determine the last string, as sorted by a case-sensitive, neutral language comparison.
-
Count: Calculates the number of records in the specified range.
-
List (Comma-Separated Values): Concatenates all record values in the specified range into one long string in which the values are separated by commas and quotation marks (such as "data1","data2","data3").
-
List (Tab-Separated Values): Concatenates all record values in the specified range into one long string in which the values are separated by tabs.
-
List (Single Value Per Line): Concatenates all record values in the specified range into one long string in which the values are separated by line breaks.
The following range options are available:
-
All Records: Calculates the function by using all records that are listed in the table.
-
Current Page: Calculates the function by using all records that are listed in the table as it appears on a single printed item.
-
Current Group: Calculates the function by group, using the records that are specified in the table.
|
You can use a Table Data Field data source outside of the table in which it is defined, but when you do this, only an aggregate function can be used for the data source. The function defaults to Sum, and for the range you can select All Records or Current Page (Current Group is not supported). A typical use for this configuration would be to place the total price in an object outside of the table in which it is defined.
|
Examples
Use the examples in this section to become familiar with the basic steps for calculating a sum or other aggregate function in the Table Data Field data source. For each example, you must first create a new table object that is connected to a sample database and then configure a Table Data Field data source, as described in the following procedure.
|
If you want to go through the steps for both examples, you must create a separate BarTender document for each example.
|
The examples in this section require that you create a text file database that has a table that includes a Product field, a State field, and a Price field, such as the following sample database.
Product Knife Dish towel Coffee mug Fork Spoon Tongs Hot pad Dinner glass Wine glass Spatula Teacup Juice glass Tumbler
|
State WA OR CA WA WA WA OR CA CA WA CA CA CA
|
Type Cutlery Linens Glassware Cutlery Cutlery Implement Linens Glassware Glassware Implement Glassware Glassware Glassware
|
Price 2.99 12.99 6.99 2.99 2.99 3.99 8.99 3.99 6.99 4.79 5.59 1.99 4.99
|
After you create the sample database, add a table object to your template that is connected to the database, and then sort the database fields according to the value in the State field. This procedure simultaneously creates a Table Data Field data source that appears on the Data Sources pane of the Toolbox.
To add a table object that is connected to your database and then sort by the State field
-
On the object toolbar, click the icon. Alternatively, click Table on the Create menu.
-
Click Create Table from Database. The Database Setup wizard starts.
-
Complete the wizard to connect to the sample database.
-
In the Database Setup dialog, click Sort Order.
-
In the Available Fields list, double-click State to add it to the Fields to Sort By list, and then click OK.
-
Click the template at the location where you want to add the table object, and then drag the sizing handles to fit the table within the template.
In this example, you will use the Table Properties dialog to configure a new row for the Sum aggregate function and then add the function, which will sum the prices for each State group.
To configure the table properties
-
Open the Table Properties dialog. To do this, double-click the table object. Alternatively, right-click the object, and then click Properties.
-
In the navigation pane, click Rows to display the Rows property page.
-
In the Rows area, click to add a new row.
-
In the Row Properties area, under Table Data, follow these steps:
-
Click Template, and then select Group Footer in the list.
-
Click Group By, and then click to select the State check box in the list.
-
Click Page Break, and then select Yes in the list. The table object displays a new, empty row.
-
Click Close to close the Table Properties dialog.
To add the Sum aggregate function
-
On the Data Sources pane of the Toolbox, expand the Table 1 node under the Table Data Fields node.
-
Drag the Price data source to the bottom right table cell under the existing Field: Price cell, as shown.
-
Right-click the Field: Price object that you just created, and then click Properties to open the Text Properties dialog.
-
On the Data Source tab, verify the following:
-
The Calculate sum or other aggregate function check box is selected.
-
The Function option is set to Sum.
-
The Range option is set to Current Group.
-
Click Close.
-
Click Print Preview. Your items appear on three labels, grouped by the State field, with the total price calculated for each state and shown in the aggregate field that you added.
In this example, you will use the Table Properties dialog to configure a new row for the Count aggregate function and then add the function, which will count the number of products that are in each State group.
To configure the table properties
-
Open the Table Properties dialog. To do this, double-click the table object. Alternatively, right-click the object, and then click Properties.
-
In the navigation pane, click Rows to display the Rows property page.
-
In the Rows area, click to add a new row.
-
In the Row Properties area, under Table Data, follow these steps:
-
Click Template, and then select Group Footer in the list.
-
Click Group By, and then click to select the State check box in the list.
-
Click Page Break, and then select Yes in the list.
-
Click Close to close the Table Properties dialog. The table object displays a new, empty row.
To add the Count aggregate function
-
On the Data Sources pane of the Toolbox, expand the Table 1 node under the Table Data Fields node.
-
Drag the Product data source to the bottom left table cell under the existing Field: Product cell, as shown.
-
Right-click the Field: Product object that you just created, and then click Properties to open the Text Properties dialog.
-
On the Data Source tab, verify the following:
-
The Calculate sum or other aggregate function check box is selected.
-
The Function option is set to Count.
-
The Range option is set to Current Group.
-
Click Close to close the dialog.
-
Click Print Preview. Your items appear on three labels, grouped by the State field, with the total number of products calculated for each state and shown in the aggregate field that you added.