Using template variables as input parameters to jira gadget

[Therese Lindepil]

Hi,

I would like to add a form as a template in Confluence.

The input of the form should be used as input parameters to a jira gadget.

Is this possible?

Best regards,
Therese

[John Burns · Accepted solution]

I achieved this by creating a user macro as a wrapper for the jira gadget i wished to use as such:

## Macro name: version-issues
## @param Project:title=project|type=string|required=true|desc=Project Key 
## @param Version:title=version|type=string|required=true|desc=Fix Version
  <ac:macro ac:name="jiraissues">
    <ac:parameter ac:name="title">All Resolved Issues</ac:parameter>
    <ac:parameter ac:name="renderMode">static</ac:parameter>
    <ac:parameter ac:name="columns">type,key,summary,reporter,resolution,component</ac:parameter>
    <ac:parameter ac:name="url">http://jiraurl:8080/sr/jira.issueviews:searchrequest-rss/temp/SearchRequest.xml?jqlQuery=project+%3D+%22$paramProject%22+AND+fixVersion+%3D+%22$paramVersion%22</ac:parameter>
  </ac:macro>

This example creates a jira issues list for a given project and fixversion

Then you can put this macro in your template.

[John Burns]

I'd like to note that I haven't figured out a way to pass a template parameter into the macro parameters.

[Therese Lindepil]

Thank you for answering that fast :-)

So are the project and fix version added by the end user in a confluence form?

Are the parameters confluence variables?

[John Burns]

They will be parameters in the macro. As I said above, I still don't know how to have the user enter them as template-level variables. The user needs to edit the macro once the templates generates the page, but at least it simplifies filling in the parameters of the macro. They will be presented with a macro edit screen with just those parameters.

[Tarun Sapra]

Instead of URL I would rather use the JQL query directly

<ac:parameter ac:name="jqlQuery">project = CONF AND FixVersion=5.8 </ac:parameter>

[Sascha Ziemann]

The example has been completely destroyed by the forum.

[Adam Barylak]

I figured since we solved this recently, i would add our official solution to this request which allows to inject any number of variables into any JQL filter in a Confluence Template.  Below is the how-to as well as some usage notes i wrote for our company:

First we start out with determining the current syntax of the JQL Issues/Filter macro in confluence:

1. Install or Enabled the “Confluence Source Editor” plugin
2. Create new page
3. Add the JIRA Issue/Filter macro
4. Use a simple basic query
5. Click the “<>” button to view the source of the page.

6. Copy the JIRA Issue/Filter macro syntax to a text editor, e.g.

   <p>
     <ac:structured-macro ac:macro-id="5a45704b-a904-4440-bdc0-da68a22d5802" ac:name="jira" ac:schema-version="1">
       <ac:parameter ac:name="server">JIRA</ac:parameter>
       <ac:parameter ac:name="columns">key,summary,type,created</ac:parameter>
       <ac:parameter ac:name="maximumIssues">20</ac:parameter>
       <ac:parameter ac:name="jqlQuery">assignee = abarylak and resolution is EMPTY </ac:parameter>
       <ac:parameter ac:name="serverId">c8001771-0b52-30e0-9703-562a2820710f</ac:parameter>
     </ac:structured-macro>
   </p>

Then we need to use the above syntax (a little modified) and use that for our macro.

1. Create a macro with 2 parameters: maxRows, columns
2. Remove the macro-id from the above syntax
3. Modify the $body variable to remove unnecessary white space using trim() and html paragraph tags (see below)
4. Inject the $body into the jqlQuery element in the above syntax
5. NOTE: The “serverId” parameter may change server to server, so you must get that ID from your current server you want to deploy this to via the source editor plugin.

6. Results are as follows:

   ## Macro title: JQL Filter Allowing Variables
   ## Macro has a body: Y
   ## Body processing: Rendered
   ## Output: JIRA Issues/Filter Macro w/ injected variables
   ##
   ## Developed by: Adam Barylak
   ## Date created: 2016-05-27
   ## Installed by: Adam Barylak
   
   ## This macro will prompt for max number of rows and the column option, then the body will require the JQL with injected variables
   ## @param maxRows:title=Max Rows|type=int|required=true|default=20|desc=Enter the max rows, anything above 1000 will only display 1000 rows.
   ## @param columns:title=Columns|type=string|required=true|default=key,summary,type,status,assignee|desc=Enter the columns you want returned.  Must be separated by commas, all lowercase, and no quotes. e.g. key,summary,assignee,test author,reporter 
   #set ($body = $body.replaceAll("\Q<p>\E",""))
   #set ($body = $body.replaceAll("\Q</p>\E",""))
   #set ($body = $body.replaceAll("\Q<span>\E",""))
   #set ($body = $body.replaceAll("\Q</span>\E",""))
   #set ($body = $body.trim())
   <p>
   <ac:structured-macro ac:name="jira" ac:schema-version="1">
       <ac:parameter ac:name="server">JIRA</ac:parameter>
       <ac:parameter ac:name="columns">$paramcolumns</ac:parameter>
       <ac:parameter ac:name="maximumIssues">$parammaxRows</ac:parameter>
       <ac:parameter ac:name="jqlQuery">$body</ac:parameter>
       <ac:parameter ac:name="serverId">c8001771-0b52-30e0-9703-562a2820710f</ac:parameter>
   </ac:structured-macro>
   </p>

