OrgJQL integrates your org data with Jira so you can create queries using orgOfand create manager-specific reports and notifications using Rovo.

Getting Started

Start here to set up your org data and create your first reports.

Quick Start

OrgJQL Quick Start

Step 1: Create a spreadsheet of the users and managers you want to include

If you don’t have any headings, the first column will be treated as the users and the second column their managers.

NOTE: These must be Jira display names

If you do use headings, the user heading must be user and the manager heading must be manager. Here’s an example Google Spreadsheet:

Step 2: Click on Update Org Data and add org data

Click on the “Update Org Data” card

Then click on the “Add Org Data” button:

Copy your spreadsheet data and paste into the editor and click “Save”

Step 3: Test your data

Click on the “Test Org Data” tab and enter a query using your org data. OrgJQL defines an orgOf JQL function that takes a manager’s name and returns a list of everyone who rolls up to them. Here’s an example query:

assignee in orgOf("William MacLane")

Verify the expansion is correct.

Using Rovo to create manager-specific reports

In OrgJQL, all one-off reports are built for a given “root manager”. This is the top level manager for the report. Every report has an “Org Chart Control” that allows you to drill up and down into the org:

When you select a manager, all issues in the report are filtered to that manager.

Creating a basic report

To get to the OrgJQL Assistant, click on “Chat” at the upper right of the window, then on “Browse Agents”, and then select “OrgJQL Assistant”.

To create a basic report for a manager (essentially, just the org information) you can enter the following into the Rovo chat:

Create a new report for William MacLane

The OrgJQL Assistant will return a link that shows you the org control for that manager

Adding issues to your report

To add issues to an existing report, type something like:

Add this JQL to the report: labels=2024-Q4

The OrgJQL Assistant will create a new link to a report using that JQL to pull issues.

If there are issues in your report, you’ll see an “Issues Table” bottom in sidebar at the left:

Clicking this will display an issues table filtered by the selected manager. The selected manager is shown in purple at the top of the Issue Table:

The shortcut for toggling the issue table is: t.

Using Rovo to Create Leaderboards

A Leaderboard is a component that rolls issues up by manager and slices them by another dimension. By default, the values in a leaderboard are issue counts, but that can be changed to another statistic (e.g., Story Points, Average Days Late, Max Days Late, etc.)

To add a leaderboard to an existing report, type something like this into the OrgJQL Assistant chat:

Add leaderboard to this report

Rovo will response with a new link that has the leaderboard in it.

Clicking on a pie slice or cell will select all issues in that go into that. These will be highlighted in orange.

After clicking on the “In Progress” pie slice

If some but not all issues in an element are selected, the label will be shown in italics:

After clicking Leo Moore’s In Progress cell

All selected issues are indicated in the sidebar

Clicking on this control brings up the Issue Table:

Using Rovo to Create Issue Graphs

Issue Graphs show the critical path for a set of issues that are linked by “blocked on” relationships and which have time estimates. To add a leaderboard to a report, type something like this for any of the linked issues:

Add an issue graph for BIZDEV-246

NOTE: OrgJQL traverses the graph by pulling prerequisite and dependent issues in steps. Picking an issue in the center of the issue graph will cut the traversal time in half.

Selecting a manager will drills down into the Issue Graph for that manager. For instance, if we select “Leo Moore”, we’ll see this:

Issues can be selected by clicking on a node or by selecting issues in the issue table:

Tweaking an OrgJQL Report

All OrgJQL reports are constructed in the Forthic language. Rovo understands how to construct this Forthic. You can also construct this Forthic on your own (or tweak the Forthic that Rovo comes up with).

Understanding Forthic
Tweaking a Leaderboard
Tweaking an Issue Graph

Administration

Go deeper into OrgJQL and look at JQL Executions and customize your own org functions.

Review JQL Executions

In this section, we'll go over how to view and manage OrgJQL executions. These "expansions" are stored by Jira for performance, but they are updated when issues are created or updated or when org data is changed. You can also force update any or all OrgJQL executions here.

OrgJQL Review Executions

Background on JQL Executions

When a JQL function is executed, it expands into a JQL fragment that is substituted into the query.

Jira stores these expansions in a pre-computations database to improve performance. The expansions for OrgJQL are listed in the table of the “Review JQL Executions” page.

Searching for clauses

