How does PocketQuery differ from the SQL Macro + Chart Macro combo by Appfire?

[Rory McCallion]

I'm a power data analyst here at Atlassian and I use the SQL Macro + Chart Macro + Run Macro to make all sorts of powerful reports. I'm excited to find a new reporting pluging for writing SQL and visualizing the results! I'd like to know what features or benefits I can get from using PocketQuery, and if there are any specific usecases which make it preferable to my usual toolset. Thanks!

– Rory

[Felix Grund (Scandio) · Accepted solution]

Hi Rory,

Thank you for your question and your interest in PocketQuery! I know that the macros you mentioned can be very powerful and you can do some good reporting with them. There might be use cases, however, where PocketQuery might help you achieve expressive reports. Let me summarize some key strengths of the plugin:

• Only administrators can create/edit/delete SQL queries. These queries can have named parameters that are replaced by the macro parameters specified by users of the PocketQuery macro. This way, we want to ensure that queries are secured by administrators and users don't have to fiddle with SQL code.
• Queries can be dedicated to specific spaces by administrators, so you can scope where they can be used by the macro.
• Administrators can create custom templates for rendering the result from the database. These templates can contain arbitrary Velocity code, enhanced by a few objects that are provided in the Velocity context by PocketQuery. In these templates you can also create different kinds of charts (see next point).
• Advanced charting technology is integrated in the plugin. PocketQuery has a JavaScript API that embedds Google Charts and you can create custom templates for your macro result that render all kinds of charts. It gives you all the power that the Google Chart API provides. You can also simply wrap the PocketQuery macro in a Confluence Chart macro and the default Confluence charts will be shown. In addition, we are currently working on the integration of the NVD3 library.
• The named parameters of your query can be dynamically passed to the macro as GET parameters in the URL. There is now also an option that produces a form in the macro HTML by which users can run the macro with different values as parameters.
• Results of queries can be indexed (if configured for your query). There is a job that executes all queries in a specified time interval and writes all content to the Confluence index. Afterwards, you can search the contents of your database that were queried by PocketQuery in your Confluence pages.

I think these are the most important features. For further reading, we have a documentation and a few articles on our website:

• Why PocketQuery?
• Documentation
• Article on templating in PocketQuery (very new!)
• Articles on Release 1.13

Let me know if you need further information.

Regards, Felix