function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
David Roberts 4David Roberts 4 

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.
 
Best Answer chosen by David Roberts 4
Ronald OlechRonald Olech

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.

  1. 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. Report filter on main object and on the same object but via a Lookup in the report type
  2. 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

Ronald OlechRonald Olech
You don't mention if you've tried it, so I would suggest first construct a custom report type.  The built in reports won't work for such a specific need.  Based on your description it sounds like you could make Accounts primary (in the report itself, you'll filter this to only show accounts that are manufacturers).  Join it with "Retailier of' and save.  Then in the bottom of the report summary is a section: Fields available for reports.  Click  Edit Layout.  Then on the right-hand side, under the legend will be a box labeled View: with a drop-down menu of the onjects selected in the join.  Select Retailer of, then just below that click on the link "Add fields related via lookup »".  From here you can get the retailer and from the retailer the license (I think - hard to know with the info available).  I hope this helps!
David Roberts 4David Roberts 4
Hi Ronald, Many thanks for your reply. I think your last sentence, “Add fields related via lookup »". may prove the missing link! I’ll give it a try. Warm regards, Dave R.
David Roberts 4David Roberts 4

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.
Report dilemma schema and example

...bedtime reading, I fear.

Regards,
Dave.

Ronald OlechRonald Olech

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.

  1. 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. Report filter on main object and on the same object but via a Lookup in the report type
  2. 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.

 


 

This was selected as the best answer
David Roberts 4David Roberts 4
I think it's because I'm using custom objects.
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!
David Roberts 4David Roberts 4
Well, I've managed to code the query, class, controller, VF page and get 100% code coverage!
Just struggling to install in our production org.
Trying Force.com IDE.....
David Roberts 4David Roberts 4
Wow. It's taken me some time but here is the solution...
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.
David Roberts 4David Roberts 4
The wrapper controller:

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;
    }

    
}
David Roberts 4David Roberts 4
The test class:

/**
 * 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();    
       
    }
}
David Roberts 4David Roberts 4
The VF page:
<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>
David Roberts 4David Roberts 4
I need to do a similar thing with Accounts with Licences and Contacts. I'll post that example, too.