You can filter the table by clauses:

Refreshing expansions

You can click on the “Refresh” control for each clause to force update an expansion:

When are Precomputations Updated?

The following trigger OrgJQL precomputation updates:

Ticket is created (after 15 minutes of inactivity)
Ticket is updated (after 15 minutes of inactivity)
OrgJQL data is updated
Custom JQL function code is updated
When “Refresh All” is clicked in the “Review JQL Executions” page

The refresh job is asynchronous and runs from a queue, so only one job is ever running at a time.

NOTE: Precomputations are only updated if their values have changed.

Stopping a Precomputation Refresh Job

You can stop a running precomputation job by clicking “Force Stop”:

Customize JQL Functions

In addition to the built-in orgOf JQL Function, you can create custom JQL functions in Forthic.

OrgJQL Customize JQL Functions

The following functions can be customized:

fx.users allows you to write custom Forthic code to return an array of users
fx.issues allows you to write custom Forthic code to return an array of issues

Background

OrgJQL was written in Forthic, a language developed at LinkedIn to build hundreds of internal Jira-based tools for program and engineering management. Forthic is also the language that allows fx.users and fx.issues to be customized on-the-fly, directly within your Jira instance without any external servers.

Forthic in Jira

The editor for customizing OrgJQL functions is actually a Forthic IDE. You can execute words directly within the IDE and see their effect in the developer pane at the bottom of the editor:

You can define new words using this pattern:

: NEW-WORD WORDS TO BE EXECUTED;

For instance, we can extract 3 + from the previous example and create a new ADD-3 definition and use it like this:

Example: Customize fx.users

Getting all the users that roll up into a manager

Let’s start by showing how to get all the users in a manager’s org. This is actually how the orgOf JQL function is implemented.

NOTE: This assumes you have some org data set up. See “Managing Org Data” for more info.

The FULL-ORG word returns all of the users that roll up into a manger (see the right sidebar for more info). For instance, executing the following line will return all of the users in Kai Bentley’s org:

"Kai Bentley" FULL-ORG

A silly example to get all users whose names are more than 12 characters

This is an example that does something that you can’t do directly in JQL. It’s a silly example, but it illustrates a useful pattern. We’ll use SELECT to filter a list of names given a predicate, in this case, all names greater than 12 characters:

"William MacLane" FULL-ORG "LENGTH 12 >" SELECT

We could use this in JQL by defining a new word called MACLANE-LONG:

: MACLANE-LONG "William MacLane" FULL-ORG "LENGTH 12 >" SELECT;

We can then save the code and then test the expansion in the “Test Custom JQL Functions” tab:

Get the direct reports for a manager

We can use the DIRECTS word to get the direct reports for a manager. For instance, we could use this definition to get the direct reports for “William MacLane”:

: MACLANE-DIRECTS "William MacLane" DIRECTS;

In the case of DIRECTS, we could actually just call it directly (since we can pass data to words through Forthic):

Customizing fx.issues

We’ll implement a classic JQL function that returns all Epics that have unresolved Stories. We can do this in 10 lines of Forthic:

["project"] VARIABLES

: FIELDS                       ["Summary" "Parent"];
: project-EPIC-JQL             ["project = " project @  " and resolved = NULL and issuetype='Epic'"] CONCAT;
: project-EPICS                project-EPIC-JQL FIELDS jira.SEARCH;
: project-EPIC-KEYS-STR        project-EPICS "'key' REC@" MAP UNIQUE "," JOIN;
: project-STORY-JQL            ["Parent in (" project-EPIC-KEYS-STR ") and issuetype='Story' and resolved = null"] CONCAT;
: project-UNRESOLVED-STORIES   project-STORY-JQL FIELDS jira.SEARCH;
: project-EPICS-w/UNRESOLVED   project-UNRESOLVED-STORIES "'Parent' REC@" MAP UNIQUE SORT;

: INCOMPLETE-EPICS             (project !) project-EPICS-w/UNRESOLVED;

(Don’t worry about the syntax just yet – we’ll go over each line)

Giving it a try

The word that gives us the incomplete epics is called…INCOMPLETE-EPICS. We can run it like this:

"ENG" INCOMPLETE-EPICS

Passing a parameter to a definition

