I am looking to create a view in SQL that will cater for all modules in a monthly statement/report.
I have built some queries in the console which provide a lot of the information users need but I am struggling to decide what is the best method of reporting on all tickets from Crystal and if I need to keep them separate like the queries/dashbaords. Ideally I would like 1 report that could be run per customer/month that can provide everything they need.
The report I am currently working on is a monthly statement to list the following;
- Current Open
- Closed (Resolved) - This is where I am struggling, I want resolved/fulfilled, rejected etc (anything that has been dealt with)
- Breached or any other specific requirements I might get like that.
I can produce the above for Incidents using
|LEFT JOIN im_incident_resolution ir ON ir.im_incident_guid = p.pm_guid|
This provides me with a resolution date in the view so I can use that but I am not sure how I can bring in requests? We are looking at a new process that records a date in the process table for anything that is "dealt" with but this will need to go through CAB/QA etc I can't see me being the only one who wants to report on everything.
We are only just starting to roll out request now incident is setup but eventually we will add change, problem etc so it would be nice if I can have a "master view" and 1 monthly statement of activity regardless of the module.
FROM pm_process P
LEFT JOIN im_incident i ON i.pm_guid = p.pm_guid
LEFT JOIN rm_request r on r.pm_guid = p.pm_guid