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.
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.
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.
But is it enough to process the outputted table containing several hundreds of rows? Unfortunately, no.
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
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 firstname.lastname@example.org and I will contact you.