function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Andy Morton 14Andy Morton 14 

Show number of cases by contact

Hello

I have a visualforce page (CustomerServiceReport) that uses the standard Account controller and an extension (CustoemrServiceReportExtension) to pull through some basic information about cases relating to that Account (i.e. number of cases raised in last 30 days, number closed, etc). 

I want to expand on this to show the contacts and number of cases they have raised in the last 30 days (ideally restricted to the top 10 with ordered by highest to lowest). For the life of me I don't seem to be able to get it working. All I will need is the Contact's Name the case is raised under and the number of cases they have raised.

I'm fairly new to Visualforce and Apex so still trying to get to grips with it. This is a bit of a side-project (which may make my employer's life a bit easier) that I figure may help me improve my skills. 

Anyone able to help me out? I'm fine with it been a secondary extension if it makes things easier/tidier.  
Best Answer chosen by Andy Morton 14
Gaurav Sharma 472Gaurav Sharma 472
List will be of Type AggregateResult.

here a sample code how you can get different fields

List<AggregateResult> ArResult = [select count(id),contact.name,accountid from case where contactid!= null and accountid=:acct.id AND CreatedDate <= LAST_N_DAYS:30  group by contact.name , accountid  order by count(id) desc];

for (AggregateResult ar : ArResult )  {
    system.debug(ar); //complete row 
    system.debug(ar.get('expr0'));  //count
     system.debug(ar.get('Name'));  //contact name
    
    
}

All Answers

Gaurav Sharma 472Gaurav Sharma 472
Hi Andy,

i assume you can use the same approach you are using for showing data by account.


following query will provide data for cases by contact for given account.   for vf page implemmetation use same approach you used for account.


select count(id),contact.name,accountid from case where contactid!= null   and accountid = 'Your_account_id_from_vf_page'  group by contact.name , accountid  order by count(id) desc
Dushyant SonwarDushyant Sonwar
Andy,

The Aggregate Query solution provided by gaurav will work great for you. If you have trouble implementing it, just post your vf / apex code and he may guide you about his solution! 
Andy Morton 14Andy Morton 14
Hi Gaurav and Dushyant, thank you for your assistance.

I've added the query onto a new extension but getting a failure when saving: Illegal Assignment from List to List. Have been looking through some forums but can't seem to see what's causing it (probably something obvious I'm missing). 
  
public class CSRExt3 {

    Private final Account acct;
    
    public CSRExt3(ApexPages.StandardController stdController) {
        this.acct = (Account)stdController.getRecord();
    }
    
        public List <Case> getNewCases() {
    
            List<Case> results = [select count(id),contact.name,accountid from case where contactid!= null and accountid=:acct.id AND CreatedDate <= LAST_N_DAYS:30  group by contact.name , accountid  order by count(id) desc];
            
    return results;
}
     
    
    
}

 
Gaurav Sharma 472Gaurav Sharma 472
List will be of Type AggregateResult.

here a sample code how you can get different fields

List<AggregateResult> ArResult = [select count(id),contact.name,accountid from case where contactid!= null and accountid=:acct.id AND CreatedDate <= LAST_N_DAYS:30  group by contact.name , accountid  order by count(id) desc];

for (AggregateResult ar : ArResult )  {
    system.debug(ar); //complete row 
    system.debug(ar.get('expr0'));  //count
     system.debug(ar.get('Name'));  //contact name
    
    
}
This was selected as the best answer
Andy Morton 14Andy Morton 14
Hi Gaurav

That's great, working as intended - many thanks for your help.