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:
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?