I'm not sure if I've mis-understood the requirement, but it sounds to me as if you need to add a formula first which says that if the incident is resolved and closed at the same time to return the value of 1. Something like:
if pm_process.pm_creation_date = im_incident_closure.im_creation_date then 1 else 0
then you can do a sum function for your cross tab report on this field. Does this do the trick?
Yes and No, I can see the logic in the function you have described, but it would only cover incidents that have been opened and resolved on the same day, if an incident was taken on a monday and resolved on tuesday the variable would show as 0 because the date wasn't the same.
what I'm thinking is maybe having 2 subreports one looking at opened and another looking at resolved, setting up a formula field for the total summary on each report rather than the normal auto total, and then passing these two formula fields to the main report so they can exist in the same cross tab at the same time and you could then have a bar chart showing both. does that sound like it's possible?
What will be the Row & Columns in your cross tab report?
Nothing too complicated, just Open and Resolved for Rows and a total for each in Columns.
Still a little unclear. Do you want just the one column or do you need a total for each month or something?
Jan Feb Mar Apr May Total Opened 100 95 88 103 90 476 Resolved 65 71 62 71 68 337
The picture of the report in your reply is exactly what i'm looking for, i'd given up hope of getting the date column on the report so was trying for just the total.
There's probably a neater way but you can create a Union query like this:
SELECT TOP (100) PERCENT pm_creation_date, 'Open' AS Marker
WHERE (pm_class_type_guid = '7bfd587a-1e80-4aa6-ac18-2fe1c49dcdb5')
SELECT im_creation_date, 'Resolved' AS Marker
You can then create a Crystal Cross Tab Report based on this View.
Columns is creation date grouped by month.
Rows is 'Marker'
I'm assumming the Guid for Incident class type is the same.
Thanks for that, I'd looked up Union linking the tables but didn't quite get it. I'll create a view with the code and see what comes out.
I have created a few reports that I needed to do this for to show figures for open/closed/resolved within date ranges. I did them in different ways:
- Create a view using a union's and report on that.
- Use sub reports, I have a few reports that use sub reports to get different data sets.