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

[Asjadazeez]

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 ?

[Katerina Rudkovskaya [Stiltsoft]]

Hi ,

The Table Transformer macro belongs to our Table Filter and Charts for Confluence app, so maybe we’ll be able to play with other macros to help with the case.

If I understand your case correctly, you have the Jira Charts macro (the two-dimensional chart, in particular) wrapped into the Table Transformer macro for further transformation.
You want your users to select date ranges for the Jira Charts macro but not via the JQL not to ruin smth but via a kind of a date picker that will be “caught” on the fly by the macro.

If this is the case, I don’t think that it is possible to do directly.

But we can suggest using the following workaround: Jira Issues macro <- Table Filter macro <- Pivot Table macro <- Table Transformer or Chart from Table macros if required.
The Jira Issues macro will give us a tabular list of issues in general with date fields visible. The Table Filter macro will allow your users to set date range filters from the page view mode and leave only the needed issues. The Pivot Table will aggregate the filtered issues and serve as a replacement for the two-dimensional chart.

If such idea suits you, we can move to our support portal that is confidential. You may give us the screenshot of your Jira Issues macro in a published state (with visible headers) and a project of the aggregated table that you need to get. We’ll help you with all the settings.

[Asjadazeez]

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.

[Natalie Paramonova [Stiltsoft]]

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]

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.