---
title: "Box BQL Custom Charts"
canonical: "https://support.appfire.com/space/RDD/544866320/Box%20BQL%20Custom%20Charts"
format: markdown
---
## **PUBLISHED:THIS WAS THE WORK IN PROGRESS**
> Macro (toc)
> Macro (excerpt)
>
> 
>
>
> [BQL CUSTOM CHARTS](/wiki/pages/resumedraft.action?draftId=172656070)
>
> [Create custom charts based on BQL or filters as tables or charts, with grouping and aggregations](/wiki/pages/resumedraft.action?draftId=172656070)
## Overview
Teams have their own key insights for their **Jira Software** projects, agents need specific answers to be solved for their **Jira Service Management** queues, and so on. You could name more examples where a user needs to perform a different query to search in Jira for relevant information. That’s why the *Jira Query Language* (*JQL*) was built for. It’s a powerful search based on clauses (field + operator + value) to return a list of issues, which for the sake of reuse, can be saved as *[filters](https://support.atlassian.com/jira-software-cloud/docs/save-your-search-as-a-filter/)*.
This gadget displays the result of a JQL query or an existing [filter](https://support.atlassian.com/jira-software-cloud/docs/save-your-search-as-a-filter/), as a table or a chart. Select a predefined filter or a custom JQL `Select filter or custom JQL`, then indicate how you want to display the results `View Type`, the `Columns` you want to display in your report in case of a table (drag and drop to change the order), and performing grouping and/or aggregations (see next sections for further information).
> ℹ️ For example, to list all the issues in the “Teams in Space” project (project = “TIS”) and still pending to do (status = "TO DO") sorted by CreationDate in descending order (ORDER BY created DESC) you’ll type the following JQL query
> ℹ️
> ℹ️ `project = "TIS" AND status = "TO DO" ORDER BY created DESC`
> ℹ️
> ℹ️ [Get started with JQL](https://www.atlassian.com/software/jira/guides/expand-jira/jql) or have a look at the [Advanced Search](https://support.atlassian.com/jira-software-cloud/docs/use-advanced-search-with-jira-query-language-jql/) to master queries in Jira
You can select any of the following visual metaphors to represent the resulting data by the JQL in the `View Type` field: Table, line chart, tile chart, pie chart, bar chart, grouped bar chart, stacked bar chart, or multi-line chart.
---
## View type: Table
You can view the results of your JQL query as a table, where you can select the columns that are displayed.
Using the table view, you can display results as:
- Calculated columns
- Group of results
- Aggregation of results
### Calculated columns
Calculated columns are a powerful way to add data to a table.
In this field, you find the Customer Portal column that is not natively available in Jira.
**Customer Portal**
The column provides a link to the issue in the corresponding Customer Portal link for users of the Customer Portal.
Use the Customer Portal when you share a dashboard with customers so that they can access the ticket.
For more information see:
[https://appfire.atlassian.net/wiki/spaces/RDD/pages/146309699](https://appfire.atlassian.net/wiki/spaces/RDD/pages/146309699)
The example shows how the Customer Portal column links to.
Instead of:
```
https://acme.atlassian.net/browse/ISM-12
```
the column links to:
```
https://acme.atlassian.net/servicedesk/customer/portal/1/ISM-12
```
### Group results
`Group results by selected columns`
If you are familiar with the `GROUP BY` statement in SQL, you can choose how to group the results of your JQL query.
**NOTE**
When you select to group the results from the previously selected columns,** the result rows are split into groups, based on their values**. This means that only one row is displayed for each of the groups.
Pay attention to selecting the columns based on the results that you want to display for each group
**EXAMPLE**
If we filter the previous list of issues by the column **Status**, the results show only two rows, one for the status *To Do* and the other for the status *Done*.
Selecting the columns: **Status, Assignee, **and** Project**, the resulting list displays three rows.
The grouping of columns is typically combined with aggregations.
### Aggregations
Aggregations are also common in the SQL domain. These functions get the values of grouped rows as the input of that function to return a calculated value.
This gadget supports five functions:
- **Count**. It returns the number of rows in that group.
- **Sum**. It returns the addition of the sequence of the numbers of the group.
- **Min**. It returns the smallest value of the range of values of the group.
- **Max**. It returns the largest value of the range of values of the group.
- **Mean**. The arithmetic mean, sums the values of the grouped rows and divides the result by how many rows are being averaged.
For example, if we want to calculate the mean of the story points
---
## View type: Chart
> ✅ Remember that:
> ✅
> ✅ **Chart by**. Indicates the x-axis values (horizontal axis), also referred to as the series, or in layman’s terms, the concepts we see in the legend of the chart.
> ✅
> ✅ **Type**. Is the aggregation you want to apply to the **field**: count, sum, max, min, and mean. Check the [aggregations](https://appfire.atlassian.net/wiki/spaces/RDD/pages/146309431/JQL+Custom+Charts#Aggregations) section.
> ✅
> ✅ **Field**. Indicates the y-axis values (vertical axis), what we want to plot in the chart.
### Line chart
A line chart represents the results of your JQL query as a series of data points connected by a straight line. It’s common to visualize trends over periods or dates in the x-axis.
### Multi line chart
This chart is a line chart with more than one line, which is useful when we need to compare data in a time series or trends.
### Tile chart
This visual representation presents the information in a set of tiles. You can display the result of your JQL and apply aggregations. For example, you can display the number of requests of each request type of your Jira Service Management support desk, or display the sum of all the original estimates of the assigned issues, by assignee.
### Bar chart
A bar chart represents the results of your JQL query as a series of rectangular bars with the height proportional to the represented values. It’s useful to compare results of different types or categories.
For example, **Chart by** *assignee*, **type** *count,* and **field** *issues *would display a chart where each assignee would display the number of assigned issues:
### Grouped bar chart
We can perform more complex comparisons of the information returned by our JQL query by grouping by specific fields. In the previous example, we could add a **Group by ***status*, and we’d see the number of issues by assignee grouped by the issue status:
So for each category, in our case the **Chart by** *assignee*, there are two or more coloured bars, which labels can be seen at the top of the chart.
### Stacked bar chart
Similar to the grouped bar chart, but in this case, the combined results of the groups are on top of each other. Thus, the height is the combined result, making it not appropriate for cases with negative values.
### Pie chart
A pie chart is a visualization of your data in a circular graph, where each slice indicates the quantity of the result of your JQL. When a pie chart has several sections or slices, it’s difficult to compare one with another. To overcome this inconvenience, the pie chart comes with a table indicating the values of each slice and the % it represents.
When the field selected in “Chart by” has a date format e.g., Created, a new selector appears to allow grouping by day, week, month, quarter, or year, making the results easier to understand and communicate.
### Area chart
An area chart is a visualization that represents the results of your JQL query as a series of data points connected by a straight line and filled with a colored area beneath it, which can help emphasize the magnitude of the values being represented. An area chart is often used to show trends over time or categories, as well as to compare multiple series of data.
### Stacked area chart
A stacked area chart is a variation of the area chart where the areas are stacked on top of each other instead of being overlaid. Each stack represents a category or a group, and the height of the stack represents the total value of the group. It's useful for showing the relative contribution of each group to the total, as well as for tracking changes in the total over time. However, stacked area charts may not be suitable for displaying negative values, as they can become difficult to read.
---
## Custom colors
Colors have the power to communicate meaning, provoke emotions, and highlight information. Within our organizations and teams, it’s common to associate concepts with specific colors, making easier and quicker the communication of ideas and information.
Whenever you select a chart, you’ll be able to select the color of each specific segment or value of it:
The color picker allows the selection of color either by hexadecimal code, RGB, or our pre-defined 24-color palette (selected based on the right contrasts and tones).
### Hide segments
Not all the results are needed when communicating information in a chart, sometimes because one of those values or segments distorts the results or deviates the attention.
Clicking on the 👁 eye icon, the corresponding segment is hidden (or shown) in the chart:
### Reorder segments
In the same line of hiding segments or customizing the colors of our charts, rearranging the position of the existing segments or values reinforces the way we transmit the information with our charts.
Just drag and drop any segment or value from the six dots on the left side of the segments and move it upwards or downwards to the correct place, and the chart will be updated accordingly.
## Configuration
Name your gadget meaningfully, so everyone knows at a glance what it is about and when to use it. Fill out the rest of the fields as applicable, namely:
- The **data source**, where the source Jira instance is installed.
- The** JQL (Jira Query Language) query or filter** to filter the list of issues (see the [JQL documentation](https://support.atlassian.com/jira-software-cloud/docs/use-advanced-search-with-jira-query-language-jql/)). If you don’t add any and click “Load”, the gadget will request all the content in the source instance, this might cause performance issues. We recommend adding at least one clause, for example, to list all the issues of the project Teams in Space use the clause `project = "TIS”`. Remember that the gadget returns the results of the query, which are not fixed and could change over time.
- The **View Type** parameter to indicate the visual representation of the JQL query results in a Table, line chart, tile chart, pie chart, bar chart, grouped bar chart, stacked bar chart, or multi-line chart.
- The **Chart by **parameter (just for charts) to select the x-axis value, in other words, what you see in the horizontal axis. These values or concepts are indicated in the chart legend.
- The **Type** parameter (just for charts) to indicate what type of aggregation you want to apply to the **field** parameter: Count, sum, max, min, mean. Check the [aggregations](https://appfire.atlassian.net/wiki/spaces/RDD/pages/146309431/JQL+Custom+Charts#Aggregations) section.
- The **Field** parameter (just for charts) to select the y-axis values (vertical axis), think of what you want to count (or any other aggregation), and plot in the chart.
- The **columns** (just for the table view) that will appear in the list to display the search results. At least one column has to be present. Drag and drop to reorder the columns.
- **Group results by selected columns **(just for the table view) in case you want to group the results of the query by the previously selected **columns**. The resulting rows the result rows will be split into** **groups, based on their values. Check the [group results](https://appfire.atlassian.net/wiki/spaces/RDD/pages/146309431/JQL+Custom+Charts#Group-results) section.
- The **aggregations** (just for the table view) in case you need to perform calculations -Count, sum, max, min, mean- over the results. Check the [aggregations](https://appfire.atlassian.net/wiki/spaces/RDD/pages/146309431/JQL+Custom+Charts#Aggregations) section.
- Finally, indicate if you want to use the current settings for all the compatible gadgets in the dashboard. This option eases the pain of configuring one by one all of the gadgets with the same default configuration
## Integrations
- :jira_software:
Jira Software
> Macro (contentbylabel)
- :jira_service_management:
Jira Service Management
> Macro (contentbylabel)
We are working on our growing catalog of [https://appfire.atlassian.net/wiki/spaces/RDD/pages/146309915](https://appfire.atlassian.net/wiki/spaces/RDD/pages/146309915) and [https://appfire.atlassian.net/wiki/spaces/RDD/pages/146310010](https://appfire.atlassian.net/wiki/spaces/RDD/pages/146310010), but contact us you want us to expedite a specific one, visit our [https://appfire.atlassian.net/wiki/spaces/RDD/pages/146309984](https://appfire.atlassian.net/wiki/spaces/RDD/pages/146309984).
## Dashboards
This gadget is not included in any pre-defined dashboard, check other orphan gadgets:
Orphan gadgets
> Macro (contentbylabel)
## See also
> Macro (contentbylabel)