You need to sign in to do that
Don't have an account?
Cross table reporting
I am trying to achieve the following:
I have accounts:
ManufacturerA
RetailerX
RetailerY
There is a custom object “Licence” consisting of:
Licence holder (an account e.g. RetailerY) with other licence details.
An account may have multiple licences.
I have a custom object “Retailer of” consisting of:
Manufacturer (account)
Retailer (account)
For example;
RetailerX is a retailer of ManufacturerA
RetailerY is a retailer of ManufacturerA
I want to report the licence data of retailers of a manufacturer.
Manufacturer: ManufacturerA:
Retailer: RetailerX
Licence 001 …other details
Licence 005 …other details
Retailer: RetailerY
Licence 8097 …other details
Licence 8098 …other details
Licence 8099 …other details
Licence 8100 …other details
I have tried joined reports but haven't found a way to achieve it.
Please can anyone tell me how?
(I'm assuming I have structured the data in the only way possible using custom objects).
Does it require a VisualForce page?
Any suggestions and examples would be much appreciated.
Dave.
I have accounts:
ManufacturerA
RetailerX
RetailerY
There is a custom object “Licence” consisting of:
Licence holder (an account e.g. RetailerY) with other licence details.
An account may have multiple licences.
I have a custom object “Retailer of” consisting of:
Manufacturer (account)
Retailer (account)
For example;
RetailerX is a retailer of ManufacturerA
RetailerY is a retailer of ManufacturerA
I want to report the licence data of retailers of a manufacturer.
Manufacturer: ManufacturerA:
Retailer: RetailerX
Licence 001 …other details
Licence 005 …other details
Retailer: RetailerY
Licence 8097 …other details
Licence 8098 …other details
Licence 8099 …other details
Licence 8100 …other details
I have tried joined reports but haven't found a way to achieve it.
Please can anyone tell me how?
(I'm assuming I have structured the data in the only way possible using custom objects).
Does it require a VisualForce page?
Any suggestions and examples would be much appreciated.
Dave.
I have not yet learned Apex (my plan for 2016), so I can't help you there. I still think you can get this to work without using a Join report. I have two additional suggestions.
- You can filter separately on the same object that has a different path in your report type. For example I recently made the following report and custom report type. The report type structure is Opportunity > Contact > Account, with a lookup from the opportunity to the account associated with it. So the account data exists in the report type by 2 paths and can be filtered separately. The first lIne: "Organization Name: Organization Name..." is a filter on the lookup field from the Opportunity. The last line, AND Largest Gift... filters a field from the account derrived from the contact on the opportunity. This seems similar enough to your case, so this might be worth exploring more.

- Can you add a new lookup field and append the License ID to the 'Retailer of' object. Then you can derive everything from the retailer of object.
Good luck! And please give me a like and/or best answer if any of this helps. That helps me build my profile.All Answers
Hi Ronald,

Sadly, this doesn't answer the question. I'm edging towards having to learn how to query in a visualforce page.
This diagram may help understand my dilemma.
"Ideal Standard" products are sold by "Ware" and "Warwick". I want the licence details of only those two retailers.
A simple join is insufficient unless it limits "Account Name" in block B to "Retailer" in Block A.
...bedtime reading, I fear.
Regards,
Dave.
I have not yet learned Apex (my plan for 2016), so I can't help you there. I still think you can get this to work without using a Join report. I have two additional suggestions.
- You can filter separately on the same object that has a different path in your report type. For example I recently made the following report and custom report type. The report type structure is Opportunity > Contact > Account, with a lookup from the opportunity to the account associated with it. So the account data exists in the report type by 2 paths and can be filtered separately. The first lIne: "Organization Name: Organization Name..." is a filter on the lookup field from the Opportunity. The last line, AND Largest Gift... filters a field from the account derrived from the contact on the opportunity. This seems similar enough to your case, so this might be worth exploring more.

- Can you add a new lookup field and append the License ID to the 'Retailer of' object. Then you can derive everything from the retailer of object.
Good luck! And please give me a like and/or best answer if any of this helps. That helps me build my profile.Point 2 doesn't work as each retailer can have multiple licences.
I've created a class doing a simple query...adding complexity.
Will share result when I get there!
Just struggling to install in our production org.
Trying Force.com IDE.....
Thanks to Jim Rae for the 'wrapper class' suggestion.: https://developer.salesforce.com/forums/?id=906F000000096tcIAA.
I had to extend this to include a further level.
So, I have a wrapper that holds the Manufacturer and a list of a second wrapper.
The second wrapper holds an associated Retailer plus a list of his licences.
Here comes the code.
I've left all the debugging statements in. Also, the VF page isn't formatted. That's next...
Hope this helps a few (including anyone looking at https://developer.salesforce.com/forums/ForumsMain?id=906F000000098fZIAQ).
Enjoy.
public with sharing class WrapperRetOfCON {
/*
This class 'wraps' data from multiple objects into one class for display in a vf page
with thanks to JimRae
https://developer.salesforce.com/forums/?id=906F000000096tcIAA
https://...visual.force.com/apex/vwWrapperRetailerLicences
'Retailers_of' records a manufacturer and the name of a retailer that sells that manufacturer's products
The retailer may have 'licences'.
*/
private List<Account> tempAcct = new List<Account>();
//Map holds manufacturer ID and list of their retailers
private Map<ID,List<Retailer_Of__c>> AcctRetofMap = new Map<ID,List<Retailer_Of__c>>();
//ManuAcctIDs is a set of manufacturer account ids used while getting retailers
private Set<ID> ManuAcctIDs = new Set<ID>();
//Map holds retailer ID and list of their licences
private Map<ID,List<Licence__c>> AcctLicencesMap = new Map<ID,List<Licence__c>>();
private List<Account> tempRetAcct = new List<Account>();
private Set<ID> RetAcctIDs = new Set<ID>(); //for sub sub
public List<wrapper> wrapout {get; set;}//the output to vf page
//constructor
public wrapperRetOfCON(){
wrapout = new List<wrapper>();
}//wrapperRetOfCON
//wrapper 1
class wrapper{
//private variables used to set up sub-sub-level wrapper
private List<Account> tempAcct = new List<Account>();//will hold retailer list
public Account acctManu {get; set;}
//This is a list of other wrappers that are nested in the top wrapper
public List<wrapper2> retofs {get; set;}
public wrapper(){
if(acctManu==null){acctManu = new Account();}//initialize the account holder
if(retofs==null){retofs = new List<wrapper2>();}//initialize the retailer wrapper listholder
}
}//wrapper
//wrapper 2 - the sub-wrapper
//gather list of retailers for a manufacturer
class wrapper2{
public Retailer_Of__c retof {get; set;}
public List<licence__c> retsLicences {get; set;}
public wrapper2(){
if(retof==null){retof = new Retailer_Of__c();}//initialize the Retailer_Of__c holder
//if(retofManu==null){retofManu = new List<Account>();}//initialize the manufacturer/Account holder
system.debug('retsLicences= '+retsLicences);
if(retsLicences==null){retsLicences = new List<licence__c>();}//initialise sub sub wrapper
system.debug('now retsLicences= '+retsLicences);
} //constructor wrapper2
}//wrapper2
//This is called by the "Run Report" Button on our VF page
public PageReference buildwrapper() {
//this will have to be expanded into a loop if # is greater than limit
//first get a list of retailers that are mentioned in retailer_of
tempRetAcct = [select id,name from Account where id in (select retailer__c from Retailer_Of__c) ];//limit 50
system.debug('Retailers: '+tempRetAcct);
for(Account a:tempRetAcct){RetAcctIDs.add(a.id);}
//find their licences
for(Licence__c licence:[select id,name,company__c,company__r.name,active__c,maintenance_expires__c from Licence__c where company__c=:RetAcctIDs]){
system.debug('seeking licences of '+RetAcctIDs);
//if licence, put in Map
if(AcctLicencesMap.containsKey(licence.company__c)){
system.debug('add to '+licence.company__r.name);
AcctLicencesMap.get(licence.company__c).add(licence);//adds Retailer_Of__c for this account to the retof list in the map
}else{
system.debug('new list for '+licence.company__r.name);
AcctLicencesMap.put(licence.company__c,new List<Licence__c>{licence});//adds new Licence__c list for this account to the map
}
}//AcctLicencesMap loop
//first get a list of maufacturers that are mentioned in retailer_of
tempAcct = [select id,name from Account where id in (select manufacturer__c from Retailer_Of__c) ];//limit 10
system.debug('manufacturers: '+tempAcct);
//hold ids in a set
for(Account a:tempAcct){ManuAcctIDs.add(a.id);}
//then find the retailers that sell their products
for(Retailer_Of__c seller:[select id,manufacturer__c,retailer__c,retailer__r.name,manufacturer__r.name from Retailer_Of__c where manufacturer__c=:ManuAcctIDs]){
system.debug('seeking sellers of '+ManuAcctIDs);
//if seller, put in Map
if(AcctRetofMap.containsKey(seller.manufacturer__c)){
system.debug('add to '+seller.manufacturer__r.name);
AcctRetofMap.get(seller.manufacturer__c).add(seller);//adds Retailer_Of__c for this account to the retof list in the map
}else{
system.debug('new list for '+seller.manufacturer__r.name);
AcctRetofMap.put(seller.manufacturer__c,new List<Retailer_Of__c>{seller});//adds new Retailer_Of__c list for this account to the map
}
}
system.debug(AcctRetofMap);
//now put results into wrapper for display on VF page
for(Account aa:tempAcct){
//create and fill a new wrapper object then add to wrapout
wrapper tmpwrapper = new wrapper();
system.debug('transferring '+aa);
tmpwrapper.acctManu=aa;
list<wrapper2> t2 = new list<wrapper2>();
for(Retailer_Of__c oo:AcctRetofMap.get(aa.id)){
wrapper2 twrap2 = new wrapper2();
system.debug('oo='+oo.retailer__r.name);
twrap2.retof = oo;
twrap2.retsLicences=null;
//make a wrapper list of licences
list<Licence__c> templic = AcctLicencesMap.get(oo.retailer__c);
if (templic != null){
list<licence__c> t3 = new list<licence__c>();
for(Licence__c ol:templic){
system.debug(ol.name+' ret='+ol.company__r.name);
t3.add(ol);
}//ol
twrap2.retsLicences=t3;
}//templic
t2.add(twrap2);
}//oo
tmpwrapper.retofs=t2;
wrapout.add(tmpwrapper);//that's a manufacturer and it's retailers plus their licences add to the output object
}//aa
system.debug('Wrapout: '+wrapout);
return null;
}
}
/**
* This class contains unit tests for validating the behavior of Apex classes
* and triggers.
* https://...force.com/apex/vwWrapperRetailerLicences
*
*/
@isTest(SeeAllData=true)
private class vwWrapperRetailersLicencesTest {
static testMethod void myUnitTest() {
PageReference pageRef = Page.vwWrapperRetailerLicences;
Test.setCurrentPage(pageRef);
WrapperRetOfCON theCont = new WrapperRetOfCON();
theCont.buildwrapper();
}
}
<apex:page title="Retailers Of:" controller="WrapperRetOfCON" tabStyle="Account">
<!-- wrapper example from
https://developer.salesforce.com/forums/?id=906F000000096tcIAA
multiple objects in repeat loop
https://.....visual.force.com/apex/vwWrapperRetailerLicences
-->
<apex:sectionHeader title="Manufacturers and their Retailers - 19/09/2016 - 13:33"/>
<apex:form >
<apex:pageBlock title="Start here...">
<apex:pageBlockButtons location="top">
<apex:commandButton value="Run Report" action="{!buildwrapper}"/>
</apex:pageBlockButtons>
</apex:pageBlock>
<apex:pageBlock >
<apex:repeat value="{!wrapout}" var="w">
<hr/>Manufacturer: <apex:outputText value="{!w.acctManu.name}" /> <br/>
<apex:repeat value="{!w.retofs}" var="wr">
Retailer: <apex:outputText value="{!wr.RetOf.Retailer__r.name}" /> <br/>
<apex:repeat value="{!wr.retsLicences}" var="wl">
<apex:outputText value=" Licence: {!wl.name} Active = {!wl.Active__c} Expires {!wl.Maintenance_Expires__c} " /> <br/>
</apex:repeat>
</apex:repeat>
</apex:repeat>
</apex:pageBlock>
</apex:form>
</apex:page>