How to perform calculations in confluence using Jira issues count?

[Manupriya S]

Hi,

In confluence, I want to create a table which performs certain calculations, such as: adding the two different Jira issues count. 

suppose:

I have total open issues count = 50 issues {got using the Jira Macro}

Another value that i have is, total closed = 40 issues {got using the Jira Macro}

Now i would like to perform sum on the above two Jira issues macros such that my result is:

TOTAL ISSUES = 50(Total open) +40(total closed)   

Total issues= 90

So i need to build-in something dynamic - a table which fetches the values from the Jira (using a query to look for particular issues result) and perform calculations on it.

Please help out. 

Happy to use coding and gadgets/plugins as well if possible to perform this.

Thank You

[DPKJ · Accepted solution]

@Manupriya S

You have four possibilities here,

1. Statistics gadget,
2. Using User Defined Macro
3. HTML Macro and
4. Using plugin at back-end.

Statistics gadget,

• Go to Jira and configure statics gadget (as things you want to see on confluence)
• You can find this on dashboard widget sections
• Now in confluence admin locate `External Gadget`
• Go to this page, and then to Gadget Feed tab
• Add your Jira base url here
• Now go to any confluence page in edit mode
• Go Other macro,  and search for `statistics`.
• Add this to page.
• More here - https://community.atlassian.com/t5/Confluence-questions/How-to-insert-the-JIRA-quot-Issue-Statistics-quot-chart-in-a/qaq-p/145074

This will have some short comings, like you not see added results.

User Defined Macro/HTML Macro

• You need to call Jira rest api and get appropriate results and display them as you like
• This will require adding Javascript to your macro content,
• Run the following REST query on the Confluence server to obtain JIRA's UUID (universal unique identifier):
  

  <Confluence URL>/rest/jiraanywhere/1.0/servers

  Which returns:

  [
    {
      id: "<UUID>",
      name: "JIRA",
      selected: true,
      url: "<JIRA URL>"
    }
  ]

• Copy the "id" value, this is the UUID.
• Plug that value from #2 into a standard ajax call using jQuery:
  

  jQuery.ajax({
    type: "GET",
    url: "<Confluence URL>/plugins/servlet/applinks/proxy?appId=<UUID>&path=<JIRA URL>/rest/api/latest/search/jql=<YOUR_JQL>",
    dataType: "json",
    async: false,
    success: function( data ) {
        //process data and render it in html tags
    }
  });

Back-end Plugin

• In this approach you can create Macro plugin
• call jira rest api using App-Links
• and render results
• This will require knowledge of plugin development and Java.
• Useful links
  • Macro Module - https://developer.atlassian.com/server/confluence/macro-module/

[Manupriya S]

Hi @DPKJ , Thank You very much for helping out and providing elaborations. 
I need some more help from you - can you please confirm in the above JQuery ajax code within the brackets "<YOUR_JQL>"  : so is this the Jira filter query or the java query?

[Manupriya S]

Also, just to be sure, we are writing down this JQuery - within the HTML macro in the confluence?

[DPKJ]

JQL implies filter query that you use in Jira. Reference here - https://confluence.atlassian.com/jirasoftwareserver/advanced-searching-939938733.html

Make sure pass it in url encoded form as it contains spaces etc.

[Manupriya S]

Hi @DPKJ  

So you mean I should have the URL link of the jira query pasted within :

<YOUR_JQL>

[Manupriya S]

I will explain what I did:
In Confluence- I added the HTML Macro and pasted the code you provided above:

jQuery.ajax({
  type: "GET",
  url: "<Confluence URL>/plugins/servlet/applinks/proxy?appId=<UUID>&path=<JIRA URL>/rest/api/latest/search/jql=<YOUR_JQL>",
  dataType: "json",
  async: false,
  success: function( data ) {
      //process data and render it in html tags
  }
});

Also made changes on the code by adding the values where required(Confluence URL, Jira URL and UUID) and also added the URL link of the Jira query filter that I have. 

But I am not getting any result. Its simply the code showing up on the screen.

If you could help out much appreciated. Many Thanks

[DPKJ]

So if you want to search for issues in particular project (whose key is AA), we write this in Jira JQL Text box (i.e in advance search)

project = AA

 Now if you are using API you will replace <YOUR_JQL> with url encoded above query, i.e.

project%3DAA

<YOUR_JQL> is just an place holder that you have to replace with valid URL encoded JQL query that you use in Jira.

Here is reference guide for JQL  https://confluence.atlassian.com/jirasoftwareserver/advanced-searching-939938733.html

[DPKJ]

@Manupriya S  you have to wrap JavaScript inside script tag in macro.

[Manupriya S]

okay can i please bother you to ask how do you convert into a url embedded code?
I have a very long query i use the advanced search for Jira queries. 

[DPKJ]

@Manupriya Syou either use this built-in JavaScript function https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/encodeURI

Or when do you do an advance search, check your browser address bar, the part after 'jql=' is encoded JQL Query.

[Manupriya S]

Okay Thanks a lot the seconf option worked! :) 
But now i am getting an empty screen with no result on it but it should display me 47 issues count. 

[DPKJ]

Once you are getting response, how are you processing it? You have to create one HTML element with and you have to put count in that html element using JavaScript inside callback function.

Can you share HTML Macro code? So that I can verify.

[Manupriya S]

Hi @DPKJ  I will share in a while. Thanks for helping.

[Priya N]

Hi @DPKJ  while your answer worked to fetch details in my 

Jira v8.5.9 and Confluence 7.4.5 While i try the same GET url in the 

Confluence 6.13.8 and Jira V8.5.9 it give me page not found error .

What am i doing wrong 

<Confluence URL>/plugins/servlet/applinks/proxy?appId=<UUID>&path=<JIRA URL>/rest/api/latest/search/jql=<YOUR_JQL>

Could you please let me know how to form the URL for my confleunce version  

[DPKJ]

I need to find this.

[Priya N]

So after a lot of trial and erros if I don't use the JIRA url encoded it works. Just jql encoded 

[Fernando Guigou]

Many thanks for the point about the  User Defined Macro/HTML Macro

I have used it successfully to retrieve information from Jira. 

However, now I am trying to update Jira. In particular, to transition an issue from one state to another. I have tried this, without success. Apparently, way of interacting does not support POST requests. Any suggestion?

 AJS.$.ajax({ 
               url: '/plugins/servlet/applinks/proxy?appId=<appId>&path=/rest/api/2/issue/ISSUE-001/transitions', 
               dataType: "json", 
               type: "POST", 
               data: ({ "transition" : { "id" : "11" } }),   
               context: document.body })
            .done( ...