Getting date value from the date picker to use in jira chart jql on a confluence page

Question

Hello All,

I have a confluence page, which displays tables of issues form jira.

The Jira chart is used to access jira with a jql, and nested in a table transformer.

There is a datepicker , which user can select date.

The date needs to be accessed from the jql to run the query.

Is there a way this can be done ?

Asjadazeez · Answer

I am infact using a table transformer.

Thanks this work around seems to be ok, but will require a lot of redesign of my KPIs because i am using over 43 jira charts(2d) nested in a Table Transformer. But again , i would have to go away with the table data created by those 2d jira charts.

Natalie Paramonova _Stiltsoft_ · Answer

Hi @Asjadazeez ,

Maybe the following workaround will suit you: use the Table Transformer macro instead of the Table Filter macro for filtering.

Wrap your Jira Issues macro with a general JQL in the Table Transformer macro and then in the Table Excerpt macro to reuse the filtered table. So, later you’ll be able to use the Table Excerpt Include macro several times to create different charts based on the same source table.

To filter your table with the help of the Table Transformer macro, create another manual table with your variable (in our case it will be month in the format coming from the Jira Issues table):

Then we cut “a month part” from the date and compare it with our variable:

SELECT T1.* FROM T1
WHERE T1.'Date'->split(" ")->0 = (SELECT T2.'Variable' FROM T2)

By changing the variable, you will change the filtered table and all the reused tables as well.

In the previous option with the Table Filter macro you may also use the Table Excerpt macro after the Table Filter macro: set the date range filter once and reuse the filtered table for all the charts concerning the specific month.

Asjadazeez · Answer

So, the idea is to show monthly statistics and KPIs , each confluence page will have the KPIs for a given month. Eg. October Page will have KPIs for October , So on..

So using a table filter didnt really  help me out.

I could have hard coded the dates for october in the jql, but i have over 30 charts, and i would need to change the date for each chart every month.

Is there a way i could declare a date\s as global variables(object) in that page, so that i can reference to that variable\object. that way i would need to change only 2 values for each month.