locked
Report that shows Count = 0 if no activity values RRS feed

  • Question

  • I wrote a simple report that counts activities for a time period set by parameters.  The query returns a list of users with columns displaying the number of activities.  If a user has at least 1 activity, they show on the data return with 0's in columns where they have no activity.  If a user did not have any activities, that user does not show on the return of the query at all.  I would like the user name to show with all 0's for the count of each activity even if they had no activities for the time period selected. Here is the query:

    SELECT     FullName, SalesCalls, Appointments, PhoneCalls, Emails, Tasks

    FROM         (SELECT FilteredSystemUser.fullname AS FullName,
     
    COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'Sales Call' THEN 1 END) AS SalesCalls,
    COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'Appointment' THEN 1 END) AS Appointments,
    COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'Phone Call' THEN 1 END) AS PhoneCalls,
    COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'E-Mail' THEN 1 END) AS Emails,
    COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'Task' THEN 1 END) AS Tasks

    FROM          FilteredSystemUser
    INNER JOIN  FilteredActivityPointer ON FilteredSystemUser.systemuserid = FilteredActivityPointer.ownerid

    WHERE  FilteredSystemUser.businessunitidname = 'Sales'AND (FilteredSystemUser.IsDisabled =  'False')AND FilteredActivityPointer.scheduledstart >= @startdate and FilteredActivityPointer.scheduledstart <= @enddate
                        
    GROUP BY FilteredSystemUser.fullname) AS a

    Any suggestions on how to do this are appreciated!

     

     

     

    Sunday, August 15, 2010 4:36 PM

Answers

  • Hi,

    Please use below Query

    SELECT     FullName, SalesCalls, Appointments, PhoneCalls, Emails, Tasks

    FROM         (SELECT FilteredSystemUser.fullname AS FullName,
     
    ISNULL(COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'Sales Call' THEN 1 END), 0) AS SalesCalls,
    ISNULL(COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'Appointment' THEN 1 END), 0)  AS Appointments,
    ISNULL(COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'Phone Call' THEN 1 END), 0)  AS PhoneCalls,
    ISNULL(COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'E-Mail' THEN 1 END), 0)  AS Emails,
    ISNULL(COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'Task' THEN 1 END), 0)  AS Tasks

    FROM          FilteredSystemUser
    Left Outer JOIN  FilteredActivityPointer ON FilteredSystemUser.systemuserid = FilteredActivityPointer.ownerid

    WHERE  FilteredSystemUser.businessunitidname = 'Sales'AND (FilteredSystemUser.IsDisabled =  'False')AND FilteredActivityPointer.scheduledstart >= @startdate and FilteredActivityPointer.scheduledstart <= @enddate 
                        
    GROUP BY FilteredSystemUser.fullname) AS a

    Hope this helps.


    Thanks, Ranjitsingh R | http://mscrm-developer.blogspot.com/ | MS CRM Consultant
    • Proposed as answer by Ranjitsingh R Monday, August 16, 2010 4:34 AM
    • Marked as answer by Jim Glass Jr Monday, August 16, 2010 3:02 PM
    Monday, August 16, 2010 4:33 AM

All replies

  • Hi,

    Please use below Query

    SELECT     FullName, SalesCalls, Appointments, PhoneCalls, Emails, Tasks

    FROM         (SELECT FilteredSystemUser.fullname AS FullName,
     
    ISNULL(COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'Sales Call' THEN 1 END), 0) AS SalesCalls,
    ISNULL(COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'Appointment' THEN 1 END), 0)  AS Appointments,
    ISNULL(COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'Phone Call' THEN 1 END), 0)  AS PhoneCalls,
    ISNULL(COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'E-Mail' THEN 1 END), 0)  AS Emails,
    ISNULL(COUNT(CASE WHEN FilteredActivityPointer.activitytypecodename = 'Task' THEN 1 END), 0)  AS Tasks

    FROM          FilteredSystemUser
    Left Outer JOIN  FilteredActivityPointer ON FilteredSystemUser.systemuserid = FilteredActivityPointer.ownerid

    WHERE  FilteredSystemUser.businessunitidname = 'Sales'AND (FilteredSystemUser.IsDisabled =  'False')AND FilteredActivityPointer.scheduledstart >= @startdate and FilteredActivityPointer.scheduledstart <= @enddate 
                        
    GROUP BY FilteredSystemUser.fullname) AS a

    Hope this helps.


    Thanks, Ranjitsingh R | http://mscrm-developer.blogspot.com/ | MS CRM Consultant
    • Proposed as answer by Ranjitsingh R Monday, August 16, 2010 4:34 AM
    • Marked as answer by Jim Glass Jr Monday, August 16, 2010 3:02 PM
    Monday, August 16, 2010 4:33 AM
  • Copied your query into environment, and still no return of Users without any kind of activity.  Example, User 1 had no activities at all (on vacation) so User 1 does not appear at all in the return.
    Monday, August 16, 2010 3:36 PM