locked
SQL Query - Custom Report - Duplicates RRS feed

  • Question

  • Hi

    I am attempting to create a custom report of CRM data in visual studio. I need to display a table of all cases according to two custom fields named category and priority with totals.

    I am able to export the data from CRM to a dynamic excel pivot table and create the report very easily however I cannot display the table correctly in Visual Studio. In visual stuido the data is duplicated for each case and not grouped by category and priority.

    For example my pivot table should appear as follows:

    Count of   (incidentid)  
    Category Priority Total
    Incident 1 - High 3
      2 - Medium 2
      3 - Standard 1
    Maintenance 5 - Other 2
    Project 5 - Other 2
    Service Request 4 - Low 15
      5 - Other 57
    Grand Total   82
         

    In visual studio the service request, 4 - low category is repeated 15 times. I have tried Select DISTINCT and Group BY without success. My query is

    select DISTINCT  incident0.incidentid as 'incidentid', incident0.new_priorityname as 'new_priorityname', incident0.new_categoryname as 'new_categoryname' from FilteredIncident as incident0 where ( incident0.createdonutc >= dbo.fn_BeginOfLastMonth(GetUTCDate()) and incident0.createdonutc < dbo.fn_EndOfLastMonth(GetUTCDate())  and incident0.customerid = N'{C0AA16E8-190C-DB11-97EC-00137259CAFC}')

    Appreciate any suggestions

    Thanks
    Tony

    Thursday, August 4, 2011 8:30 AM

Answers

  • You have included the incidentid in your query which is unique for every incident (Case), so your query will never group correctly.  To group incidents by Priority, you will have to drop the incidentid from your query:

     

    select
     incident0.new_priorityname as 'new_priorityname',
     incident0.new_categoryname as 'new_categoryname',
     count(*) as Total
    from
     FilteredIncident as incident0
    where
     incident0.createdonutc >= dbo.fn_BeginOfLastMonth(GetUTCDate()) 
    and
     incident0.createdonutc < dbo.fn_EndOfLastMonth(GetUTCDate()) 
    and
     incident0.customerid = N'{C0AA16E8-190C-DB11-97EC-00137259CAFC}'
    group by
     incident0.new_priorityname as 'new_priorityname',
     incident0.new_categoryname as 'new_categoryname';
    


     


    --pogo (pat) @ pogo69.wordpress.com
    • Marked as answer by TonySim Thursday, August 4, 2011 10:43 AM
    Thursday, August 4, 2011 9:41 AM

All replies

  • You have included the incidentid in your query which is unique for every incident (Case), so your query will never group correctly.  To group incidents by Priority, you will have to drop the incidentid from your query:

     

    select
     incident0.new_priorityname as 'new_priorityname',
     incident0.new_categoryname as 'new_categoryname',
     count(*) as Total
    from
     FilteredIncident as incident0
    where
     incident0.createdonutc >= dbo.fn_BeginOfLastMonth(GetUTCDate()) 
    and
     incident0.createdonutc < dbo.fn_EndOfLastMonth(GetUTCDate()) 
    and
     incident0.customerid = N'{C0AA16E8-190C-DB11-97EC-00137259CAFC}'
    group by
     incident0.new_priorityname as 'new_priorityname',
     incident0.new_categoryname as 'new_categoryname';
    


     


    --pogo (pat) @ pogo69.wordpress.com
    • Marked as answer by TonySim Thursday, August 4, 2011 10:43 AM
    Thursday, August 4, 2011 9:41 AM
  • Hi

    Thanks for your reply. This makes sense and the report is now displaying the correct values howver I am slightly confued. If I dont use 'incident.id' how do display the total number of cases per category and priority.

    Do I need to create a custom column or somehow total the number of categories. My data is correct however ny 'Total' column is blank.

    Appreciate your suggestions.

    Thanks
    Tony 

    Thursday, August 4, 2011 10:02 AM
  • This line is the count:

     count(*) as Total
    

    If you run the query I supplied, you should receive a dataset that contains 3 columns for each row:

    • Priority
    • Category
    • Count of each Priority/Category combination

    I don't know what you're doing with the results of the query, so I cannot say why your Total column is blank.

     


    --pogo (pat) @ pogo69.wordpress.com
    Thursday, August 4, 2011 10:36 AM
  • Hi

    Thanks for you all help. I can't believe I missed that. Report is working 100% correctly.

    Thursday, August 4, 2011 11:10 AM