Skip to Content

Server-side pivot table in Jitterbit App Builder

You can configure server-side pivot tables.

What is a pivot table?

A pivot table is a table of statistics that summarizes the data of a more extensive table (such as from a database, spreadsheet, or business intelligence program). This summary might include sums, averages, or other statistics, which the pivot table groups together in a meaningful way.

Pivot tables are a technique in data processing. They enable a person to arrange and rearrange (or "pivot") statistics to draw attention to useful information.

Three ways to pivot data in App Builder

There are essentially three different ways to pivot data in App Builder. This article will discuss using server-side pivot. The three available options are:

  1. Client-side pivot (using a panel type of pivot)
  2. Server-side pivot (doing the pivot as a rule on the server)
  3. Manually constructing the pivot in a rule – non dynamic columns:
select
    EmployeeId,
    sum(case when year(OrderDate) = 1996 then 1 else 0 end) as [1996],
    sum(case when year(OrderDate) = 1997 then 1 else 0 end) as [1997],
    sum(case when year(OrderDate) = 1998 then 1 else 0 end) as [1998],
    count(OrderId) OrderCount
from Orders
group by EmployeeId
order by EmployeeId

Server-side pivot table capabilities

Benefits of using a server-side pivot table vs. a client-side pivot table include:

  • Pivot table gets generated on the server, and then gets connected to a standard Multi-Row Panel
  • Works with Panel Filter and Download CSV options
  • Conditional Formatting is supported with server-side

How to create a server-side pivot table

Configuring a server-side pivot table is similar to creating other Charts in App Builder, however, a Pivot Table requires the following three Column Usage Types be defined within the mvSQL Pivot Rule logic:

  1. Pivot Row
  2. Pivot Column
  3. Pivot Value

pivottable.png

Step 1. create a pivot rule containing mvSQL logic for the pivot table

  • Navigate to the Business Logic Layer
  • Click the + Business Rule button
  • Assign the Rule a Name. For example: Customer (Order Pivot)
  • Select Pivot as the Purpose
  • Select the Target Table. For example: Order

  • Click Save

    pivotrule.png

  • Confirm the Tables tab displays the required Table(s) needed for your logic

  • Click the Columns tab and create three entries along with their associated logic to represent the Row, Column, and Value

    pivotcolumns.png

  • Confirm the appropriate Usage Type has been set for each of the following values:

    • Pivot Row
    • Pivot Column
    • Pivot Value

  • Click the Where tab and add any additional logic, if required

    wherelogic.png

  • Save the Rule and Validate the results to make sure they're as expected, and no errors are encountered

Step 2. create a page using the pivot rule

  • Add a new Page to hold the pivot table (or you can add a Panel to an existing Page)
  • Add a Panel, here you will select Multi-row Panel as the Type
  • Select the Pivot Rule created in Step 1 as the panel Source
  • Click Save
  • Click to Select the Controls representing the Pivot Row and Pivot Value
    • The first Control should be the value associated with the Pivot Row
    • The second Control should be the value associated with the Pivot Value

  • Add any Conditional Formatting logic to the Control(s)

    pivotpage.png

  • Run the page