locked
CRM 4 SQL Reporting services RRS feed

  • Question

  • Hi

     

    I'm using CRM 4 and am trying to create a custom report using visual studio 2005 with SQL reporting services. I want to create a list of activities within each month linked to contacts with contact information. I can get the link for the appointments but seem to get stuck when adding another entity - eg  phone call.

     

    This is the SQL code that was wroking for me

     

    SELECT     FilteredContact.fullname, FilteredAppointment.activityid, FilteredActivityPointer.activityid AS Expr1, FilteredActivityPointer.regardingobjectid,
                          FilteredAppointment.subject, FilteredAppointment.scheduledstart, FilteredAppointment.scheduledend,
                          FilteredContact.defaultpricelevelid, FilteredContact.jobtitle, FilteredContact.parentcustomeridname, FilteredContact.parentcustomerid,
                          FilteredAppointment.statuscode, FilteredAppointment.statecode, FilteredAppointment.statecodename, FilteredAppointment.statuscodename
    FROM         FilteredActivityPointer INNER JOIN
                          FilteredContact ON FilteredActivityPointer.regardingobjectid = FilteredContact.contactid INNER JOIN
                          FilteredAppointment ON FilteredActivityPointer.activityid = FilteredAppointment.activityid

     

     

    then I tried to add in the phone call table

     

    SELECT     FilteredContact.fullname, FilteredAppointment.activityid, FilteredActivityPointer.activityid AS Expr1, FilteredActivityPointer.regardingobjectid,
                          FilteredAppointment.subject, FilteredAppointment.scheduledstart, CONVERT(varchar, FilteredAppointment.scheduledstart, 6) AS Date,
                          CONVERT(varchar, FilteredAppointment.scheduledstart, 1) AS Date2, FilteredAppointment.scheduledend, FilteredContact.defaultpricelevelid,
                          FilteredContact.jobtitle, FilteredContact.parentcustomeridname, FilteredContact.parentcustomerid, FilteredAppointment.statuscode,
                          FilteredAppointment.statecode, FilteredAppointment.statecodename, FilteredAppointment.statuscodename, FilteredAppointment.ownerid,
                          FilteredAppointment.owneridname, FilteredPhoneCall.activityid AS Expr2, FilteredPhoneCall.subject AS Expr3
    FROM         FilteredActivityPointer INNER JOIN
                          FilteredContact ON FilteredActivityPointer.regardingobjectid = FilteredContact.contactid INNER JOIN
                          FilteredAppointment ON FilteredActivityPointer.activityid = FilteredAppointment.activityid INNER JOIN
                          FilteredPhoneCall ON FilteredActivityPointer.activityid = FilteredPhoneCall.activityid

     

    Does any one know where i am going wrong?

    Thursday, November 27, 2008 4:06 PM

Answers

  • Your problem is that you're using an INNER JOIN to both the appointment and phone call views, so the query will only return records that are both appointments and phone calls, hence no data.

     

    Try:

     

    SELECT     FilteredContact.fullname, FilteredAppointment.activityid, FilteredActivityPointer.activityid AS Expr1, FilteredActivityPointer.regardingobjectid,
                          FilteredAppointment.subject, FilteredAppointment.scheduledstart, CONVERT(varchar, FilteredAppointment.scheduledstart, 6) AS Date,
                          CONVERT(varchar, FilteredAppointment.scheduledstart, 1) AS Date2, FilteredAppointment.scheduledend, FilteredContact.defaultpricelevelid,
                          FilteredContact.jobtitle, FilteredContact.parentcustomeridname, FilteredContact.parentcustomerid, FilteredAppointment.statuscode,
                          FilteredAppointment.statecode, FilteredAppointment.statecodename, FilteredAppointment.statuscodename, FilteredAppointment.ownerid,
                          FilteredAppointment.owneridname, FilteredPhoneCall.activityid AS Expr2, FilteredPhoneCall.subject AS Expr3
    FROM         FilteredActivityPointer INNER JOIN
                          FilteredContact ON FilteredActivityPointer.regardingobjectid = FilteredContact.contactid LEFT OUTER JOIN
                          FilteredAppointment ON FilteredActivityPointer.activityid = FilteredAppointment.activityid LEFT OUTER JOIN
                          FilteredPhoneCall ON FilteredActivityPointer.activityid = FilteredPhoneCall.activityid

     

    Thursday, November 27, 2008 6:38 PM
    Moderator

All replies

  • Your problem is that you're using an INNER JOIN to both the appointment and phone call views, so the query will only return records that are both appointments and phone calls, hence no data.

     

    Try:

     

    SELECT     FilteredContact.fullname, FilteredAppointment.activityid, FilteredActivityPointer.activityid AS Expr1, FilteredActivityPointer.regardingobjectid,
                          FilteredAppointment.subject, FilteredAppointment.scheduledstart, CONVERT(varchar, FilteredAppointment.scheduledstart, 6) AS Date,
                          CONVERT(varchar, FilteredAppointment.scheduledstart, 1) AS Date2, FilteredAppointment.scheduledend, FilteredContact.defaultpricelevelid,
                          FilteredContact.jobtitle, FilteredContact.parentcustomeridname, FilteredContact.parentcustomerid, FilteredAppointment.statuscode,
                          FilteredAppointment.statecode, FilteredAppointment.statecodename, FilteredAppointment.statuscodename, FilteredAppointment.ownerid,
                          FilteredAppointment.owneridname, FilteredPhoneCall.activityid AS Expr2, FilteredPhoneCall.subject AS Expr3
    FROM         FilteredActivityPointer INNER JOIN
                          FilteredContact ON FilteredActivityPointer.regardingobjectid = FilteredContact.contactid LEFT OUTER JOIN
                          FilteredAppointment ON FilteredActivityPointer.activityid = FilteredAppointment.activityid LEFT OUTER JOIN
                          FilteredPhoneCall ON FilteredActivityPointer.activityid = FilteredPhoneCall.activityid

     

    Thursday, November 27, 2008 6:38 PM
    Moderator
  • Thanks for that. Thanks great

    Friday, November 28, 2008 9:11 AM