Create the macro in confluence with the following options or similar (change as needed):

• Macro Name: jira_issue_filter_vars
• Macro Title: JQL Filter Allowing Variables
• Description: Insert entire JQL query in body of macro.  Inject template variables as needed.  If quotes needed around variable, insert variable first, then surround variable with quotes.
• Categories: Reporting
• Macro Body Processing: Rendered

How to use JQL Filter Macro in Template with Variables:

1. Get your query working in JIRA in Advanced mode (text based) with your filter(s) for a single run.
2. Copy advanced JQL text to notepad or clipboard.
   1. Notes for format:
      1.  Do not include any line breaks in the JQL
      2. Ensure all whitespaces are spaces and not tabs or other characters
      3. Ensure all special characters such as parenthesis or quotes are basic text (not the special characters sometimes used by MS Word or MS Outlook)
      4. If all else fails, manually re-type the JQL using temporary placeholders for the variables, then using the “$” keyboard command to place the variables into the JQL statement where necessary
3. Create template in Confluence with any header level variables needed. (you can create variables with the gui, but using them later requires you to type $ then the variable name or copying and pasting.  If you use the UI again, it will add an additional $ sign to the variable therefore making it a new variable and it won’t work.)
4. Add the “JQL Filter Allowing Variables” macro with appropriate options. (it is located in the Reporting category)
   1. Max Rows: only allows a max of 1000
   2. Columns: you may need to use a normal JIRA Issue macro and configure it with your specific columns, then view the source to determine the correct name of some fields.  However, the rule so far is that there are no spaces except what is in the field name, and all fields are lowercase, and not surrounded by quotes.  Examples of some fields: key, summary,related requirement/s,test case type,test author
5. In the body of the macro, paste in your entire JQL query from step 2
   
6. Inject your variable or variables into the JQL.
   
7. If you need to include quotes around variable to support spaces in variable text, add them after injecting the variable(s) into the query.
   
8. Using this method you can inject multiple variables if needed.
   
9. Save your template and use as needed.

Hopefully this helps others accomplish this same task.  This ends up being really useful if you need to repeat a bunch of JQL filters for different things on different pages in Confluence and you don't have to store these filters as saved filters in JIRA either.

[Kim Poulsen]

Adam, absolutely excellent write-up and solution. It works straight out of the box, once the "serverId" line is exchanged with the one for the local server.

 If I had 10 points to give you would get them.

Edit:

I have edited Adam's answer above to include removing <span></span> tags as well. I found that a body like this:

     project=TO and sprint in ("Sprint 1", "Sprint 2")

ended up as

     project=TO and <span> sprint in ("Sprint 1/1", "Sprint 1/2") </span>

That obviously does not compute. Removing the <span> does the trick.

[Adam Barylak]

Thanks for the update.  When it injects those tags is probably one of the reasons we sometimes just have to re-type the entire jql string by hand and why I put the "If all else fails" section in the guide for our users.  I just didn't have the time to investigate further.  I will be adding that to my macro as well.  Thanks for looking into that.

[Nastja K]

Hi Adam,

Thank you so much for the solution.

I have one more question: How to display JIRA link on the top of Confluence page as it is displayed when using original JIRA issue macro?

shot_109.bmp

I have transformed your macro a little bit and that's what I got:

<ac:structured-macro ac:name="jiraissues" ac:schema-version="1">
<ac:parameter ac:name="server">JIRA</ac:parameter>
<ac:parameter ac:name="count">false</ac:parameter>
<ac:parameter ac:name="maximumIssues">1</ac:parameter>
<ac:parameter ac:name="serverId">JIRA_server_ID</ac:parameter>
<ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
<ac:parameter ac:name="key">$body</ac:parameter>
</ac:structured-macro>

And I can't see the link to JInk to JIRA on a top of the page(

Thank you in advance.

[Adam Barylak]

@Nastja Kozlova, The "1 JIRA Link" at the top of the page is only applicable when using an actual web link from the JIRA issue into Confluence.  This is done when you include a single JIRA issue in the Confluence page as well.  However, this is not the same as displaying a filter result in Confluence.  The JIRA Issues Macro is displaying the results of a JIRA Issue Filter, not displaying a single JIRA issue.

If you want the Link thing at the top of the page, you cannot use a JIRA filter.

[Brian W. Bishop]

Is there a way to specify a custom field in the columns list?

[Brian W. Bishop]

nevermind - figured it out: custom field name = "Release Notes Comments" then add the column as 'release notes comments' as the column

[Brian W. Bishop]

is there a way to suppress the auto-hyperlinking of the jira issue key? or a way to pass in columns that could build the PROJKEY + "_" + 123 string that represents the issue key without a link?

[shira segal inbar]

@Brian W. Bishop

Hi Brian,

Can you elaborate on how to add custom field? Where do you add the custom field name = "Release Notes Comments"?  

