Confluence is the absolute worst system for project management, and you should avoid using it as such at all costs.
Getting reports from Confluence is a real pain in the ass, and you may have trouble explaining to your management why after paying for enterprise licence your diagrams are suddenly looking worse than those you used to prepare in Excel. The diagrams don’t have labels you would expect and look like total shit compared to the cool diagrams in MS Office which trace their history of awesome looks for like 20 years all the way back to MS Works. And guess what? If you want to fix the length of the tables you have to pay for that! That’s a separate paid plugin! Even such primitive things as Gantt charts are also paid additions! That is called extortion.
You also can’t help yourself with old good sql query, since while Confluence requires a DB, it uses it merely to store user passwords and some minor stuff, while storing real data in txt files scattered across the server file system. Most modern and well-designed CMS store all data in the DB, and this peculiarity makes Confluence so stupidly unique.
There are some report generating tools – paid of course – but I doubt it suits even merely sophisticated needs.
So, what I will try to show in this tutorial is how to organize content in Confluence and how to extract it to somewhat good-looking reports with ability to export data to Excel, where you can finally create what any sane manager expects from you. Again, designated project management systems are available on the market, from Atlassian as well, so if you have an option – opt out of Confluence. Confluence is born for other purpose… and that purpose is yet to be discovered.
But if for some reason you don’t a choice and have to stick with Confluence, here’s what you should do without paying a single penny to those wiki sharks.
First, create a template for project page. Each project must have its own page, with a single style information table for all projects. Enclose this table in Excerpt macro. It would be great to enclose each field in the table as a separate excerpt, but alas, multiple excerpts is a paid upgrade. Primitive things are always monetized in Confluence world, get used to it.
All projects pages must be created according to this template. Also add a name of the project linked to the page in this table, preferably the tiny link provided by Links to this Page tool. Tag the page with some unique label, like project number. This identifier should be used to label all content related to the project. All project progress information should be entered as blog posts with two obligatory labels: the project number and a stage identifier as suggested by the workflow in your organization. The best practice for IT-related projects is having at least 4 labels: Achievement, Issue, Risk and Nexstep. Make sure all blog posts are tagged with project label and one of those tags.
Now, create a separate page and divide it into sections with Section macro, one for each project. Now divide each section into 5 columns. Use excerpt include macro to include the excerpt from the project page to the first column. Name the other 4 columns according to the project workflow, in this example the names are Achievement, Issue, Risk and Next steps. In each of the column insert a blog posts macro, and restrict the labels in the macros to the project label and one of the workflow labels – achievement, issue and so on. Use + before the label to make it obligatory. Copy and paste this section below and change the links and labels to fit the next project. Repeat until each project has section on this page.
Now, save the page and check out the report. It gathers the project data as excerpt from the project page, meaning if the data is changed, the report will reflect the change. And it also gathers the blog post related to the project and puts them into columns accordingly. If you remove the stupid created by, No blog posts found, Blog Posts and all that hardcoded shit that is sure to make your management grumble about the freaking Facebook the staff is using to prepare spreadsheets these days, this report looks quite good. If you added links to the project data tables as instructed above, you may click on the project name to open project page, and you can also click of the blog post titles to see achievement or issue details.
However, it is very hard to export the data to a sane system like MS Excel. To do that, we will have to create a bit different report.
Using the same columns and excerpts macros described above, create a new page. But this time, don’t use sections, just create a page that stacks project page excerpts in long, long row. The resulting page is far from being management-friendly, so treat it as a technical page. The magic begins when you select everything and paste it to Excel. As special paste options are not available yet, just paste it. Now select all contents of the spreadsheet, go to another one, and use special paste to transpose your selection. Now sort everything from A to Z, scroll down and remove the table labels you still have in the selection. When done, you’ll have a neat little Excel table with all data exported from Confluence in less than 2 min. And now you can use the power of Excel to create whatever charts and diagrams your management requires.
And one more report I would like to show you is not for management, but for accounting.
Create a Budget template, with a single excerpt containing a 1 row table with project link and all the fields required by your finance department. Now use this template to add a budget page to all your projects. Then create another page, filled with excerpt include macro pulling excerpts from all those budget pages. The resulting report is also technical. Select all the data and paste it
to Excel. Sort the data alphabetically to remove the repetitive cell titles. Now you’ve got a budget table with all your projects financials in a single Excel spreadsheet which you accounting may use to plan the budget.
And while they do that, politely ask them if your organisation could invest in a less out of the ass project management tool.