SQLite Report help

0

hi everyone, I just started using jasper software.  I need to know how to generate an automated report. I started with jasper studio.

i have connect my help desk sqllite db to jasper studio and i have created a new blank report.

i have the datasource filter with thios query: 

SELECT * FROM tickets

and i have as a result this field:

id
summary
status
description
priority
created_at
updated_at
closed_at
created_by
assigned_to
viewed_at
reopened
requires_purchase
category
external_id
email_message_id
status_updated_at
warning_alert_count
error_alert_count
muted
master_ticket_id
reported_by_id
due_at
remote_id
synced_at
sharer_id
parent_id
billing_rate
first_response_secs

the category field contains these values: Users Support, systems management.

ID field is the primary key.

my goal is generate a report like this:

a field with this sql query (numer of tickets opened last month divided by category):

SELECT category, COUNT(id)
FROM tickets
WHERE(created_at >= DATE('now','start of month','-1 month')
AND created_at < DATE('now','start of month','-1 day'))
GROUP BY category

in the same report i would other field like (numer of tickets closed last month divided by category):

SELECT category, COUNT(id)
FROM tickets
WHERE(closed_at >= DATE('now','start of month','-1 month')
AND closed_at < DATE('now','start of month','-1 day'))
GROUP BY category

and after i want moltiplicate the result of this 2 fields and insert in other field.

is it possibile this in the same report?

and is it possible schedule the generation of this report every month automatically?

thanks

riccardo.zuppani_1's picture
Joined: May 14 2019 - 4:26am
Last seen: 5 days 9 hours ago

0 Answers:

No answers yet
Feedback