0

I am newbie so for the first time i am using group by clause.But i am not getting where i am going wrong.

I have a VF Page where there is a status field which is a multi-picklist and a record type field which is also a picklist along with other fields like date which all are criteria.When user enters the criteria and clicks on run report the pdf or xls based on which report type user selects.

The original query which works for all record type is as below

String query = 'Select Strategy__c,Deal_Currency__c,Sector__c,Fund_Size_Millions__c,Geography_New__c,Point_of_Contact_lookup__c,Point_of_Contact_lookup__r.name,Timing_Quarter__c,Timing_Year__c,Total_Leverage__c,Description__c,Deal_Comments__c,Source1__r.name, Term_Sheet_Provided__c, Status__c,Analyst1__r.name, Sr_Leverage__c, Sponsor__c,Sponsor__r.name, Source_Contact2__c, Source_Contact1__c, Seller__c, Revenue_mm__c, Name, Mezz_Tranche_Amount__c, EBITDA_mm__c, Deal_source__c, Date_Received__c, Banker__c, Analyst3__c, Analyst2__c, Analyst1__c,Priority__c,Timing__c, Transaction_Type__c,Deal_source__r.name From Log__c';

query += whereClause;
query += ' order by Date_Received__c asc, name asc';

Now the requirement is when the user selects the one of the record type in it called Mezz record type...i have to group by status and subtotal on 5 amount fields and i have display subtotal fields in VF page rendered as pdf/xls.So i have written separate query for that.I dont know whether using the separate query is the best solution,please correct me if i am wrong.Below is my query.

String groupDealStatus;
if(isMezzRecordType)
{
    groupDealStatus='SELECT Status__c,SUM(Equity_amount__c) equitySum,SUM(Equity_Payment_Amount__c) equityPayment,SUM(Debt_Payment_Amount__c) debtAmount,SUM(Debt_Amount__c) Debt,SUM(Japan_Debt_Amount__c) JapanDebtAmount From Log__c ';
    groupDealStatus+= whereClause;
    groupDealStatus+= ' GROUP BY Status__c';
    System.debug('****groupDealStatus*****'+groupDealStatus);
    AggregateResult[] totalDealResults= Database.query(groupDealStatus);
}

Is this the correct way of performing group by?Pdf VF page is not rendering when i select more than 1 picklist value from status multi-picklist.If i have to write wrapper class for this how i can proceed with it.

Please help.

Thanks.

4
  • Where is recordtype ? log__c field? – Ilya Lepesh May 5 '16 at 4:35
  • log__c is an object from where i am fetching all required fields – Learner_sfdc May 5 '16 at 4:39
  • so you need condition on log__c with given recordtype name ? – Ilya Lepesh May 5 '16 at 4:40
  • Yes...I am taking record type from the URL params as user will select it in VF Page.string mezzRecordTypeName = Log__c.SObjectType.getDescribe().getRecordTypeInfosByName().get('Mezz Deal Log').getRecordTypeId(); isMezzRecordType=(System.currentPageReference().getParameters().get('recType')==mezzRecordTypeName)?true:false; System.debug('***********Mezz condition*****'+isMezzRecordType); – Learner_sfdc May 5 '16 at 4:43
2

Given query returns aggregated result for 5 fields and groups by field status, where Status and recordtype is in condition.

SELECT Status__c,
SUM(Equity_amount__c) equitySum,
SUM(Equity_Payment_Amount__c) equityPayment,
SUM(Debt_Payment_Amount__c) debtAmount,
SUM(Debt_Amount__c) Debt,
SUM(Japan_Debt_Amount__c) JapanDebtAmount 
FROM Log__c
WHERE Status__c in ('ok','nok')
AND ( recordtype.name = 'recname' OR recordtypeId = '0xxxxxxxxxxxx')
GROUP BY Status__c

If you don't need to display recordtype - should work. Try this in query editor, if it's what you need, it will work in VF page. Every time you gonna to generate pdf, you should run query.

2
  • please let me know, if it works – Ilya Lepesh May 5 '16 at 4:44
  • Sorry ,Because of server problem it was not loading,The query that i was using is running – Learner_sfdc May 5 '16 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.