Jira search JQL using variables

[Scott Harman]

Hi guys

I'm trying to construct a page template for our Customer Information test page, and we use a custom field to tag all issues against a given customer -

Simply speaking, what I'd like to do when generating the customer info page, is to simply define that customfield value as a variable in the template then use that in JQL query as part of the template

Is this possible?

Cheers

Scott

[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.

[Mustafa Oeztuerk]

I have tried to follow you recommendation but I have problem in the latest step to put the JQL code in the body of the macro. I just get the following error. Any idea?

image2016-10-12 12:10:14.png

[Mustafa Oeztuerk]

The biggest question is what does it mean to inject (step 5) the varialbe of the header into the JQL. I get always the wrong reference to the variable already defined.

[Adam Barylak]

If you look at the screenshots for those steps, Step 5 shows the JQL as just text in the body of the macro.  Then, in Step 6, it says to inject the variable(s).  As you can see in the included screenshot the "7.2" text in Step 5 is replaced with a green lozenge for $fixVersion (which is the variable).  The way to inject the variable would just be to delete the 7.2 text, then start typing $ then the variable name and using inline suggestions to select your variable.

[Alex]

Hi Scott,

I believe it is possible to create a saved filter which you can use on the customer info page using a custom field variable. It should be as simple as using the Advanced Search to create the JQL query and then saving as a new filter.

See here for more information about Advanced Searching:

https://confluence.atlassian.com/display/JIRA/Advanced+Searching#AdvancedSearching-FunctionsReference

[Scott Harman]

Hi Alexandra,

What I really want to do is use the confluence variables themselves (like $CCGroup) as part of the JQL query i.e CC Group = $CCGroup which then executes the search for the customers http://myserver/secure/IssueNavigator.jspa?reset=true&jqlQuery=%22CC+Group%22+%3D+$MyCustomer

When I enter the variable in the JQL query window, it won't be accepted, but I can explicitly retype the CC Group field name - however this is what I want to avoid

I want to automatically present the customer's open incidents (visible to the customer when the access the page) and the customer's bugs (which are only accessible to staff and key accounts)

But what I want to avoid is having to get my users to hand craft each of these queries, or to generate an explicit filter for each customer and incident type

If we can just template it, then the idea will be -

Projects and Services group start filling in the page with the customer name and the customer's group name

Add the acceptance test criteria and drawings

Add data and drawings on workflows, and log the acceptance test past fail (as well as bugs) for the customer in Jira which they will have access to - but the confluence page will be the customer overview document

[John Dyer]

This would be really nice

[Christian Hattinger]

Hello John! Did you find a solution of how to use filters in Confluence which use custom field variables in the JQL query? Thanks, Christian

[John Dyer]

Christian, No, I never did figure it out -John

[Doug Swartz]

This can be done pretty simply using the Bob Swift Run Self-Service Reports for Confluence plugin run macro.

Define the parameters you want in your run macro and embed the JIRA Issues list in it. In the JIRA macro replace the JQL argument value with the Run Parameter name. For example:

Here is the Run macro with the embedded Issues : 

RunMacroExample.png

The JIRA Issue macro has this JQL: 

project = tc and reporter = '$selected_user '

Displays this form:

ConfluenceRunForm.png

And this result:

ConfluenceRunJiraList.png

[Doug Swartz]

The trick is to embed the argument in quotes. Otherwise the Jira macro wont let you save the $ preceded argument variable.

[Moritz Latzka]

is there another trick for projects and resolutiondate? both don't work and I don't have the option to save the query.

image2016-4-6 14:3:9.png

[Darren Pegg]

@Doug Swartz

works a treat... I could't save the filter but used the source editor instead and now I can select the version and it will load the issues related!

Awesome!!!!!

<p>
  <ac:structured-macro ac:name="run">
    <ac:parameter ac:name="replace">selected_version:Select Version:Version:Select::1.0::1.1::1.2::1.3</ac:parameter>
    <ac:parameter ac:name="id">A1</ac:parameter>
    <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
    <ac:rich-text-body>
      <ac:structured-macro ac:name="jira">
        <ac:parameter ac:name="columns">key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution</ac:parameter>
        <ac:parameter ac:name="server">Base Url - Populated By The Application Link</ac:parameter>
        <ac:parameter ac:name="serverId">Populated By The Application Link</ac:parameter>
        <ac:parameter ac:name="jqlQuery">project = TEST AND fixVersion = "$selected_version" </ac:parameter>
        <ac:parameter ac:name="maximumIssues">20</ac:parameter>
      </ac:structured-macro>
    </ac:rich-text-body>
  </ac:structured-macro> </p>
<p> </p>
<p> </p>

[EmersonP]

Hi Guys,

Somebody figured it out? Even in another way ?

Regards,

-Emerson 

[Alex]

Hi Scott,

I believe it is possible to create a saved filter which you can use on the customer info page using a custom field variable. It should be as simple as using the Advanced Search to create the JQL query and then saving as a new filter.

See here for more information about Advanced Searching:

https://confluence.atlassian.com/display/JIRA/Advanced+Searching#AdvancedSearching-FunctionsReference

[Joris Hilhorst]

This solution did not fix the issue, as far as I could tell. -1 for customer support.