I am wanting to show query results in a confluence page and am not sure how to go about it.
Confluence doesn't have anything that can read a database built into it.
Reading a JIRA database directly is usually the worst option for reporting on JIRA data.
Confluence does have the JIRA macros for reading JIRA data. I'd strongly recommend using those.
Well, I realize that normally it is not good to report this way, however, I am trying to pull worklog information and have not found a great way to do that other than directly from the database as I do not know how to use the REST API in the same way to make this query where I sum up users time logged for the next 30, 60, and 90 days.
SELECT u.display_name Associate , SUM(CASE WHEN w.startdate BETWEEN NOW() AND NOW() + INTERVAL 30 DAY THEN w.timeworked END/3600) '30 Days' , SUM(CASE WHEN w.startdate BETWEEN NOW() AND NOW() + INTERVAL 60 DAY THEN w.timeworked END/3600) '60 Days' , SUM(CASE WHEN w.startdate BETWEEN NOW() AND NOW() + INTERVAL 90 DAY THEN w.timeworked END/3600) '90 Days' FROM worklog w JOIN cwd_user u ON u.user_name = w.author JOIN cwd_membership m ON m.directory_id = u.directory_id AND m.lower_child_name = u.lower_user_name WHERE m.membership_type = 'GROUP_USER' AND m.lower_parent_name = 'atl_servicedesk_it_agents' AND w.startdate BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 90 DAY) GROUP BY u. display_name ORDER BY u.last_name;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.