Dashboard control value to filter by the previous hour, day or week

Question

I want to create a Dashboard control dropdown where the user can select from Hour, Day or Week.

That selection should then be used to filter the data to be data from the last Hour, Day or Month.

Let's say the Control is called TimePeriod, then the WHERE clause would say something like

WHERE resolution_at > {TimePeriod}

In reality I want to filter to the previous Calendar month - the SQL would be slightly more complex but the idea is the same.   I could not get Atlassian Analytics to use the Control to accept the variable name as I tried to above.

Is what I am trying to do even possible?   Thanks in advance.

Joanna Waligora · Answer

You can create a control with this sql:

select 'HOUR' AS `Time Range`
union ALL
select 'DAY' AS `Time Range`
union ALL
select 'MONTH' AS `Time Range`

Let's assume you call it "TIME_RANGE".

Then in your chart, whatever it may be, you can reference it in the 'where' clause.

Example:

SELECT 
    i.issue_id AS ticket_id,
    i.issue_key AS ticket_key

FROM 
    jira_issue i

WHERE

CASE
        WHEN {TIME_RANGE} = 'HOUR' THEN i.created_at > current_timestamp() - INTERVAL 1 HOUR
        WHEN {TIME_RANGE} = 'DAY' THEN i.created_at > current_timestamp() - INTERVAL 1 DAY
        WHEN {TIME_RANGE} = 'MONTH' THEN i.created_at > current_timestamp() - INTERVAL 1 MONTH
        ELSE i.created_at > current_timestamp() - INTERVAL 1 DAY
    END

The 'Else' is just a default in case user 'clears' the selection.

I feel like there should be a way to integrate the variable directly into the 'interval' statement thereby eliminating the need for the case statement, but I ran into syntax issues when testing, so this is the next best thing.

Marlene Kegel - codefortynine · Answer

Hello @Jes Kampman,

I am Marlene, product manager of Quick Filters for Jira Dashboards.

As mentioned before, standard Jira dashboards don't have the possibility to dynamically filter other gadgets. But it can be done with a Marketplace app, for example our app Quick Filters for Jira Dashboards.

Our app provides a Quick Controller gadget that interacts with all other Quick Gadgets on your dashboard. In that controller you can configure a huge variety of filters using JQL.

It's also possible to create dropdown filters for date fields to have a more dynamic possibility in filtering for dates.

Rodney Dsouza _Atlassian Certified Expert_ · Answer

Thanks David but I need it in a visual chart since this is reporting that gets presented to management.

I don't think regular/traditional Jira Dashboards have control gadgets, maybe some plugins do.

We don't have any reporting plugins, but do have Atlassian Analytics and so looking for an answer in that feature.   Atlassian Analytics do have controls and so I was looking for some kind of 'trick' that could work for my use-case.

Thanks.