Pivotopia
- Andrei Pisklenov
- Zhenya Elfimova
- 1 Overview
- 2 Configuration
- 2.1 Matrix calculation type
- 2.2 List calculation type
- 2.3 Number calculation type
- 2.4 Dimensions
- 2.4.1 Group
- 2.4.2 Format
- 2.4.3 Filter
- 2.4.4 System Dimensions (Jira System fields)
- 2.4.5 Custom Field Dimensions (Jira Custom Fields)
- 2.5 Measures
- 2.5.1 Format
- 2.6 OrderBy (result sorting)
- 3 ResultSet
- 4 Configuration examples
Overview
Pivotopia is a powerful headless data aggregation JavaScript library designed to work with any JSON object arrays. It serves as a core library for Universal Report and also functions as a utilities library for Scripted Report, enabling the creation of pivot tables from Jira issues effortlessly.
It could assist you in aggregating issues, worklogs, comments or any other data to a pivot-like table with a simple config. Navigate to examples below to get some insights.
Pivotopia could be used at the step when data has already been received, and you would like to aggregate it by some of the fields. It is always a painful part and requires a lot of validation and debugging, so why not simplify it as much as possible?!
To start data aggregation in your Scripted Report, you need to follow a few steps:
Initialize Pivotopia instance.
Prepare an aggregation config.
Validate the configuration.
Call the "calculate" method with an array of issues and a validated configuration object.
Visualize the calculated results.
In your scripted report, data aggregation with Pivotopia could look like in example Pivotopia | Full Scripted Reports example
Configuration
Where config could have different fields depending on the calculation type.
Currently, there are 3 available calculation types:
Calculation type | Description |
|---|---|
matrix | A matrix ("pivot table") report type is a way to organize and display data in a two-dimensional grid or matrix format. It allows you to compare two sets of data by presenting them as rows and columns in a matrix. Each cell within the matrix represents the intersection of data from the respective row and column, making it easy to analyze relationships between these data points. Matrix reports are commonly used for comparing data across different dimensions, such as issue fields, worklog fields, comment fields, etc., providing a structured view of complex data for better insights and decision-making. |
list | "List" report type provides a tabular representation of issues with any existing or calculated fields. It's a way to display issue information in a structured and compact format, making it easier to view and work with a list of issues. Every row of the report represents a single issue. The core value lies in the ability to use calculated fields such as “Age (Business days since creation)”, “Effort Variance”, etc (see “Issue: Example list of calculated fields” below). |
number | A number report displays a single or multiple numeric values, such as a total spent time, average remaining estimate, or any other quantitative metric for a scope of issues. This report are straightforward and focus on presenting a specific number without additional visual elements. |
Matrix calculation type
For matrix calculation, at least one row and one measure is required.
{
type: 'matrix';
rows: Dimension[];
cols?: Dimension[];
measures: Measure[];
orderBy?: OrderBy;
}List calculation type
For a list report, column configuration is required, with at least one column dimension.
{
type: 'list';
cols: Dimension[];
orderBy?: OrderBy;
}Number calculation type
Number report type consists only of measures. At least one measure is required.
{
type: 'number';
measures: Measure[];
}Dimensions
Dimensions are responsible for creating Row Headers and Column Headers in Matrix report type, and for content cells in List report type.
Let's take a look at the configuration:
This configuration will show a matrix report with project and issue type in rows and statuses in columns. Both rows and columns are Dimensions. So they represent Row Headers and Column Headers in the calculation result:
Group
Groupers in pivot tables are essential for organizing and summarizing data effectively. They allow you to aggregate data by specific categories or time periods, making it easier to analyze patterns and trends. This helps in gaining insights from large datasets by breaking them down into manageable, meaningful chunks.
Different dimensions support various grouping options. For example, all date and time-related dimensions can be grouped by day, week, or month.
Example:
Format
A formatter in a pivotopia is a configuration option, that allows you to apply different styles and formats to the cells within the table. This includes customizing how numbers, dates, and text are displayed, and setting up different formats for headers, totals, and data cells. It enhances readability and helps emphasize important information in your data.
Example:
Filter
A filter is a feature that allows you to display only the data that meets certain criteria. By applying filters, you can focus on specific subsets of your data, such as a particular date range, category, or value, making it easier to analyze relevant information and draw insights from your data.
Example:
System Dimensions (Jira System fields)
Dimension id | Dimension name | Dimension type | Notes |
|---|---|---|---|
| Issue ID | number | The issue ID |
| Key | string | The Issue key as string |
| Summary | string | The Issue summary as string |
| Description | string | The Issue Description (plain text) as string |
| Environment | string | The value of the environment field (plain text) as string |
| Priority | IssuePriority | The priority of the issue |
| Issue Type | IssueType | The issue type |
| Status | IssueStatus | The issue status |
| Status Category | string | The issue status category |
| Created | date | The creation time of the issue |
| Updated | date | The time when the issue was last updated at |
| Due Date | date | The time the issue is due |
| Resolution Date | date | The time the issue was resolved at |
| Resolution | string | The resolution of the issue |
| Original Estimate | duration | The original estimate of how long working on the issue would take, in seconds |
| Remaining Estimate | duration | The estimate of how much longer working on the issue will take, in seconds |
| Time Spent | duration | The time that was spent working on the issue, in seconds |
| Security Level | string | The security level set for the issue |
| Parent | Issue | The issue parent |
| Project | Project | The project the issue belongs to |
| Assignee | User | The assignee of the issue |
| Reporter | User | The reporter of the issue |
| Creator | User | The user who created the issue |
| Votes | number | The number of voters of the issue. Returns an error if voting is disabled |
| Voters | User | The voters of the issue. Only returns the voters the requesting user has permissions to view. Returns an error if voting is disabled |
| Watcher | number | The number of watchers of the issue. Returns an error if watching is disabled |
| Watchers | User | The watchers of the issue. Only returns the watchers the requesting user has permissions to view. Returns an error if watching is disabled |
| Labels | string | The list of labels associated with the issue |
| Affected Versions | string | The list of versions the issue affects |
| Fix Versions | string | The list of versions where the issue was fixed |
| Components | string | The list of project components the issue belongs to |
Custom Field Dimensions (Jira Custom Fields)
Matchers will be applied to a wide range of custom fields based on custom field type (customfield_10010, etc).
Dimension matcher | Dimension type | Notes |
|---|---|---|
Any custom field of | string | For all the custom fields of String type (Input field) |
Any custom field of | user | For all the custom fields of User picker type (single-user or multi-user picker) |
Any custom field of | string | For all the custom fields of Select List type (single-picker) |
Any custom field of | date | For all the custom fields of Date type |
Measures
Pre-defined measures which are available on all the instances
Name | Display Name | Scheme | Type | Category | Description |
|---|---|---|---|---|---|
| Count of Issues | Issue | Number | Count | Counts issues regardless of their statuses. In Format you can also select display with bar to get visual display of percentage of issues in a group compared to the total count of issues. |
| Count of Opened Issues | Issue | Number | Count | Counts issues where issues |
| Count of Resolved Issues | Issue | Number | Count | Counts issues where |
| Count Of Status Changes | Issue | Number | Count | Counts number of times the issue changed its status |
| Sum of logged time | issue.worklog | Duration | Sum |
|
| Sum of Original Estimate | Issue | Duration | Sum | Sum of values from Original estimate field |
| Sum of Time remaining | Issue | Duration | Sum | Sum of values from Time remaining field |
| Mean of Votes | Issue | Number | Sum |
|
| Latest Issue Creation Date | Issue | Date | Date | Calculates the latest date of the issue creation in a selected scope of issues |
| Newest Issue Creation Date | Issue | Date | Date | Calculates the latest date of the issue creation in a selected scope of issues |
| Sum of Age (Business days) | Issue | Duration | Sum | Sum of “Age (Business days since creation)” calculated field values |
| Sum of Age (Calendar days) | Issue | Duration | Sum | Sum of “Age (Calendar days since creation)” calculated field values |
| Sum of Business Days since last update | Issue | Duration | Sum | Sum of “Business Days since last update” calculated field values |
| Sum of Calendar Days since last update | Issue | Duration | Sum | Sum of “Calendar Days since last update” calculated field values |
| Progress by issues | Issue | Percent | Percent | Calculates progress by count of resolved or open issues relative to the total count of issues |
| Progress by time | Issue | Percent | Percent | Calculates progress based on time spent or remaining estimate relative to the original estimate |
Format
Depending on the measure type, there could be a few possible formatting options.
OrderBy (result sorting)
Ordering configuration is a part of Pivotopia input parameters. Depending on which column we are using for sorting, it could look like:
Sort by Row Headers (in Matrix only)
Sort by one of the data columns (both in Matrix and List)
Sort by one of the total columns (in Matrix only)
ResultSet
ResultSet is a result object, which Pivotopia returns after the calculation. It consists of 6 elements:
Row Headers
Column Headers
Data Cells
Total Row
Total Column
Grand Total