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_FilteredI ncident .createdon
CRMAF_FilteredI ncident .customeridname
CRMAF_FilteredI ncident .new_typeofsupp ortname
CRMAF_FilteredI ncident .owneridname
CRMAF_FilteredI ncident.respons ibleidcontactna me
CRMAF_FilteredI ncident .incidentid
FilteredService Appointment .new_avsoftware name,
FilteredService Appointment .new_updatesrec eivedname,
FilteredService Appointment .new_backuptest name,
FilteredService Appointment .new_cablinginf rastructurename ,
FilteredService Appointment .new_backuptest name,
FilteredService Appointment.new _serviceidname
FROM FilteredInciden t AS CRMAF_FilteredI ncident
INNER JOIN FilteredService Appointment as serviceappointm ent ON
serviceappointm ent.regardingob jectid = CRMAF_FilteredI ncident.inciden tid

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

[deepuv04]

Hi,
you wanted to a join on serviceidname equals Maintenance but in the given sql the join is on
serviceappointm ent.regardingob jectid = CRMAF_FilteredI ncident.inciden tid.

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

[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)

[Mark Braithwaite]

Hi

Thanks very much for your help. I tried INNER JOIN ON serviceappointm entregardingobj ectid = (CRM_FilteredIn cident.incident id and serviceappointm ent.serviceidna me = 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

[Mark Braithwaite]

Hi

Thanks very much for your help. I tried INNER JOIN ON serviceappointm entregardingobj ectid = (CRM_FilteredIn cident.incident id and serviceappointm ent.serviceidna me = 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