You need to sign in to do that
Don't have an account?
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"> </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>
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;}
}
}