+ Start a Discussion
claperclaper 

Help with Group By CUBE in VisualForce Page

Hi Guys,

 

I'm trying to create a cross-tabular view of my data in a  Visualforce page. but i'm unable to show the data correctly; on the pageblocktable it doesnt group properly by owner.name so it will show something like this:
              # of customer       #of nonCustomer    #of OpenCovarage
rep 1           399
rep 1                                                  2554
rep 1                                                                                              1

 

 

any help? pls!!

===============================================================================================

this is my controller:

public class mtc_Overview {

String currentUserId = UserInfo.getUserId();
User CurrentUser;
String currentUserRole = UserInfo.getUserRoleId();
List<UserRole> RepRole = [Select id from UserRole where parentroleid =: currentUserRole];
public list<AggregateResult> listAr = new list<AggregateResult>();

public List<Accls> getAccounts()
{
List<id> RepsId = new List<id>();
for(User u:[select name from User where isactive = true AND userroleid =: RepRole ORDER BY name])
{
RepsId.add(u.id);
}

listAr = [select owner.name num, type, COUNT(id) total from account where ownerid =: RepsId GROUP BY CUBE (owner.name,type)];

List<Accls> Results = new List<Accls>();
for(AggregateResult ag: listAr)
{
Accls objAccls = new Accls();
if(String.valueof(ag.get('num')) != null && string.valueof(ag.get('type')) != null){
if(ag.get('type') == 'customer'){
objAccls.totalCustomers = integer.valueof(ag.get('total'));}
if(ag.get('type') == 'Non customer'){
objAccls.totalNonCustomers = integer.valueof(ag.get('total'));}
if(ag.get('type') == 'Open Coverage'){
objAccls.TotalOpenCoverages = integer.valueof(ag.get('total'));}
objAccls.UserName = String.valueof(ag.get('num'));
Results.add(objAccls);}
}
return Results;

}

public class Accls
{
public Integer TotalAccounts {get;set;}
public Integer TotalCustomers {get;set;}
public Integer TotalNonCustomers {get;set;}
public Integer TotalOpenCoverages {get;set;}
public String UserName {get;set;}
}


}

========================================

VF page

<apex:page controller="mtc_Overview" readOnly="true" >
<apex:form >
<apex:SectionHeader title="MTC Overview" subtitle="MTC"/>
<apex:toolbar id="toolbar" style="background-color:#EEECD1;background-image:none;" height="20px;">
<apex:toolbarGroup >
<apex:commandLink value="Territory Designer"/>
<apex:commandLink value="Manager Grid"/>
<apex:commandLink value="SA Grid"/>
<apex:commandLink value="Industry Grid"/>
<apex:commandLink value="Detail Assigment"/>
<apex:commandLink value="MTC Overview"/>
</apex:toolbarGroup>
</apex:toolbar>
<apex:pageBlock >
<apex:pageblocktable value="{!Accounts}" var="arr">
<apex:column >
<apex:facet name="header">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</apex:facet>
<apex:outputText value="{!arr.UserName}" />
</apex:column>
<apex:column >
<apex:facet name="header"># of Customer</apex:facet>
<apex:outputText value="{!arr.TotalCustomers}" />
</apex:column>
<apex:column >
<apex:facet name="header"># of Non Customer</apex:facet>
<apex:outputText value="{!arr.TotalNonCustomers}" />
</apex:column>
<apex:column >
<apex:facet name="header"># of Open Coverage</apex:facet>
<apex:outputText value="{!arr.TotalOpenCoverages}" />
</apex:column>
<apex:column >
<apex:facet name="header"># of MIF</apex:facet>
<apex:outputText value="{!arr.TotalAccounts}" />
</apex:column>
<apex:column >
<apex:facet name="header"># of Leases</apex:facet>

</apex:column>
</apex:pageblocktable>
</apex:pageBlock>
</apex:form>
</apex:page>


 

Best Answer chosen by Admin (Salesforce Developers) 
claperclaper

ANSWERED MY OWN QUESTION:

 

I just need to loop again trough the users and make sure to only add one time every users into the Result list. here is the code

 

==============================================================

public class mtc_Overview {

String currentUserId = UserInfo.getUserId();
User CurrentUser;
String currentUserRole = UserInfo.getUserRoleId();
List<UserRole> RepRole = [Select id from UserRole where parentroleid =: currentUserRole];
public list<AggregateResult> listAr = new list<AggregateResult>();

public List<Accls> getAccounts()
{
List<id> RepsId = new List<id>();
for(User u:[select name from User where isactive = true AND userroleid =: RepRole ORDER BY name])
{
RepsId.add(u.id);
}

listAr = [select owner.name num, type, COUNT(id) total from account where ownerid =: RepsId GROUP BY CUBE (owner.name,type) order by owner.name];

List<Accls> Results = new List<Accls>();
for(User u:[select name from User where isactive = true AND userroleid =: RepRole ORDER BY name]){
Accls objAccls = new Accls();
for(AggregateResult ag: listAr)
{

if(String.valueof(ag.get('num')) != null && string.valueof(ag.get('type')) != null && String.valueof(ag.get('num')) == u.name){
if(ag.get('type') == 'customer'){
objAccls.totalCustomers = integer.valueof(ag.get('total'));}
if(ag.get('type') == 'Non customer'){
objAccls.totalNonCustomers = integer.valueof(ag.get('total'));}
if(ag.get('type') == 'Open Coverage'){
objAccls.TotalOpenCoverages = integer.valueof(ag.get('total'));}else{objAccls.TotalOpenCoverages = 0;}
objAccls.UserName = String.valueof(ag.get('num'));
}
}Results.add(objAccls);}
return Results;

}

public class Accls
{
public Integer TotalAccounts {get;set;}
public Integer TotalCustomers {get;set;}
public Integer TotalNonCustomers {get;set;}
public Integer TotalOpenCoverages {get;set;}
public String UserName {get;set;}
}


}