Dynamic query using Jira Issue Macro

[Gualter Augusto]

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

[Diego]

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]

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]

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

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