How do I work with table data in Confluence?
The main issue you encounter when working in Confluence is quite poor functionality for work with data tables. So is there any solution that can simplify your experience and save you a bit of time on other activities? Is there anything that can transform this great collaboration platform into business intelligence or business analytics solution.
Of course, you can use MS Excel for interpretation of your business data with all its rich capabilities to filter tables, generate charts and reports, or create pivot tables with summarized and aggregated values. But it will not be convenient for you to share your results with the rest of the team as you will have to copy-paste all your tables with different sets of filtered data, insert charts as screenshots on Confluence pages and so on. But all this will be immediately shattered when either you or your manager will decide to change business intelligence approach and change one or two filtration parameters, so all you work will be in vain.
Table Filter for Confluence – Your New Friend
While you are sitting and thinking about the way to manage your table with business data in Confluence, thousands of users are using Table Filter for Confluence in their everyday work. So what capabilities does it offer to its users?
The add-on provides a bundle of the three macros including the following:
- Table Filter allows you to add multiple filter types that you can apply to your table data and get only the data you are interested at the moment.
- Pivot Table lets you generate a table with summarized and aggregated data values from the source table containing repeated data labels.
- Table Chart is a reporting tool that generates a dynamic chart with automatic update of its values upon switch of column with data values or table filtration.
All these macros can be combined on your Confluence pages for better experience and flexible management of your business data.
Filtering Table Data in a Blink of an Eye
Table Filter add-on supports tables generated by other macros and add-ons. For example, you have some external system for business intelligence data that stores financial transactions. This system allows you to export the transactional data to a CSV file, which you can further transform with the reporting tools into business analytics information. As usual, you can load this data into MS Excel and do what you want. But what will you do if you need to share your work results with your colleagues and team mates?
Here Table Filter will be a real help for you! All you need to do is install Advanced Tables for Confluence add-on from Bob Swift, attach the CSV file, map the CSV macro to this file and then place all these into the Table Filter macro. In real life it will take not more than a minute to perform all these actions. The add-on is also compatible with other add-ons that generate table data on the basis of SQL, HTML or JSON data, or macros that output the list of pages, JIRA issues and so on.
Then you can add the appropriate filters and set their options. You can utilize the following five filter types:
- Drop-down filter fetches all the unique values from the selected table column and allows you to select one or multiple values to filter your table by.
- Free text filter allows you to enter custom text query to filter data in the selected column. This filter support regular expression so you can enter custom text queries for flexible data filtration.
- Global filter works the same way as free text filter but it filters data throughout the whole table.
- Number range filter allows you to specify the number ranges for filtering data values within the pre-defined range.
- Date filter allows you to select the time period for showing data values.
Optionally, you can set the default values for each filter you have added, regulate width of filter boxes, hide the unnecessary columns. Additionally, you can enable the static or dynamic row numbering, set the default sorting order for each table column and enable the automatic fitting of the table to your screen area.
After saving the page you will see the filtration pane with the table located under it. So if your table is long enough, it will be a real pain to find the necessary entry or a range of entries.
By using the filtration pane you can filter your table data and get a list of values you are interested in. You can also select the current filters in the macro body by clicking the Save Settings button or temporarily hide the unnecessary columns.
So let’s see what way Table Filter add-on can help you with table filtration:
- We have dropdown filters that allow us to filter table data by names of agent, types of customers, transaction types and transaction statuses.
- Free text filters are used to search for specific IDs of transactions or filter some ID pattern using the regular expressions.
- The number range filters for the Transaction Amount and Fee Amount columns provide us with a capability to set the appropriate ranges for transaction and fee amounts.
- By using the date filter we can get the list of transactions that were performed within the specific time period.
- With the global filter we can filter our table with business analytics data by some custom text query or regular expression.
- The column with customer ID’s is not necessary so we can hide it through the dedicated option on the filtration pane.
For easier work with table data you can also enable dynamic or static row numbering, so each time you can see the number of entries matching your filtration criteria.
Pivot Charts in Confluence with Ease
If your table has the constantly repeated values in one or several columns, you may find it useful to count how many times this or that value is available in the table, or count the sum of values throughout the table. For this objective you can try to transform your source table into a pivot table.
As usual, all you need is to insert your data table or the macro generating a table into the Pivot Table macro and that’s done.
So what’s next? You need to spend a bit of your time on configuration of this macro. No worry, you will do it quickly. You need just to select the table column containing repeated labels across which data will be summarized and aggregated. Afterwards just select the table column containing data values which a specific operation will be performed on. You can choose among the following operation types:
- Average – calculates the average value for each unique entry from the available data values.
- Sum – summarizes data values for each unique entry in the table.
- Count – counts the number of data values for each specific label.
- Min / Max – calculates the minimal and maximal values for each label in the table.
The last configuration step is selection of the decimal and thousands separators, and the number of decimal places to show.
Once complete, just save the macro and the page. You will get a table with aggregated or summarized data values depending on the selected operation.
By using the configuration pane located to the right side of the pivot table, you can instantly switch to another column with row labels, change the column with data values for calculation or select a new operation type. All the updates can be directly saved in the macro without switching the page to edit mode.
You can combine your pivot table with the Table Filter macro and get a capability to generate a pivot table on the basis of filtered data. In such a way you can exclude the irrelevant values and get the actual overview of business data. For example, you needn’t data for the whole month, you just need a pivot table for the first half of the month, or count transactions within the range of 500-1,000 USD.
Dynamic Charts in Confluence
Most Confluence users got used to the Chart macro, to the constant necessity of editing Confluence pages for changing the configuration of the macro and impossibility to change the chart type on the fly. This issue will no longer be your problem once you try the Table Chart macro.
Add this macro to the page and paste the table or a macro generating a data table within its body.
Then you need to define the settings of the macro. As usual, you can select the chart type, the column with labels or dates and the column with data values. Optionally, you can specify the appropriate date format and decimal separator, set the size of the chart, and enable the hiding of the source table and chart controls.
Once you save the page with the Table Chart macro, you will get a generated chart with the chart controls displayed when you point to the area to the right side of the chart.
You can change the chart type on the fly, switch the column with data values, change the size of the chart and download the image of the chart. All your modifications can be saved in the macro body directly.
Combining Macros for Great Flexibility and Productivity
You can feel the real power of Table Filter add-on when you combine all the three macros. We placed the monthly report on conducted financial transactions into a CSV file and attached to the Confluence page. Output of the table data from a CSV file is performed by the CSV macro from Advanced Tables add-on. CSV macro is further placed within Table Filter, then into Pivot Table, and finally into Table Chart macro.
So what objectives can be achieved with the combination of macros. Let’s see in details.
In the first case we would like to see the number of transactions with the amount within $500 to $1700 processed by agents in the first half of September. Table Filter macro provided us with the filters to filter the transactional data by necessary criteria. Pivot Table macro calculated the number of transactions matching the filtration criteria per each day, and Table Chart macro generated a Time Line chart showing the spread of transactions within the first half of the month.
One more case allows tracking the transaction amount processed by each service agent. Here we do not apply any filters, Pivot Table macro generates a table with summarized data by each service agent. Table Chart macro outputs the pie chart showing the correlation of processed transaction amounts by service agents.
One more example shows the correlation of average transaction amounts per each transaction type in the second half of September.
Table Filter can become a real alternative to Excel’s table management functions. Easy-to-use and fully customizable macros allow you to get most of your data tables, including different types of filtration, generation of summarizing tables and visualization of table data in charts. Get a pretty new experience with Table Filter macro in Confluence, and forget MS Excel forever. If you already using Table Filter add-on, but you are missing some features feel free to submit your ideas at the dedicated feedback forum.