Pivot tables with pivot panels in Jitterbit App Builder

Pivot Panels are a technique used in data processing, and represent a table of statistics that summarizes the data from a more extensive table(s). In App Builder you can create client-side Pivot Panels by selecting the Pivot Panel Type when creating a Panel, and leveraging a data object to define the data to be displayed in the table. This guide will step you through how to create a Pivot Panel as well as limitations and notes when using Pivot Panels in App Builder.

Example Pivot Panel, Row = Customer, Column = Employee, Value Control = Sum of Order Total

To create a pivot panel

Prior to creating and adding a Pivot Panel to a page in your App Builder Application, you should have a data object already created in the Business Logic layer that the Pivot Panel will be using. The data object should have values for the Row, Column, and Value Control that the Pivot table will display, and also needs to aggregate the data so that there are no duplicate values.

1. Navigate to App Workbench > Add a Page
2. Assign a Name for the new page, and click Save. For example: Order Pivot
3. Click Create
4. Select the single row Page Layout
5. Click Add Panel, and select Pivot as Panel Type
6. Select the data object the pivot table should use as the Source
7. Review the assigned Name and edit if needed
8. Click Save
9. Click Create under Controls and select the Column that will represent the Row data in the Pivot table. For example: Customer
10. Set the Control Type to Row
11. If the data selected is an ID and you want to change it to Name, modify the List Control Properties information to select the appropriate List Business Object Set, Key, and Title.
12. Click Save and click the Create+ button
13. Select the Column that will represent the Column data in the Pivot Panel. For example: Employee
14. Set the Control Type to Column
15. If the data selected is an ID and you want to change it to Name, modify the List Control Properties information to select the appropriate List Business Object Set, Key, and Title.
16. Click Save and click the Create+ button
17. Select the Column that will represent the Value data in the Pivot panel. For example: Sum of OrderTotal
18. Set the Control Type to Value
19. Click the X button to exit out of the Control definition screen, click the Run button to preview the results in the application

Limitations and notes on pivot panels

• Pivot Panels are limited to display 1 Row, 1 Column, and 1 Value Control.

• Child Panels can only bind to the Pivot Panel on the Row Column.

• It is the responsibility of the data object to provide unique values per Column header / Row header combination. The data object should SUM or COUNT or use whatever aggregate function it needs. A pivot panel will throw an exception if duplicate values are detected, which implies that you need to modify the data object to perform the appropriate aggregation.

• Row and Column headers can be looked up using a Source table. For example, you can convert a EmployeeID to an Employee Name when the pivot table is rendered. This is setup similar to how a List Control is Configured - Source table, ID Column, Name Column.