Edit- never mind, I succeeded

Thanks,

Shira

[Monan Yao]

Thank you so much! 

Very useful user macro for release management! 

[Leonard Chew]

Thanks loads, @Adam Barylak

Very nice step-by-step description! - It worked perfectly.

While I was at it, I modified the macro with a checkbox to let the JQL be displayed in an info-box under the result table.
Most of our users are developers and they like to know on which basis the data is selected without having to edit the page.

## @param displayJQL:title=Display JQL|type=boolean|required=true|default=false|desc=Displays the JQL in an Info Box under the Result Table

#if ($paramdisplayJQL=="true")
<ac:structured-macro ac:macro-id="94f6dacf-19a6-4679-ab0b-4052266a6b47" ac:name="info" ac:schema-version="1">
<ac:rich-text-body>
<p>$body</p>
</ac:rich-text-body>
</ac:structured-macro>
<br/>
#end

[Mukti Prabhakar]

Is there a solution to this problem with onDemand version of Confluence?

[Matthew Davison]

Any solutions for a JIRA pie chart instead of issue filter?

[Eric Monfette]

Any way to display total issue count for each query for this macro?

[Michael Wohlgemuth]

You, sir, are an absolute legend! Thank you so much @Adam Barylak - this makro gives me the option to remove a whole self-written plugin!

[Jean-Christophe Gonzales]

With this perfect procedure I was able to put in place what I wanted on my page template. The variable is entrered once at the time of the page rendering and I get Jira lists I needed. Thanks for that.

I also tried to use this method to establish the link both ways between the Confluence page and the Jira issue. It works fine from Confluence to access the Jira Issue, but no way to generate the link type "Mentioned in" on the jira Issue to reach the confluence page from Jira.

Obviously it works fine when the issue key is entered manually in the Jira macro, I get the "mentioned in" link type on the Jira issue and the link works fine. What I need is the same facility, but using a variable, 

I look forward to a good soul to provide a solution 

Thanks in advance

[Igor DeFaye]

Is there a true solution when the admin group doesn't want to support user macros at all ? I feel this hasn't been answered properly yet. It's a great workaround, but not useful for big groups that are very restrictive.

[Rachael Garbee]

Is this restricted to server instance or can this be done on cloud?

[Andre Schlegel-Tylla]

The "Confluence Source Editor" isn't available for Cloud :-(

[Eric Monfette]

Is there a way to convert the output of this macro to a string or integer? Example: turn "86 issues" link into just "86". Would make this macro very useful for charts.

[Eric Monfette]

This is very helpful. Is there a way to display the total issue count of the query through this macro? Any help would be great.

[Kim Poulsen]

If you search this page for  "count", that should to the trick:

<ac:parameter ac:name="count">true</ac:parameter>

[B Daniel Fairchild]

This was really helpful for me. I was able to write a JIRA issues wrapper that just took in the fixVersion for the $body. I finally figured out how to have the fixVersion be a variable passed to all the macros on the page (I have several) that rely on it.

I had to change the Macro Body Processing to Rendered and have @noparams set. So when I created the template and added the macro, I could add the $fixVersion variable to the macro in the template. The $fixVersion is set at the top of my page and once set, all the macros are updated with that data. Thus, the $fixVersion becomes the $body in the macro.

So I guess if you only have 2 params, if you just pass them into the body, you should be able to parse them from $body and assign them to the paramX variables in your macro. I haven't seen any other way to pass variables to the params in the pop-up dialog box.

[Ross Gustafson]

Roxanne can you give a sample of what you did with the $body to make it work? Do you have a code sample. I'm fairly new to Velocity, Atlassian macros and the such. Did you just put the $fixVersion directly inside the text block of the macro? Did you just use $body directly in your JIRA search query?

[Kim Poulsen]

I have reconstructed this somewhat - for whom might be interested in this.

The User macro will contain the storage representation of all the macros you want to render with the variable.

Macroname: applytemplatevar
Type: rendered
Template:

## @noparams
<ac:structured-macro ac:name="panel">
 <ac:parameter ac:name="bgColor">#72bc72</ac:parameter>
 <ac:parameter ac:name="titleBGColor">#00a400</ac:parameter>
 <ac:parameter ac:name="title">$body</ac:parameter>
 <ac:parameter ac:name="borderStyle">dashed</ac:parameter>
 <ac:parameter ac:name="borderColor">blue</ac:parameter>
 <ac:parameter ac:name="titleColor">white</ac:parameter>
 <ac:rich-text-body>
   <p>A formatted panel</p>
 </ac:rich-text-body>
</ac:structured-macro>

Design a template with a single variable - you can call it whatever you like. insert the macro above and put the variable inside the body as well. You should now have the variable both at the top of the page (or at least outside the macro too) AND inside the body of the applytemplatevar macro.

Now create page using this template. You will be prompted with entering the variable as usual. The create page screen will complain about broken macros because the simple example above does not have a value for the body initially. Preview the page and see that the panel shows with the title entered. Now what I am wondering is:

1) how to get more variables
2) how to apply this to macros entered on the edit page, so any macro can be used.

(Answer edited to format nicely)