+ Start a Discussion

How are the standard "AND" report types created?

On the opporunity object we have several custom lookup fields that look up to custom objects. When you create these fields there are new report types automatically created. Something like "Opportunties AND Primary Solution". Here are some other examples:



Yet I also have a custom lookup field on the Opportunity object that looks up to the standard Account object (Reseller Account). With this relationship there is no automatically generated report type Opportunities AND Reseller Account. Does anyone know why? Does this only work with custom objects?


Yes, I know custom report types could address this issue but this is not an ideal solution. The automatically created report types also automatically pull in other fields form the opp account, owner, etc, and groups them accordingnly. It's a lot of work to do this on a  custom report from scatch.


Does anyone know why this isn't working?





Usually when you have lookups like that it files the report for it under the parent object.  Did anything get added under Contacts & Accounts?


I can see why it might not get generated though -- imagine having another report type called "Accounts and Opportunities."  It would be hard to distinguish the new one from the original.


Under the parent object it would be a "with" report as there could be multiple opportunities associated with the reseller account. Oddly enough I do not see this report in the Accounts & Contacts section.


I can see how it could be confusing to have an "Opportunities and Account" as it wouldn't be clear what account. The custom account lookup or the opportuntiy Account but "Opportunties and Reseller Account" is not that confusing. Looks like custom report is the only option but it just wont be as polished.


Did a little more testing and this is what I've found.


Opportunity Object with lookup to custom object -> "Opportunities and Custom Object Name" report type automatically created

Opportunity Object with lookup to standard object -> No automatic report types created.

Custom Object with lookup to Opportunities object -> "Custom object with Opportunities" report type created in Other Reports category but this is structured very similar to an "and" report in that it pulls some of the basic account information.


Apparently if you have a lookup from a Standard object to a Standard object you get none of the automatically generated report types. Unfortunately not what I was hoping for.

Tom TobinTom Tobin

I prefer to call those report types "auto-generated" because the report types that are shown because you check the "allow reporting" checkbox.

Here's the rules:

1) you have a custom object, you check "allow reporting", you'll get a report type with the name of hte custom object

2) if you have the custom object as the child of anything, and check the box, you'll get a "parent WITH custom object" report type

3) if you have a lookup from the custom object that's the child to another custom object (co2), then you'll get a "parent WITH custom object AND co2

4) if you have a standard object and a lookup to a custom object, you get "object AND lookup custom object"


You never get report types generated from lookups otherwise, I think because they are too many lookups, and creating them would create far too many (e.g. for opportunity there would be opportunity AND account, opportunity AND contact, opportunity AND  campaign, opportunity AND created by user, opportunity AND owner user, opportunity AND last modified by user.


This all is why we designed the Custom Report Type framework, Go join up to 20 linked objects, linked any way you want (lookup or master-detail) and name it the way you want.


More details on what gets created when and the types of report types were in the presentation "reporting for developers"


Tom TobinTom Tobin

oh, and you never get a report type shown for Standard object AND standard object...


Thanks for the details, I had a difficult time tracking this down in the documentation.


Yup, that last one is the what I discovered. Custom report types it is!


I'll create/search for an idea and maybe I am missing it today but when you add fields via lookup to a custom report type the fields always go to the top section of fields. You then have to manually move and sort them in the section you actually wanted them to go to.