Custom Report

Mark Braithwaite

Hi

I am trying to create a custom report of Microsoft CRM data in Visual Studio.

I am extracting data from two different tables, an incident table and a service appointment / activity table. We trying to create a service report which we can send to our customers at the end of each month.

My query is:

SELECT CRMAF_FilteredIncident .createdon
CRMAF_FilteredIncident .customeridname
CRMAF_FilteredIncident .new_typeofsupportname
CRMAF_FilteredIncident .owneridname
CRMAF_FilteredIncident.responsibleidcontactname
CRMAF_FilteredIncident .incidentid
FilteredServiceAppointment .new_avsoftwarename,
FilteredServiceAppointment .new_updatesreceivedname,
FilteredServiceAppointment .new_backuptestname,
FilteredServiceAppointment .new_cablinginfrastructurename,
FilteredServiceAppointment .new_backuptestname,
FilteredServiceAppointment.new_serviceidname
FROM FilteredIncident AS CRMAF_FilteredIncident
INNER JOIN FilteredServiceAppointment as serviceappointment ON
serviceappointment.regardingobjectid = CRMAF_FilteredIncident.incidentid

The tables are correctly joined except I am having two issues.

1. Data is now repeated for each case that has more than one service activity.

2. The second table which shows service activity information is repeated for
each service activity even the service activities do not contain the required data. I only want to display service activities where the serviceidname equals Maintenance. I need to show all cases though and not only the cases which relate to service activities which are associated with the service Maintenance.

Does anyone have an idea as to how I could streamline the presentation of my report? I would really appreciate any suggestions.

I assume that I may need to include a WHERE statement.

Thanks
Mark

Sep 26 '08 #1

deepuv04

Hi,
you wanted to a join on serviceidname equals Maintenance but in the given sql the join is on
serviceappointment.regardingobjectid = CRMAF_FilteredIncident.incidentid.

are you doing a join on the same columns.....?

If possible can you provide the table structure with some sample data and required output.

Thanks

Sep 26 '08 #2

getmeidea

For joins you can specify multiple join conditions between two tables
for eg:
select * from table1 inner join table2 on (table1.id = table2.id and table1.name = table2.name)

Sep 29 '08 #3

Mark Braithwaite

Hi

Thanks very much for your help. I tried INNER JOIN ON serviceappointmentregardingobjectid = (CRM_FilteredIncident.incidentid and serviceappointment.serviceidname = Maintenance) and the results returned only the cases associated with service activities that are associated with the service type Maintenance.

This is excellent. Thanks so much for the bit of guidance that I needed. I just have one problem with my report.

I need to display all cases and not only the cases associated with service activities associated with the service type Maintenance.

So my report should show all cases and only service activities associated with service type Maintenance.

Some I am half way to joining the tables correctly.Any suggestions would be greatly appreciated. I just need to make one last change.

Thanks again
Mark

Sep 29 '08 #4

Mark Braithwaite

Hi

Thanks very much for your help. I tried INNER JOIN ON serviceappointmentregardingobjectid = (CRM_FilteredIncident.incidentid and serviceappointment.serviceidname = Maintenance) and the results returned only the cases associated with service activities that are associated with the service type Maintenance.

This is excellent. Thanks so much for the bit of guidance that I needed. I just have one problem with my report.

I need to display all cases and not only the cases associated with service activities associated with the service type Maintenance.

So my report should show all cases and only service activities associated with service type Maintenance.

Some I am half way to joining the tables correctly.Any suggestions would be greatly appreciated. I just need to make one last change.

Thanks again
Mark

Hi

I tried Left OUTER JOIN and now all cases are returned however for some reason a service activity is created for each case even if the service type equals Maintenance.

I just need assistance with this last bit.

Thanks
Mark

Sep 29 '08 #5