Reports

Reports provide a flexible canvas to combine narrative text, images, and dynamic data visualizations derived from your Jira information. The report editor functions similarly to Confluence, allowing you to freely type text, apply formatting (like bold, italics, lists), and insert images.

The core power of reports lies in their ability to embed live data generated by SQL Queries. You can insert data visualizations directly into your report content:

• Tables: Display raw query results in a structured tabular format.
• Charts: Visualize query results using various chart types (e.g., bar, line, pie) for easier trend analysis and comparison.
• Single Values: Extract specific aggregate metrics from your query results, such as averages, percentiles (P50, P85, etc.), minimums, maximums, or sums.

To incorporate dynamic data, you first select the SQL Queries you want to use within the report. For each query instance you add, you must specify:

1. Work Item Filter: The set of Jira work items the query should run against (defined by JQL, a saved filter, board, or sprint).
2. Parameters (if applicable): If the chosen SQL query uses parameters (like $status_category or $work_item_type), you provide the specific values for those parameters for this particular instance in the report.

This setup allows for great flexibility. You can reuse the same underlying SQL query multiple times within a single report, each time configured with different Work Item Filters or parameter values. For example, you could display several "Cycle Time" charts in one report: one showing cycle time for 'Bugs', another for 'Tasks', and perhaps another comparing cycle times across different sprints, all powered by the same parameterized cycle time SQL query.

How Report Calculation Works

When you view or refresh a report, the system performs the following steps to generate the dynamic content:

1. Identify Data Needs: The system first scans the report to find all embedded data visualizations (tables, charts, single values) and their associated SQL Queries and Work Item Filters.
2. Gather Required Fields: It determines the complete set of Jira fields needed by all the SQL queries used in the report.
3. Fetch Jira Data: The necessary work item data is fetched from Jira. To optimize performance, the system combines the required fields and fetches data for all unique Work Item Filters used in the report in as few API calls as possible.
4. Prepare Data Environment: Once the data is retrieved from Jira, it's loaded into a temporary in-memory database. A schema reflecting the structure of the fetched Jira fields is prepared.
5. Execute Queries Sequentially: The system then processes each data visualization instance one by one.
   • Filter for Context: Before running the SQL query for a specific visualization, a temporary work_items table (or view) is created within the database. This table contains only the Jira work items that match the specific Work Item Filter and parameters defined for that particular visualization instance.
   • Run SQL: The associated SQL query is executed against this filtered work_items table.
6. Render the Report: After all queries have been successfully executed and their results collected, the final report document is rendered, displaying the narrative text alongside the generated tables, charts, and single values.

This process ensures that each visualization displays data relevant to its specific configuration while optimizing the data fetching from Jira.

To help you get started quickly, the application also provides a library of pre-defined report templates, which come bundled with relevant SQL queries and visualizations.