If we look at the INCOMPLETE-EPICS definition, we see that the first part of it looks like this: (project !). In Forthic, parentheses are whitespace, so you can ignore those. We use parentheses to indicate that we’re going to take the top of the stack and store it in a variable. The project variable is defined in Line 1.

The ! word sets the value of a variable to what’s just before it. So, this line sets the value of project to "ENG":

"ENG" (project !)

Naming convention: prefix definitions with variable names

When a definition uses the value of a variable, we prefix its name with the variable’s name. You can see that in Lines 4 through 9 above. Each of those words relies on the value of project being set. Once it is, any of those can be called at will. The project variable establishes a context that those words run in.

Line 3: FIELDS

The FIELDS word simply defines an array of Jira fields to pull for each issue.

NOTE: Forthic arrays start with [ and consist of all objects on the stack up until ]. Because we use the state of the stack rather than syntax to define arrays, commas are not needed to separate array elements.

Line 4: project-EPIC-JQL

The project-EPIC-JQL word constructs the JQL to pull all the epics for a project by concatenating strings together (the @ word retrieves the value of a variable).

This is what it looks like when project has the value of "ENG":

Line 5: project-EPICS

The project-EPICS word uses the jira.SEARCH word from the Forthic Jira module to pull issues. This word expects a JQL string and an array of Jira fields. Here are the results when it’s run:

Line 6: project-EPIC-KEYS-STR

The project-EPIC-KEYS-STR word extracts the issue keys for each of the epics. It uses the following to do this:

"'key' REC@" MAP

The MAP word maps a Forthic string over each of the epics, retrieving the result for each item into a new array. For this case, the string "'key' REC@" retrieves the key for each epic. The effect is to convert a list of epic records into a list of their associated issue keys. Here is the result of this word:

Line 7: project-STORY-JQL

The project-STORY-JQL word constructs JQL to pull all of the unresolved stories for the specified epics. It is similar to the project-EPIC-JQL word, but instead of a project, it concatenates the epic key string that we just computed into a JQL string:

This JQL string pulls all of the unresolved stories that are part of the epics of project

Line 8: project-UNRESOLVED-STORIES

The project-UNRESOLVED-STORIES word just pulls unresolved stories using the JQL from Line 7 and the fields from Line 3. Here’s the result from running this:

Line 9: project-EPICS-w/UNRESOLVED

The project-EPICS-w/UNRESOLVED word just gathers the Parent field (the Epics) from each of the unresolved stories. It uniques and sorts them. These are the incomplete epics for the project in question.

Going Further

Forthic brings new capabilities to Jira. You can essentially develop applications directly within a Jira instance instead of having to go through a long build and deploy cycle. We’ve only touched on what Forthic can do. To learn more, please sign up for Forthic Office Hours or ask us a question at the Forthix JSM portal.

References

Forthic github repo: GitHub - linkedin/forthic
Forthix syntax: forthic/docs/SYNTAX.md at main · linkedin/forthic
Forthic global words: forthic/docs/modules/global_module.md at main · linkedin/forthic
Forthic Jira words: forthic/docs/modules/jira_module.md at main · linkedin/forthic

Managing Org Data

In this section, we’ll show you how to add and update organizational data for OrgJQL

OrgJQL Manage Data

Update Org Data Tab

This tab lists what data you currently have set up. The default data is what’s used for the orgOf JQL function. Non-default data can be used when customizing the fx.users function. See “Customizing JQL Functions” below.

Viewing Org Data

You can view the data for an organization by clicking on its name. For instance, clicking on “Engineering” might show the following:

Updating Org Data

The format of the organizational data is an array of user/manager pairs. Note that the names are Jira Display names. Also note that, in OrgJQL, arrays use whitespace as separators instead of commas.

An alternative way to updating org data is to copy and paste it from a spreadsheet. In this case, the first column of the spreadsheet should be user names and the second, the corresponding manager:

When adding or updating org data, just add the relevant users and managers. While you can add your entire company, you can always start with a smaller, more relevant set of people.

Default Data

The default data set can be set by ensuring the “Default Data” slider is on for the dataset in question:

The default data is used for the orgOf word and for many of the customized JQL functions.

Testing Org Data

To check your default org data, go to the “Test Org Data” tab and enter a query using the orgOf JQL function:

You can check the query by clicking the “View in Jira” link.

OrgJQL with Rovo AI