Atlassian Confluence

Table Filter and Charts with SQL macro

In this blog post, we continue work with table data in Confluence. In the previous posts we have outlined the principles of work with the Page Properties Report macro and dynamic Scaffolding tables. This time we will show you the way to pull data from some external production database and show it in Confluence in the form of a data table.

Here, the SQL for Confluence add-on will assist us with data retrieval from an external database. We will show you the several practices to process the generated data and transform it into something useful and collaborate on this data in Confluence.

So no more lyrics, just action!

SQL for Confluence

SQL for Confluence add-on comes into Pro and Express bundles. Which one to choose depends mainly on your requirements, but Express edition will be quite enough for a start.

What does this add-on allow you to do in Confluence? With this tool you can pull extracts  from JDBC compliant databases, such as MSSQL, Oracle, DB2, PostgreSQL and MySQL. So, if you have some production systems operating on the basis of these databases you can output results of your database queries to Confluence pages. In such a way you can share this data with other colleagues of yours.

You can create multiple connections to databases and then insert the SQL macro with some query on Confluence pages for showing the full or partial data series. Anyway, the outputted data amount maybe quite large for analysis with native Confluence tools. The existing capabilities of the macro does not provide any tools for filtration or data aggregation, so to get a deep insight into data will be quite difficult. Of course, you can filter data by means of SQL queries or aggregate this data, but all the time you will have to modify your SQL queries to achieve these results. 

Don’t panic as there is always a solution!

In our case we deal with the financial transactions of money transfer to foreign countries. Data is pulled from PostgreSQL database through the configured profile.

sql_configuration_in_confluence

Once we insert the SQL macro on the page and enter the appropriate SQL query to pull data, we are set for getting data from the database. Our query fetches data from the finance table and takes it for the period of one month, in our case this is June.

sql_macro_on_confluence_page

The macro also provides additional augmentations using which you can configure look of the outputted data, enable column sorting, define column types and perform basic mathematic operations on data and other options.

sql_macro_configuration_on_confluence_page

But is it enough to process the outputted table containing several hundreds of rows? Unfortunately, no.

confluence_sql_query_table_on_page

While you are googling for the way to filter data in Confluence and aggregate it against some parameter, we will present you the way to do this with our add-on with little time spent and much work done.

Our data table pulled from the transaction processing system contains information about money transfers, and includes the following data:

  • Date – date when the money transfer transaction was performed.
  • Time – time when the the money transfer transaction was performed.
  • First and Last Name – name of the person who transacted money.
  • Target Country – country which the money transfer transaction goes to,
  • Transfer Amount – amount of money transferred.
  • Transfer Tax – tax imposed on transacted money amount.
  • Payment Method – payment method used for money transaction.
  • Service Agent – the service agent who registered the money transfer transaction.
  • Transaction Status – status of the money transfer transcation.
  • Personal Number and Transaction ID are internal identifiers of the client and transaction in the system. We are not interested in them (smile)

Here, we will apply Table Filter and Charts add-on that s bundled with three macros:

  • Table Filter – filters data of any type and format with the set of available filters.
  • Pivot Table – aggreates and summarizes table data in the form of one- or two-dimensional pivot tables.
  • Chart from Table – visualizes data series with the dynamically charts and graphs.

So, watch the video below and find out the way to conquer this table with Table Filter and Charts add-on!

If you have any questions, feature requests or problems with add-on configuration, drop an email to vrutkevich@stiltsoft.com and I will contact you.

Recommended Articles