Salesforce IdeaExchange

13410

Exception Reporting (outer joins)

There needs to be the ability to run exception or without reports. For example: show me all Accounts without an Opportunity. It would also be nice to include the total number of accounts in the report so % comparisons could be made. For example: of the 1000 Accounts in salesforce.com, 350 did not have Opportunities, hence 35% of all accounts are without an opportunity.

fifedog 10/12/06	From what I understand the new reporting coming out in spring 07 will address the out join issue.
AmeriCredit 10/13/06	We have experienced the same limitation where we look at % of accounts not producing.
SonoEric 10/13/06	This is ABSOLUTELY HUGE!!!! We have been dying for this for years. We are in the medical equipment business and have loaded all 5,000 + hospitals in as Accounts in SFDC but we have to do offline reports to match up where our Accounts do NOT have opportunities. With this funtionality, we could create a report of all the accounts that do not have opportunities and sales reps could use this list as their "targeted accounts" list. I sure hope this outter join issue is added in Spring '07 like fifedog suggested. Eric
datadriller 10/15/06	Can somebody maybe relabel the idea, because it's very misleading? "without reports" had me thinking Tadd was asking for reports that don't use reports (for whatever that would be good), while he really meant "Outer Joins" or "Reports on missing records". Both terms are much better to understand.
OSJMgr 10/15/06	During the Dreamforce breakout regarding future features, they mentioned that reporting will become much improved, including the ability to do both inner and outer joins, as well as joining more than 3 or more objects. However, they indicated these enhancements would occur over two releases (Spring 07 and Winter 08?).
5WheelSteve 10/17/06	Hallelujah!!! But please hurry!!! These exception reports are brutal to manufacture outside of salesforce and highly necessary.......
RachelTANDBERG 10/19/06	Definitely need this - I also spend a lot of time exporting data and matching it up.
DMoon 10/19/06	Also looking forward to this coming out soon as I have users who want to be able to see all their contacts who do not have opportunities so fingers crossed I will be able to say that they can!
jrose@salesforce.com 10/20/06	This would address many reporting needs. Nice work, Tadd.
JPamer 10/23/06	Business Objects, the global leader in Business Intelligence solutions, has developed a strong partnership with Salesforce.com to provide advanced reporting and dashboard capabilities. A solution for building advanced "with or without" reports is provided through the Report Pack AppExchange listing below: Crystal Reports Report Pack: Build advanced reports with the Crystal Reports design tool. Includes a Driver for connecting Crystal Reports to your Salesforce.com data and 20 sample reports (including several "with or without" templates). crystalreports.com: Provides the ability to share, refresh, and schedule advanced Crystal reports directly within the Salesforce.com application. We're interested in your feedback and suggestions for future Salesforce solutions, you can post here or contact us directly at feedback.crdc@businessobjects.com.
colinmarsh00 11/01/06	This title should most definately be updated to include outer join. Granted you can still find this with a keyword search but it would make this at a glance much more clear. Is there anyway an admin or the original poster could add to the title?
ksekula 11/02/06	I would like to introduce OCEAN GenRap avaliable at AppExchange Directory. OCEAN GenRap for AppExchange OCEAN GenRap is an innovative reporting solution, which combines the functionality of an advanced text editor with an application to analyze business data. It's allows for simply building advanced reports "with or without". Flash film showing how to create Account listing without Contacts you can see here. Account listing without Contacts report We will be glad if you send your feedback for our solution. You can email at ocean@comarch.com
dcunni 11/20/06	This has morphed into custom report types: http://ideas.salesforce.com/article/show/22239. We are still doing some usability testing on this feature, but for production reporting the partner solutions mentioned above are good options. If you'd like to be a part of our usability testing for custom report types, please email us at tester@salesforce.com..
dcunni 12/18/06	Changed status to Duplicate.
tt 01/03/07	Changed status to none.
MsAmy 01/03/07	Merged Idea originally posted 12/13/06 *Exception Reporting* Exception Reporting seems so logical. I am surprised that you do not offer this feature. All businesses want to report this way, no matter what the industry is. Example - Custom Object labeled "Inventory" I can not run a report that says show me all of my inventory that does not have an opporunity. This is a basic business management report. So many of executives are turned off by this and lose interest in the system. Lack of this feature is your weakest area. Please come up with a way to fix this.
Adoernemann 02/09/07	Merged Idea originally posted 10/26/06 Report for accounts/contacts with no open tasks I would love to be able to pull a report to see all contacts/accounts that did not have an open task set for the future. This way, it would allow me to see if any accounts had been neglected or forgotten, etc.
DR888 02/09/07	Merged Comment originally posted 02/09/07 I strongly agree that this would be useful. The Neglected Reports for Leads and Accounts currently do not have "no open tasks" as a criteria, but they should. The reason is this: Since I touch most leads & accounts so often, I occasionally make an error and leave the record without having properly scheduled the next task. That lead/acct is now completely out of my workflow, never to be revisited again, which is very bad for my business! With this feature, I could run a report once a week to catch any that have "fallen off the table". Please add this important feature!!! Thanks.
tt 02/09/07	if you wanted accounts/contacts with no open tasks, would it include both accounts/contacts with no tasks? as well as no open?
HRearden 02/13/07	The Holy Grail of Salesforce! If this came about I could almost kiss your developers. I spend roughly 40 hours a month exporting data into Excel files so that I perform joins for major reports (e.g., follow-up on cases using "Log A Call" and "Emails", and what the frequency of those responses are). We would love to use something like BusinessObjects -- as their report/dashboard system is lovely, however, we haven't been able to justify the cost (as it is just me, the numbers wonk that would benefit). I would be one (temporarily) happy user if I had this.
forceAMP 02/15/07	One way to do this is to use the new Excel Connector, QueryAmp. QueryAmp eliminates all the manual joining by allowing queries from muliple objects. For more info, see the AppExchange listing at https://www.salesforce.com/appexchange/detail_overview.jsp?id=a0330000002excyAAA
April 02/28/07	Outer joins and custom report types would be a huge success for us too!! It appears I am not alone in the amount of time spent exporting data and applying complex index formulas to decipher data and provide reporting such as all accounts without opportunities, all account without a primary contact identified, etc.
TechnicolorSA 03/06/07	Absolutely agree! This enhancement would be a big value add.
Larryman 03/06/07	Merged Idea originally posted 03/06/07 Lead Activity Report which shows ALL leads whether there's an activity or not I was suprised to learn that you cannot get a report that shows all leads generated by a campaign or a landing page form and includes activities and comments for ALL leads, even if there are no activities yet. In other words, you can run a report showing all Leads generated by a campaign or form filled out on a landing page. And you can run a different report showing all Activities generated for a Lead that was generated by filling out a form on a landing page. But you can't get both! * Lead information: name, company, status * Activity and Comments: and if there are none, then the lead still shows up with a blank in the activity column How happy will our VP, Sales be when he finds out the only way to find out the level of activity against generated leads is to compare two different reports or exported spreadsheets? (Not very) Seems like a simple thing to offer. What do you think?
sdunham 03/12/07	This would be huge for us. I can name a number of reports that we want that this would allow us to get. The key being any quality data checking across objects (audit reports) - would need this. Related lists can often be misoverlook (especially by new employees, and would mess up reporting totally if not used when they are suppose to be.
5WheelSteve 03/13/07	Any eta on this functionality? It was aforementioned in this thread as a spring release.... I am seeing the tulips, but not feeling the love.......
blackCompG 03/19/07	Yes, this would be huge! In fact, I would even like to see Accounts with Opportunities, Contacts, and Activities if possible. I currently have custom fields entitled "has Activities", has Opportunities" and "has Contacts" on my Account Layout. I use this information for many reasons, including evaluating data to be purged from our database. Like mentioned above, this is a multi-step proces requiring the DataLoader and some Excel gymnastics - but hopefully this will be "fixed" with this upcoming reporting functionality mentioned here - Please make it so!
ThomasS 04/14/07	Our organization's sales executives are exclusively utilizing SFDC to manage the business. An outer join solution would allow us to further expand our reporting to include real time exception reporting with drill down such as identification which of 20k plus customers are not being "touched" by our sales & services organization within a given time period. Currently we need to dump static data and create Excel pivots to distribute this information
KSadler 04/26/07	Yes this would be a great functionality to have for our salespeople to have a quick report to find out which opportunities currently have no open activities. Please Implement!!!!
JasperMattG 05/01/07	Another popular idea implemented by Jasper4Salesforce! Jasper4Salesforce includes a powerful ad hoc reporting tool that allows any user to create and save reports to identify exception conditions such as accounts without contacts, etc. A user can report on multiple related object and filter where related objects do not exist (filtering where they do exist is also available). A full-feature trial install is available through the analytics section of the AppExchange (Currently the #1 in sales analytics and #8 overall based on number of installs on the AppExchange). Try it out and tell us what you think!
agluewvrs 05/07/07	Merged Idea originally posted 04/30/07 Contacts with Asset Report WITHOUT Product I need to pull a list of all the assets associated with my contacts, and I want to filter that list by custom criteria I have built on my account object. I CAN'T do that with the SFDC report writer right now. Custom Product fields are exposed. Since Assets can be used without Products, we need to have a choice of a report that allows for the Custom fields on Accounts to get pulled in instead. Many (if not all) marketing groups want to pull lists on people that have bought things, and filter down by the companies those people are in. The current report we can run is severely limited.
michele_p 05/11/07	Yes PLEASE! Among many reports... I need to be able to see Accounts that have purchased software(Asset -Product Family) but have not purchased Support.
KarenCanny 06/01/07	We need to be able report on Opportunties that have not had any activity in a certain time range. This would include modification to the Opportunity Detail as well as any activities added to the Opportunity. We would also need to report on Accounts with no assets.
kstoll 06/01/07	I am trying to set up a report that will show the level of activity for all activity types. Right now, when I run my report, all it shows are the activity types for which there has been some activity. ie. It is not showing me any of the activities for which there has been no activity. We need to be able to show this in our reports so that we can demonstrate where we are performing and where we are not. Can anyone please give me some pointers on how I can get a report to show summary data for activities for which there has been no activity (ie. = 0)
Hayley 06/13/07	Generating reports across objects would be huge for my organization. The enhancements can't come soon enough for me.
manlia 06/26/07	Merged Idea originally posted 06/26/07 Exception reports - Account or Opportunities without Activities I think it will be very useful if SFDC reporting can build exception reports that list all accounts that have no activities. At this point, we have to do a couple reports, export to excel and match/merge them in excel which is time consuming. This report is usually needed for the management staff.
CherylFeldman 06/27/07	Merged Idea originally posted 06/27/07 Exclusion Reports on Objects Currently in our organization all opportunities are linked to contacts if leads are converted correctly. From time to time opportunities are created blindly and there is no way for me to run an exclusion report to say, give me all opportunities that do not have contacts or contact roles attached to them. I currently manually run two reports each day to see the opportunities put in against a opportunity with contact roles report and see if there is a difference in the number of opportunities and if there is, I then look through to see which ones are not showing up on the contact role report and then re-associate the appropriate contact record with the opportunity. It would make my life so much easier if there was a way to run a report on opportunities or any object for that matter to see what objects are not associated with that object, based on a certain date field.
merissathomasarf 07/02/07	Merged Idea originally posted 07/02/07 Create a report of Contacts without Accounts attached. It would great if we could create a report of contacts in SF.com that do not have accounts attached. We have many contacts entered that are strictly contacts and do not have accounts and we need to have reports on these contacts.
NicoleW 07/02/07	Merged Comment originally posted 07/02/07 I'd like to be able to report on Contacts without Accounts and Accounts without Contacts strictly for datacleansing purposes.
tommyq 07/05/07	I would like to be able to generate a report of leads or contacts with no email or phone activity.
Sutterfield 07/12/07	I have seen a few rare cases of outer join available in the custom reports. But we are still dependent on what reports happen to be "pre-canned". I need contacts with or without activities, accounts with or without opportunities, etc etc. It appears outer joins are not entirely absent, but there sure isn't much available for any normal question like "Who are my customer contacts and which of them have been talked to this quarter?"
nayoub 07/17/07	Merged Idea originally posted 07/16/07 Reports: Add option to report on null values Add an option to report on fields with zero results. This is key for us when reporting on productivity when some people did not have any activity for the selected time period. I would like them displayed with 0 productivity, rather than not showing up at all.
Dtaj 07/30/07	Cross-selling reports using outer joins would be a huge benefit to any salesforce.com using products and pricebooks. Give me the ability to run a report on all of my customers who have purchased product A but not product B!!!
ALDmto 07/30/07	Merged Idea originally posted 07/30/07 Exclusion reports I think it could be a great thing to use "Exclusion Reports". For example, in order to follow Accounts that wasn't visited or that doesn't have any opportunities created for one year, you could make a report on all accounts and remove those who have had a visit (or an opportunity) during the year. For the moment, exclusion reports can only be made on Excel after export.
IPaz 08/08/07	Merged Idea originally posted 08/06/07 Account and Contact Report Ability to run an account and contact report that would provide not only list of accounts which have contacts, but also would provide the list of accounts with no contacts. Right now you have to run 2 separate reports and then export to excel and do a match up.
TomaszOczapowski 08/08/07	Merged Comment originally posted 08/08/07 Duplicate idea. See original idea here: http://ideas.salesforce.com/article/show/23676 Please merge
ShannonRLambert 08/08/07	I can only add my "please" to this one. It's probably the single most useful-to-me idea on the Exchange right now. :-)
RafCon 08/14/07	This is in high demand! Bring it!
michaeldenmark 08/15/07	This is my number one feature request.
amagness 08/16/07	It was my understanding that this was going to be added to the Summer release. It's very unfortunate that it didn't make the cut. There is no way to be able to compare who is/is not spending ~ an extremely important tool in measuring marketing outreach.
cpierre 09/07/07	When?
ebajo 09/17/07	Exception reporting -- for example: Accounts who have had no cases within the past 90 days.
jneilan 09/21/07	This should be a priority feature. How can you measure "sales" performance of reps without being able to show a report of all reps, especially those who are not bringing in deals? Having to run a report of reps who bring deals in and then derive the reps that are missing just doesn't cut it.
eric_holbrook 09/25/07	It would be great if I could get reports of Accounts without Contacts and Accounts without Opportunities for adoption metrics.
eld 10/24/07	This is probably the singular biggest weakness of Salesforce.com, and a source of ongoing grief for organisations seeking to use their CRM system to its full potential.
Tina 11/08/07	We would love to see exception reporting.. There are so many times that the executives ask to see a report of accounts that are missing opportunity records and I have to inform them that it has to be done offline using excel and/or access. Not good!! For the people above that are looking for a way to see which accounts have not been contacted... Thankfully we are able to reference the Last Activity record (no details related to the last activity though)...I enter the following in the criteria to provide a list of Neglected Accounts (with no calling activity or no calling activity within the last 90 days)... Last Activity Equals null Last Activity Less Than Last 90 Days Advanced Filter options.... 1 or 2
agluewvrs 11/13/07	Currently there exists a "Has Products" system field that can be leveraged to quickly ID Opps with or without products. A simple outer-join report could be created if we had more of these type of fields like "Has Contacts" or "Has Opps". True outer-joins would enable us to create a "Has Contacts" that integrates criteria so we could build a "All Accounts without Contacts who's first name equals "John"" Check out the "has X" idea: Merge with this http://ideas.salesforce.com/article/show/66249/New_field_Has_contactsassetscu...
globalcollect 11/14/07	This is essential to our company. We need to be able to run reports on accounts that have no contacts (for example, where # of contacts = null) and on our custom objects where # of contacts tagged to it = 0. Is this functionality planned?
DMoon 11/14/07	Agree with comments above - would be huge for us if we could report on contacts without opportunities, for example. Even better would be the ability to do coverage reports where I can see the % of total contacts with opportunities in a given period. Tied in with identifying contacts without opportunities in that period would be a powerful tool for my users and a feather in the Salesforce.com cap in my company. I see that this thread has been going for some time .. any ETA of when we can see the reporting changing significantly?
ahudson 11/14/07	This would be very helpful. I need to know which leads/contacts are not yet related to a campaign so that I can find them and attach them to the appropriate campaign. I can only do this with opps now. This is helpful but it does not completely fix the problem.
idkam 12/17/07	I can't believe it, we're up to Winter '08 and this feature still isn't available? This is the #1 top priority 'idea' for our company.
carramrod 12/21/07	this is needed now! i can't outerjoin accounts with open activites to see which accounts don't have an open activity.
tt Jan 4	People who want pure "outer joins" as they appear in a database would want this idea: http://ideas.salesforce.com/article/show/100464< doesn't allow you to get exception reporting as described by most of the votes here, but it is a step there.
mezner Jan 4	Merged Idea originally posted Jan 4 Reports It would be nice to run a report that will show cases without any active activities/tasks. Mary Kate Fish
tt Jan 8	For more information on the "Outer Joins" coming in Spring'08, please see: http://blogs.salesforce.com/analytics/2008/01/outer-joins-and.html
SalesOps Jan 9	Tom, a couple things. The blog entry you link to doesn't come up - can you fix that? Second, in your comment on Jan 4, you said outer joins as represented in the other Idea would not provide exception reporting - if reporting on, say, Accounts with and without Opportunities, wouldn't using a filter of "Opportunity ID equals (blank)" get us those results? If indeed the report is running off a true outer join, this should work, no?
Dreamteam Jan 9	Our AppExchange product Carousel enables this to be done today cost effectively. You can get hit the "get it now" button on the AppExchange and run it for 30 days for free. Thanks.
tt Jan 10	Blog post is working now (was offline while I fixed my example). If it was a database outer join, and you wanted to do what you say, you'd have to do: select accname, oppname from (select a.name accname,o.name oppname from accounts a, opportunities o where o.account=a.id -- and whatever other criteria you wanted -- ) where o.name=null There's no way of putting that outside SELECT into the reporting today.
gasman Jan 15	I agree with SalesOps. The one thing our reps always ask for is: - Accounts without Opportunities - Accounts without Contacts I can't easily create reports that show them either of these, and the new "with or without" reports still don't seem to get the job done. You cannot do strictly exception (aka without) reporting, and I certainly don't want to pay for an AppExchange application to perform what should be the most basic of reporting needs.
SalesOps Jan 15	I agree wholeheartedly with gasman - while it's great there are AppExchange tools that can do exception reporting, I put my foot down at spending more money on a tool simply to provide what most would consider basic data quality and adoption metrics. If I'm looking for advanced slide-and-dice capabilities, Carousel may be a good choice, but Accounts without (x) should be basic stuff by now.
agluewvrs Jan 20	Spring 08 feature "Account Roll Up fields" can be used to effectively get you the "All Accounts without Opps" report: "Exception Reporting: You can create an opportunity rollup summary field that counts the number of opportunities for each account and reports on all accounts for which that field is zero -sfdc blog" The report will simply be run with filter criteria. This could also be tackled if SFDC added a standard read only system field "Has Contacts" or "Has Opportunities" checkbox (just like the standard read only system field "Has Products").
tt Feb 7	More details on how you can currently use the functionality in salesforce to do this can be found in the article: http://blogs.salesforce.com/analytics/2008/02/exception-repor.html
sortit Apr 25	we have an issue with our data feeds...it would be very useful to be able to 'Show me all locations without Contacts'. I have tried to do this but the results are questionable...although admitedly I have not spoken to my PSR about this....
bwkwong Jun 20	I've been running an excel report that looks for any Implementation Objects without an Open Activity assigned to Owner of the implementation. We would be happy to get a report of implementation objects without an Open activity - but none of the current options work. We can use a roll-up summary field since the Implementation is not a master detail object - this really leaves us with just an Excel report which is not 100% current or can be run by anyone in the company.
mg_cool Jun 29	Changed status to Large Enterprise Idea.
clairet Jul 1	Merged Idea originally posted Jun 25 Conditional Reporting I would like the ability to build conditional logic into reporting filters. Meaning Salesforce could report on A records that do not have related B records. For Example: A records (we will say Accounts) that do not have any B records (we will say opportunity partners) associated with them. Currently Salesforce can not report on data that is not there.
surfsock Jul 16	I understand that they are working up to exception reporting and may have it out in september but that's just speculation based on some "good" information. For a data base with lots of contacts (and many that are mass imported) a way to identify the non contacted accounts (contacts) would be invaluable.
NickG Jul 31	This would add alot of excellent value if implemented ASAP. No one wants to pay extra for an AppExchange product to do something that should be very basic.
barbkuntz Oct 1	The need for exception reporting comes up repeatedly in our org. For example, the ability to find opportunities without contact roles (since contact isn't a standard field in opportunity...). I don't see this highlighted as coming in Winter '09. Bummer.
tt Dec 1	Changed status to Ideas Under Consideration.

track comments for this idea

Please log in to post a comment

last 100 promotions:

anndavis
scsweeney
maksim
brianw4123
miller12
ricgowan
mlperla
tt
acumenuk
CKA
nrwingate
justin@replicon.com
Dezzy
optimaj
nikkig
karenkbb
rcarbone@salesforce.com
ted_battreall
ckinder
quantumbso
sdmarshall
jsmengel
ulrikek
mattiasnordin
engelina
aolsen
feno
barbkuntz
nab
radliberty
rbuchanan
joshvh
charlottefinn
georgier
kimskyworks1
SFJeff
datarzan
jennifer25
david_manelski
ecstowe
ssnyder
esanchez
goforcego
bweller
sonyas
NickG
dublindee
trevorlohrbeer
eyal_wiz
andetw
kimberlyj
mdlaird
surfsock
m62Admin
lorcanore
Sunny
jns100
crmprocessowner
jamiencontonio
md1
sspeltz
daniel_gonzalez
clairet
scraps
pedro_angulo
bwkwong
merial
lfisher@salesforce.com
mobilesarah
wylieatworknz
paul_gilroy
stevo77
dearnr12132007
cventadmin
kbeck
sunedlm
davidsemones
richard_kang
werewolf
njdevilsfan
sortit
pkim04152008
hanstil
tasb
joe_b
snair
joseph_ferraro
Ali_J_in_NH
howard_huie
MicaiahF
tbc
samiradp
eino.laitinen10232007
cecileh
sfwizard3
JustinR
cases02072008
I4Commerce
new_user
mktgmgr

Resources

Successforce.com

Exception Reporting (outer joins) - Salesforce IdeaExchange

Salesforce IdeaExchange

Exception Reporting (outer joins)

Search Community

Categories

1-800-NO-SOFTWARE | Privacy Statement | Security Statement