How to chart Jira issues in Confluence?

[Christopher Lohman]

We are looking to create a chart in Confluence, which uses Jira issues as a data source. Time spent by assignee on Jira issues, is a good example of the data that we're trying to chart. I can't seem to find any data on how to accomplish this. Can anyone direct me to some documentation?

[JamieA · Accepted solution]

Write a jira gadget: https://developer.atlassian.com/display/GADGETS/Packaging+your+Gadget+as+an+Atlassian+Plugin. Once written you can add it to any confluence page.

I would not use sql to access the jira database from confluence, although that's also possible...

Update: The reason why not is because if you write a gadget, you can reuse it in jira as well as confluence, and you don't need to do things that slightly worry me, such as registering the jira datasource in confluence. If you do do that you need to ensure you have the right macros (macro security plugin?) such that others cannot write arbitrary queries against the jira database, which would subvert jira security (perm schemes and issue security schemes).

Having said that, Bob's suggestion is probably easier and quicker than writing a gadget, so I'd definitely go with that for a proof of concept etc. Time series SQL queries on jira are pretty difficult, but time spent per dev should be straightforward enough.

[Christopher Lohman]

Thank you both for your responses. I am marking this as the answer merely because it is the pure Atlassian solution. I didn't mention it in the question but the intent of the question was to ask how I would produce the chart using native Atlassian tools.

With that said, it sounds like the 'real' answer is that there is no method, using native Confluence tools, to chart Jira issues. I could write my own plugin to accomplish this but if I do, that defeats the purpose of purchasing this tool.

I find myself realizing that this product is less of a confluence and more just a wiki with a plugin framework.

[Mathilde Herdemian]

Hi Christopher,

I have the exact same need as you and would like to know if you found a solution.

Thank you

Mathilde

[Bob Swift OSS (Bob Swift Atlassian Apps)]

I would have to disagree with Jamie - a rare occurance :). I would recommend defining the JIRA database as a datasource in Confluence (read only) and just construct the appropriate SQL query for each specific chart using the SQL Plugin for Confluence . Use the Cache Plugin for Confluence to reduce the server load. This may be one of Jamie's concerns :). The hard part is getting the SQL query right. For larger or more complex environments, a small datamart can be setup that extracts data from JIRA and other places to simplify queries and improve performance, but that is usually overkill especially to get started. How to use Confluence for Business Intelligence and Reporting talks about the scenario from a Confluence perspective. For JIRA database schema information there is https://developer.atlassian.com/display/JIRADEV/Database+Schema

[JamieA]

I updated my answer with my reasoning...

[Bob Swift OSS (Bob Swift Atlassian Apps)]

Ah, yes, that is a good thought to consider. Normally, you need to limit access to the query capability to trusted users who will only expose appropriate data. This can be done with Macro Security for Confluence at the JIRA datasource level if necessary. This is covered in #7 of the how to referenced above.

[Christopher Lohman]

Bob thanks for the comment. I have read the BI and Reporting document mentioned above. My feedback for you is that writing my own queries is not of particular interest to me. I would prefer to focus my time on my own products and pay for a tool that hooks these pieces together. I would prefer to avoid learning their database schema. The Jira Search Filters are as far as I would like to "dig".

This, therefore, leaves the opportunity open for someone such as yourself. Jira doesn't give me the ability to chart my own data. I expected Confluence would and that clearly isn't the case. What I would like to see from "someone" is a charting gadget that would allow me to use as a data source, the xml address from a Jira search. Of particular interest would be to chart those values contained in the various time summaries, sent by Jira.