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 https://actonic.atlassian.net/wiki/spaces/ARB/pages/edit-v2/7177437201#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:

{
    type: 'matrix',
    rows: [{ name: 'project' }, { name: 'issueType' }],
    cols: [{ name: 'status' }],
    measures: [{ name: 'countOfIssues' }],
};

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:

...
cols: [
  { 
    name: 'created', 
    group: { name: 'year' },
  },
],
...

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:

...
cols: [
  { 
    name: 'created', 
    group: { name: 'month' }, 
    format: {name: 'monthShort'},
  },
],
...

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:

...
cols: [
  { 
    name: 'created', 
    filter: { name: 'dateTimeInDateRange', values: ['2023-01-01', '2023-05-17'] },
  },
],
...

System Dimensions (Jira System fields)

Dimension id	Dimension name	Dimension type	Notes
`id`	Issue ID	number	The issue ID
`key`	Key	string	The Issue key as string
`summary`	Summary	string	The Issue summary as string
`description`	Description	string	The Issue Description (plain text) as string
`environment`	Environment	string	The value of the environment field (plain text) as string
`priority`	Priority	IssuePriority	The priority of the issue
`issueType`	Issue Type	IssueType	The issue type
`status`	Status	IssueStatus	The issue status
`statusCategory`	Status Category	string	The issue status category
`created`	Created	date	The creation time of the issue
`updated`	Updated	date	The time when the issue was last updated at
`dueDate`	Due Date	date	The time the issue is due
`resolutionDate`	Resolution Date	date	The time the issue was resolved at
`resolution`	Resolution	string	The resolution of the issue
`originalEstimate`	Original Estimate	duration	The original estimate of how long working on the issue would take, in seconds
`remainingEstimate`	Remaining Estimate	duration	The estimate of how much longer working on the issue will take, in seconds
`timeSpent`	Time Spent	duration	The time that was spent working on the issue, in seconds
`securityLevel`	Security Level	string	The security level set for the issue
`parent`	Parent	Issue	The issue parent
`project`	Project	Project	The project the issue belongs to
`assignee`	Assignee	User	The assignee of the issue
`reporter`	Reporter	User	The reporter of the issue
`creator`	Creator	User	The user who created the issue
`votes`	Votes	number	The number of voters of the issue. Returns an error if voting is disabled
`voters`	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
`watches`	Watcher	number	The number of watchers of the issue. Returns an error if watching is disabled
`watchers`	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`	Labels	string	The list of labels associated with the issue
`versions`	Affected Versions	string	The list of versions the issue affects
`fixVersions`	Fix Versions	string	The list of versions where the issue was fixed
`components`	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 `Input field` (string) type	string	For all the custom fields of String type (Input field)
Any custom field of `User Picker (Single picker)` type	user	For all the custom fields of User picker type (single-user or multi-user picker)
Any custom field of `Select List (Single picker)` type	string	For all the custom fields of Select List type (single-picker)
Any custom field of `Date` type	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
`countOfIssues`	Count of Issues	Issue	Number	Count	Counts issues regardless of their statuses
`countOfOpenedIssues`	Count of Opened Issues	Issue	Number	Count	Counts issues where issues `resolution is EMPTY`
`countOfResolvedIssues`	Count of Resolved Issues	Issue	Number	Count	Counts issues where `resolution is not EMPTY`
`countOfStatusChanges`	Count Of Status Changes	Issue	Number	Count	Counts number of times the issue changed its status
`sumLoggedTime`	Sum of logged time	issue.worklog	Duration	Sum
`sumOfOriginalEstimate`	Sum of Original Estimate	Issue	Duration	Sum	Sum of values from Original estimate field
`sumOfTimeRemaining`	Sum of Time remaining	Issue	Duration	Sum	Sum of values from Time remaining field
`sumOfVotes`	Mean of Votes	Issue	Number	Sum
`latestIssueCreationDate`	Latest Issue Creation Date	Issue	Date	Date	Calculates the latest date of the issue creation in a selected scope of issues
`newestIssueCreationDate`	Newest Issue Creation Date	Issue	Date	Date	Calculates the latest date of the issue creation in a selected scope of issues
`sumOfAgeBusinessDays`	Sum of Age (Business days)	Issue	Duration	Sum	Sum of “Age (Business days since creation)” calculated field values
`sumOfAgeCalendarDays`	Sum of Age (Calendar days)	Issue	Duration	Sum	Sum of “Age (Calendar days since creation)” calculated field values
`sumOfBusinessDaysSinceUpdate`	Sum of Business Days since last update	Issue	Duration	Sum	Sum of “Business Days since last update” calculated field values
`sumOfCalendarDaysSinceUpdate`	Sum of Calendar Days since last update	Issue	Duration	Sum	Sum of “Calendar Days since last update” calculated field values

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)

{ 
    type: 'matrix',
    rows: ['project'],
    cols: ['assignee'],
    measures: 'countOfIssues',
    orderBy: {
      orderType: 'rowHeaders',
      direction: 'asc',
    },
},

Sort by one of the data columns (both in Matrix and List)

{ 
    type: 'list',
    cols: ['issuekey', 'project', 'issuetype', 'priority', 'ageCalendarDaysSinceCreation'],
    orderBy: {
      orderType: 'column',
      direction: 'asc',
      colNum: 1,
    },
},

Sort by one of the total columns (in Matrix only)

{ 
    type: 'matrix',
    rows: ['project'],
    cols: ['issuetype'],
    orderBy: {
      orderType: 'totalColumn',
      direction: 'desc',
      colNum: 0,
    },
    measures: ['countOfIssues'],
},

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

Configuration examples

Full Scripted Reports example

...

// initialization
const pivotopia = await SR.pivotopia.getInstance();
// aggregation configuration
const config = {
    type: 'matrix',
    rows: [{
        name: 'project'
    }],
    measures: [{
        name: 'sumLoggedTime',
    }],
};
// configuration validation
const validatedConfig = pivotopia.getValidatedConfig(config);
// get required jira fields, needed for the data aggregation 
const requiredFileds = pivotopia.getRequiredFieldsForConfig(validatedConfig);
// get jira issues with required fields only
const issues = await SR.getIssuesByJQL('created > -7d', requiredFileds.join(','));
// aggregate data
const resultSet = await pivotopia.calculate(pivotopia.jiraIssuesToFlatObjects(issues), validatedConfig);
// convert aggregation results to a html table
const tableHtml = pivotopia.resultSetToHtmlTable(resultSet, validatedConfig);
console.log(tableHtml);
    
...

Simple grouping (pivoting) by some Jira field

const config = {
  type: 'matrix',
  rows: [{
    name: 'project' // here could be any other Jira field
  }],
  measures: [{
    name: 'sumLoggedTime',
  }],
};

Timesheet for issues and filter for dates

const config = {
    type: 'matrix',
    rows: [{
        name: 'issue',
    }, ],
    cols: [{
        name: 'worklogStartedDate',
        filter: {
            name: 'dateTimeInDateRange',
            values: ['2024-01-01', '2024-03-31'],
        },
    }],
    measures: [{
        name: 'sumLoggedTime',
    }],
};