Dynamic query using Jira Issue Macro

Gualter Augusto February 13, 2019

Hello,

I am currently trying to create a Report Page template which has multiple tables with counter values.
These counters are number of issues that follow a specific JQL.

However, I want to create weekly reports that perform JQL queries depending on the week at hand:

start_date = YYYY-MM-DD HH-MM-SS
end_date = YYYY-MM-DD HH-MM-SS

When I use Jira Issues Macro to generate the JQL query I am able to set a specific start_date and end_date and it will render accordingly.
However since this is meant to be a template, I want these start and end date to be dynamic and set it in a field somewhere at the top of the page. Something like this:

-------------------------------------

start_date = YYYY-MM-DD HH-MM-SS
end_date = YYYY-MM-DD HH-MM-SS

TABLE

number of issues | Created | Resolved
11 Issues | 5 issues | 4 issues

-------------------------------------

This info will then vary depending on the week and the queries need to have the start_date and end_date depending on the variable in the Page.

How can I achieve this?

I have also tried to create a macro similiar to the Jira issues macro but I haven't been able to.

Thanks

1 answer

1 vote
Diego
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
February 14, 2019

Hello there Gualter! Thanks for sharing with the Community!

Yes, you can create this week timespan by referencing the creation date of the current content:

## @noparams

start_date $action.dateFormatter.formatGivenString("yyyy-MM-dd", $content.getCreationDate()) <br>

#set ($date=$action.dateFormatter.calendar)
$date.setTime($content.getCreationDate())
$date.add(6, 7)

end_date $action.dateFormatter.formatGivenString("yyyy-MM-dd", $date.time) <br>


We use dateFormatter to have the date displayed as intended (yyyy-MM-dd). With date.add(6, 7) we add seven days to the creationDate value, which gives us one week after the creation date.

We can set the macro as No macro body. The <br> tags are line breaks so each date has it's own line.

You can check more about the add() method here, under Field Manipulation

Class Calendar - Field Manipulation

Hope this helps you out Gualter!

Gualter Augusto February 15, 2019

Hello,

Thank you for the reply!

I took a different approach here.

I added two variables:

  • $startDate and $endDate

Which I ended up using on a new macro. The new macro was based on the existing source code from the "Jira Issue" macro, slightly changed so that the jqlQuery parameters reads from the body like this:

 <ac:parameter ac:name="jqlQuery">$body<ac:parameter>
<ac:parameter ac:count="true"><ac:parameter>

body is my whole query that with the custom fields:

 project = X and issueType = Y and created > '$startDate' and created < '$endDate'

This way I am able to return a JIRA Issue count macro, adjusted to have the custom start and end date.

 

I followed this guide:

https://community.atlassian.com/t5/Confluence-questions/Can-I-use-a-variable-with-a-macro-I-am-trying-to-create-a-custom/qaq-p/203566

 

Cheers,

Diego
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
February 15, 2019

Great to know that you were able to find assistance within the Community!

If you ever need assistance again, come share with us.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events