User Guide
Before you start
To be able to import data from Jira into the SQL database, your Jira administrator should grant you permission to create, edit, watch, and export connectors.
If you don't have access to these actions, please contact your Jira Administrator to grant SQL Connector for Jira permissions first. Jira Administrators can always access application configurations.
How to create a data source?
Step 1. Click SQL Connector Tab on Jira’s main menu. Then choose Data Sources.
Step 2. On the opened page, select Create a Data Source.
Below you can find a description of the fields, buttons, and settings of the data source creating page:
Title section
1. Name - Enter the data source name.
2. Database - Select a database type and version. You can add and edit a database connection beneath this row.
3. Description - Enter the Data Source description.
4. Share settings - You may share this data source with users or groups of users who have permission to use the application.
5. Enagle Auto-Refresh
Share settings
Select users - start typing to find and add relevant users.
Select groups - start typing to find and add relevant users group.
Select project roles - start typing to find and add relevant roles.
Allow re-sharing - select this checkbox if you allow users to re-share this data source.
Save - click to apply changes.
Please note! Other users with access to SQL Connector for Jira app might not get all data from this data source due to different Jira permissions.
Filter issues section
1. All - choose if you want to export all existing issues.
2. Select by JQL - choose if you want to use JQL to create your own request and filter data.
JQL (Jira Query Language) is a flexible tool that allows you to search for issues, projects, etc., in Jira and pinpoint exactly what you are looking for.
3. Basic - choose if you want to use standard fields filters. You can learn how to use Basic filters below.
4. Issues limit - Maximum number of issues to export (leave blank for no limit). If the Jira Administrator set the issue limit user will see an alert message.
5. Export timeout - Export timeout per 1 table, in minutes (leave blank for no timeout).
6. Filter Issues by Plan - Filtering by Advanced Roadmaps (former Portfolio) plans.
Select Fields section
1. Search field - enter the required field that you want to display in the report.
2. Magnifier - click to search the required item you want to add to your data source.
3. Clear the search field.
4. Tabs with Jira Fields:
Jira Core Fields - displays a list of regular Jira Core fields and items
Jira Software Fields - displays a list of regular Jira Software fields and items (if Jira Software is licensed)
Jira Service Desk Fields - displays a list of regular Jira Service Desk fields (if Jira Service Desk is licensed)
Tempo fields - displays a list of Tempo Timesheets and Tempo Planner fields (if plugins are installed)
Advanced Roadmaps - displays Advanced Roadmaps plugin fiedlds (if plugin is installed)
Insight fields - displays Insight Asset Management plugin fields (if plugin is installed)
5. Issues - one of the issue files subsection. Choose if you want to add all fields in a certain subsection.
6. Checkbox - check the box with the required field.
View Options section
1. Click Cog icon to open View options
2. Click Show IDs to see the field names how they will appear in database. Click again to hide IDs.
3. Click Show Types to see the field types. Click again to hide Types.
Expandable tables
1. Click + to expand the Table and see all available fields for export
2. Select the exact fields you need for the reports
3. Click the checkbox next to the Table name to select all fields from that Table
Filter Insight fields section
Since Insight schemes can store thousands or even millions individual objects each with potentially many attributes, so importing the attributes table without any filtering will be a huge problem. Also many of the objects and attributes could be of little interest to the reporting engine.
Insight Fields filter is located at tab Insight and allows to filter out the uninteresting data and greatly reduce import record set.
1. To filter by Object Type start to type Object Type name and choose from the list
2. To filter by Object Type Attributes start to type Object Type Attribute name and choose from the list
Filter will be applied to selected Insight Tables where Obect_type_id and/or Object_type_attributes_id are present.
“Apply” section
1. Create - click to create a data source.
2. Preview - Click to check the source name/description/issue filters/export items.
3. Close - leave data source creating page. Changes you’ve made will not be saved.
How to import data into SQL Database?
To work with an SQL database, you must first add the database configuration. Go to SQL Connector > Databases.
Click the Add Database button.
Fill in the details of the database configuration. To get more in-depth help in these details, please check out the Database Configuration section.
Click the Test connection button. If everything is set up correctly, you should see the ‘Test connection success.’ message.
Click the Create button. Once again, you should see a message indicating that the database has been created successfully.
Now you can use this database when creating or editing the data sources. You can either enable auto-refresh for a certain time of the day or a cron pattern or refresh data manually.
To manually export data, click on the Export data button found on the data sources page. Upon clicking this button, the status of the data source will change to Loading, and the data export progress will be updated gradually. Once the export process is complete, the data source status will change to Exported.
That’s it. Now you can check that data was successfully exported to the database.
How to archive Data Source
Archiving Data Sources can be helpful when connectors should be disabled without being removed.
Click the … next to the Export data button.
Select Archive
3. Click Archive to archive the Data Source
4. Archived Data Source can be found in the Archived tab. From the Archived tab a data source can be Restored or Deleted.
How to use Basic issue filters?
Step 1. After choosing Basic issue filters click Select issue filters button below.
Step 2. In the opened pop-up window you can:
Projects - filter issue by project.
Boards - filter issues by boards.
Issues Types - filter issue by types (Epic/Story/Task/Bug/Sub-task).
Assignee users - filter issue by assignee users. Select assignee user and click +.
Reporter users - filter issue by reporter users. Select reporter user and click +.
Select period - filter issue by date.
Statuses - filter issue by status (Open/In Progress/Reopened/etc).
Apply form - apply chosen filters.
Note: Only fields that relevant to Basic Filter criteria will be shown.
E.g. user filters by ProjectABC (1). Then only relevant to the ProjectABC Issue Types(2) and Statuses(6) will be shown for further filtering. Moreover, only relevant to the ProjectABC custom fields will be shown in Issue Custom Fields section.
How to use Jira Software Tables filter?
Filtering schemes description for each table, taking into account the Software Tables filter
Boards
If Software Tables Filter by Projects or Boards applied filtration is carried out as follows:
Loading Boards by filtered Projects.
Loading Boards by filtered Boards.
All unique Boards are combined into one list and unloaded page by page.
If Software Tables Filter is not applied Boards unloaded page by page without filtering.
Sprints
If Software Tables Filter by Projects, Boards or Sprints applied with “Filter by Issues” (default) Sprints filtration scheme is carried out as follows:
Loading Projects Issues by filtered Projects.
Loading Boards Issues by filtered Boards Configured Filters.
Loading Sprints by Projects Issues.
Loading Sprints by Boards Issues.
Loading Sprints by filtered Sprints.
All unique Sprints are combined into one list and unloaded page by page.
If Software Tables Filter by Projects, Boards or Sprints applied with “Filter by Parent Boards” Sprints filtration scheme is carried out as follows:
Loading Boards by filtered Projects.
Loading Boards by filtered Boards.
All unique Boards are combined into one list.
Loading Sprints by unique parent Boards list.
Loading Sprints by filtered Sprints.
All unique Sprints are combined into one list and displayed page by page.
If Software Tables Filter is not applied, Sprints unloaded page by page without filtering.
Sprint Issues
If Software Tables Filter by Projects, Boards or Sprints applied with “Filter by Issues” (default) Sprints filtration scheme is carried out as follows:
Loading Projects Issues by filtered Projects.
Loading Boards Issues by filtered Boards Configured Filters.
Loading Sprints Issues by filtered Sprints.
All unique Issues are combined into one list.
Loading Sprints associated with unique Issues list.
All unique Issues unloaded page by page with the corresponding unique combination of Issue and Sprint
If Software Tables Filter by Projects, Boards or Sprints applied with “Filter by Parent Boards” Sprints filtration scheme is carried out as follows:
Loading Boards by filtered Projects.
Loading Boards by filtered Boards.
All unique Boards are combined into one list.
Loading Sprints by unique parent Boards list.
Loading Sprints by filtered Sprints.
All unique Sprints are combined into one list.
Loading Issues by unique Sprints list.
All unique Issues are combined into one list and unloaded page by page with the corresponding unique combination of Issue and Sprint.
If Software Tables Filter is not applied, data unloaded page by page with filtering Issues by Basic filter values.
If a Basic filter is not applied, data is unloaded page by page without filtering.
Board Charts and Sprint Report Charts
If Software Tables Filter by Projects, Boards or Sprints Board Charts filtration scheme is carried out as follows:
Loading Boards by filtered Projects.
Loading Boards by filtered Boards.
All unique Boards are combined into one list.
Loading Boards Issues by filtered Boards Configured Filters.
Loading Sprints by Issues.
Unload filtered values Sprints for filtered Boards page by page.
If Software Tables Filter is not applied, data is unloaded with filtering by Basic filter Boards values.
If a Basic filter is not applied, data is unloaded page by page without filtering.
For the Sprint Report Charts table, the filtering scheme is similar.
How to view Data Source Export History?
Click … button near the connector and select Export History
2. Once the page is opened, you will be able to view a list of all tables that have been exported using this connector. The information provided includes the name of the user who performed the data export, the table name (as each connector can pull data from multiple tables), as well as the start and end dates of the export process. You can filter the history by connectors, tables, users, start time, end time, and export start and end time.
