This content has been marked as final. Show 5 replies
1 of 1 people found this helpful
The easiest way to see the relationships is to open the sample Crystal Reports. The reports themselves are an ok start to CR but the true jewel of the reports is that it shows the linking between tables. All linking is thru GUIDS, not the incident number or some other normal looking text so it takes a bit of getting used to but it works. Follow the reports and it all makes sense.
SELECT dbo.im_incident.im_id AS IncidentRef, dbo.im_incident_resolution.im_creation_date AS ResolveDateTime, dbo.tps_group.tps_title AS ResolvingGroup
FROM dbo.im_incident_resolution INNER JOIN
dbo.tps_group ON dbo.im_incident_resolution.im_create_group_guid = dbo.tps_group.tps_guid INNER JOIN
dbo.im_incident ON dbo.im_incident_resolution.im_incident_guid = dbo.im_incident.pm_guid
As far as the column and table, i think it would have to be the im_latest_assignment_grp_guid in the im_incident table. It is a guid so you would have to tie in the tps_group table to actually get the name.
The current_assignment is usually blanked out when tou resolve or close a ticket.
You can also get the same latest assignment from the pm_process table, but that table includes more than just incidents.
i agree with the crystal reports starting point. you can actually copy and paste the SQL code from crystal into sql management studio into a new sql view, and it will connect all the table relationships visually. save that, and refer to it.
from there, add tables you need 1 at a time, but be aware the auto relationships created when adding a new table are basically useless. Delete almost all the auto created relationships to the new table, and make them 1 at a time.
now that I think about it, there must be a master relationship diagram somewhere. I would love to print it on the 36" plotter we have in the office to refer to... I think I would need a bigger printer