By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,414 Members | 1,586 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,414 IT Pros & Developers. It's quick & easy.

Custom Report

P: 22
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
Share this Question
Share on Google+
4 Replies


deepuv04
Expert 100+
P: 227
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

P: 36
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

P: 22
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

P: 22
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

Post your reply

Sign in to post your reply or Sign up for a free account.