SQLite Report help

[riccardo.zuppani_1]

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[/code]

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 ticketswhere(created_at >= date('now','start of month','-1 month')AND created_at < date('now','start of month','-1 day'))group by category[/code]

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

select category, count(id)from ticketswhere(closed_at >= date('now','start of month','-1 month')AND closed_at < date('now','start of month','-1 day'))group by category[/code]

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