Jump to content
Changes to the Jaspersoft community edition download ×

SQLite Report help


Recommended Posts

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

 

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...