SQL support via Smart QL integration

Owned by Piotr Bojko
Last updated: Sept 13, 2018
2 min read

Smart Reports App by its own supports only reports based on JQL queries. In order to have support for SQL queries in jrxml fiels, additional addon - Smart QL App - must be used. Having both Smart Reports App and Smart QL App despite enabling support for jrxml templates with SQL queries - has more benefits:

  • Smart QL App provides an option to connect Jaspersoft Studio or Jasper Designer straight to the Jira via custom JDBC Driver (like to a real database server). You can create your jrxml files natively in the best tools for it which is an IDE (Jaspersoft Studio or Jasper Designer). 
  • Smart QL does not expose the Jira data in its raw format, but transforms it to more intuitive, readable and reporting friendly set of database schemas with additional functions and views.
  • What is more important - Smart QL App filters the data it exposes for Jaspersoft Studio or Smart Reports App through Jira permission system. It is not a gateway to the Jira native database - you still must have appropriate roles, permissions or belong to particular group to see some issues, just like in Jira itself.
  • Smart QL App focuses on projecting Jira data but also Jira Software, Service Desk or other Jira apps in a way you can extract, search or calculate any information you need with a power of SQL, JQL and XPath while Smart Reports is meant to be a reporting tool based on Jasper tech. Both plugins are developed separately and have its own road maps, schedules. You can update one app without another and it will work.

How does SQL support in Smart Reports work

  1. User who want to create report template (jrxml file) uses Jaspersoft Studio.

  2. Jaspersoft Studio connects to Smart-QL App via jdbc-over-http(s) using Smart-QL JDBC driver.  

    Database schema available through Smart-QL App is documented here.

  3. User uploads the created template to Smart-Reports App. Then he can generate report in an app.
  4. When generating report Smart-Reports App uses its submodule (also Jira Addon) - Smart Reports Smart-QL Integration - to fetch data for a SQL query from jrxml. Query is computed with Smart-QL App.