Reporting and Dashboards Blog
Reporting and Dashboards Blog - Ideas
-
Outer joins - why and how
Jan 15, 2008I talked in the last post about the Outer Joins feature, and I'd like to explain:
- how the filtering and reporting over the "with or without" relationships works
- why we did the filtering the way we did
- what that means to you
- what you can do with the feature
- what you can't do
and all that in the next 8 minutes!
Have you read Types of Joins - a Primer ? If you have, then we are ready to begin.
The join, not considering filtering
From the "Types of Joins" post - let's look at a dataset.
Accounts Opportunities Account Name Account Revenue Account Owner salesforce.com $497,098,000 Marc Benoiff Widgetplex $25 Tom Tobin startup.com $0 Tom Tobin Account Name Opportunity Name Opportunity Amount Opportunity Owner salesforce.com Plants for salesforce.com $20,000 Drew Sechrist Widgetplex Plants for Widgetplex $20 Carl Schachter salesforce.com Plants for incubators $20,000 Drew Sechrist So now we have these two things, and when we outer-join them, we can get back all accounts, even if there is no opportunity:
Account Name Account Revenue Opportunity Name Opportunity Amount salesforce.com $497,098,000 Plants for salesforce.com $20,000 salesforce.com $497,089,000 Plants for Incubators $20,000 Widgetplex $25 Plants for Widgetplex $20 startup.com $0 How the filtering works
The filtering on outer joins is different because of way the outer joins works.
If we filter on opportunity, we still have to take all rows from Account, because that's always what we do.
So, if we filter on "Opportunity Amount>$10,000", we'll get:
Account Name Account Revenue Opportunity Name Opportunity Amount salesforce.com $497,098,000 Plants for salesforce.com $20,000 salesforce.com $497,089,000 Plants for Incubators $20,000 Widgetplex $25 startup.com $0 But why?
Because we have to show all the Accounts. If we remove accounts just because there is no opportunity over $10,000, we are back to the "with" report that we already had since Summer'07 in Custom Report Types.
Why we did the filtering that way
When we did the filtering, we were worried about one thing - keeping the list of Accounts there. That's why we do the filtering the way we do.
Now, you may run into the restriction on filtering across an outer join... you can't use OR.
In an inner-join report using "A with B", you can filter across both elements. In this example, you can say "Account Revenue>$10,000 or Opportunity Amount>$10", and get:
Account Name Account Revenue Opportunity Name Opportunity Amount salesforce.com $497,098,000 Plants for salesforce.com $20,000 salesforce.com $497,089,000 Plants for Incubators $20,000 Widgetplex $25 Plants for Widgetplex $20 What could that mean on an outer-joined report?
We can't remove the Account when the Opportunity doesn't satisfy the criteria, because our basic rule is "the accounts always come back".
We can't include rows from Opportunity if the Account has Revenue larger than $10,000, because there may not be any. But then if a row doesn't satisfy either criteria, how can it be included?
Do you understand it? I sure as heck don't. It makes your head hurt just to think about it.
So you can't use OR across the outer join.
What that means to you
When you filter, the filter on the outer-joined part of the query only affects the outer-joined part of the query.
And if you had a report that was based on an advanced filter, using OR, then if you change your CRT, you'll have an error when you try to run the report. If you can edit he report, you'll be taken to the criteria page to patch up the report. Normally, using AND will do something close to being right.
What you can do
So now you can report even when there are no children. This is key in cases like:
- Campaigns that might not have members
- Opportunities that might not have Products
- Cases that might not have Solutions
Anything that can be used to build a Custom Report Type, can be used when building an outer join.
What you can't do
You still can't do full "Exception reporting". This feature can get you some things, and some exception reporting is possible already. I'll talk about exception reporting and what you can do in a another blog post.
You can't do exception reporting because if you filter on the child - that may or may not be there, it doesn't make a difference to the parent records we return. We always return all the parent records, so filtering the children doesn't get you anywhere, apart from removing child records.
Takeaways
The new joins are a useful tool in the hands of administrators or power users that can create Custom Report Types. You can use them to let report builders make reports they could never build before, allowing them to see data in different ways.
So, wait for production, and I'll have packages of some examples. Until then, sign up for pre-release and try it out there, and tell me if you aren't able to get the same answers as I am.
-
Outer Joins and 154
Jan 8, 2008The more attentive, or those subscribed via the RSS feed to the list of ideas might have seen this idea go by:
Outer joins as "with and without" relationships in Custom Report Types
And to clear up any confusion, this post is going to cover what you can and can't do. Also, some of you will start very soon to have access to the prerelease environment for the Spring'08 release, and I'd like to make sure you know what will be coming.
What are outer joins?
I dealt with different join types in the blog post:
so you may want to go back and read that to make sure we are both thinking the same things when we read the same words.
What is the Outer Joins feature
The changes we're making are to allow the use of what the database world calls outer joins (there is no good RDBMS way of talking about the "Exception reporting" that is one of the most popular ideas)
The concept is that you should be able to ask for all records of a parent, whether or not it has child records. For instance, you can list all opportunities with or without products. You can already do that specific report today in the standard Opportunity reporting, so I'm going to cover another example for the remainder of the post.
Accounts and activities
Lets look at Accounts. Accounts are an object that can have a list of cases. They can have lists of many other things, but for now, cases is what we're going to look at. Activities track tasks and events, in this case, related to a given Account.
Accounts might or might not have activities. As of Winter'08, I can create a Custom Report Type, based on Accounts with Cases
and when I run that, I will see the list of accounts that have cases:
but I don't see any results of accounts that don't have cases. And I'm sure that I have more than these few accounts. This is the Developer Edition demo data, and there are many accounts.
Also, the lack of other accounts might be more than annoying if I wanted to see what % has cases - for example, in the past month.
So, what if I wanted to see what percentage of accounts that had cases in the past month?
I'm glad you asked that question.
Well, let's go back to the Custom Report Type, but hit the "Time Machine" button on my special salesforce.com computer that makes it be spring. Now I have access to the Sping'08 release. I'm now going to edit the same Custom Report Type:
Well, I have a brand new option. I'm going to click it, and make this CRT have a "with or without" relationship between Accounts and Contacts. Cool!
I can see that once I changed the relationship at the Contact level, it changed it at the Case level. Now if there are Accounts with no anything, I'll see them, but also I'll see contacts with no cases, but then if there are cases, I should see them.
Now I can run my report again. As a user, I see no difference, no change in the report wizard. Also, as a user I can't change the relationship semantics. Since most users won't know what relationship semantics are, this might be a good thing. Of course, if you change the definition of a report type, all reports based on it will use the new definition when they are run by your users. This might be bad to change a report definition without telling your users
Which is nice. Once, when I was living in Paris, I flew to the south for a holiday, to a city on the Mediterranean renowned for it's Promenade des Anglais. Which was Nice.
But anyway, now I see all the accounts, whether they have activities or not.
Getting to numbers
First I'm going to group by account.
Now we're going to use the technique covered in "the power of one". Here, I'm going to put in count fields on the cases and Accounts, to let me count them, and then a custom summary formula to total this and get my percentage.
More wierdly, I'm going to :
Hey! So what's Case.Account.num of accounts?
It's a lookup from case, to the account, and the "num of accounts field" which is 1. I added it using the "Add fields via lookup" when doing the page layout of the CRT.
But I'm getting it twice?
Well, the first is if you go get the field from the case. The second is just getting a count of accounts from the list of accounts returned.
Hey presto, 1 for each account for which there is a case, 1 for each account listed, I should get the percentage of accounts with cases!
and let's see the result:
of course, this view isn't that great because there's a line for each Account. But I can see that I get 100% when there was a case, and 0% when there was not. But if I Account Type, and add a count of the accounts, I can see:
Which is a much more useful view for me. Those direct people are ok, but I better talk to Elay about bringing up a partner portal to track channel support cases. And find out why custom type is empty on some of the records.
In a future post, I'll cover why we did this, and why the behaviour is like it is for filtering.
Best practices
Although as you play in pre-release, you can go around changing things and setting whatever you want, in your real environment, things are different:
- don't change all your CRTs to be outer-joined
- you will confuse your users and change their results. Additionally, some things (like doing an OR in an advanced filter across the outer join) are not allowed, and your users will have to fix the report.
- changing results on users is bad
- clone the CRT, and change the clone, and roll it out to your users with a new name and a new description warning them of the change
- make sure your description says where the outer joins start
- e.g. explain that this CRT - "accounts with contacts, with and without opportunities" - has a description of: "Report made using this report type will list all accounts with contacts, whether they have opportunities or not. If they do have opportunities, this will return opportunity data. Filtering on opportunities will not restrict the list of accounts and contacts"
- don't deploy CRTs with Outer Joins until you are ready
- use the "in development" status to keep these CRTs to users with "Manage Custom Report Types" permission
- build and test in sandbox if you need to test what other users will see
- build reports on the outer-joined CRTs to give users a starting point
- as well as the CRT with the description, create an example report to help your users see what data they will get
- don't change all your CRTs to be outer-joined
-
First Custom Report Type Creation
Jul 25, 2007Custom Report Types
The first step to using the new reporting features in Summer'07 is to create a Custom Report Type. I'm going to show you how to create a simple one and bring in extra fields.
Custom Report Types are really useful if you want to expose new fields to users - for instance, to report on the Owner's Division in a report on Opportunities, or to report on a hierarchy of Custom Objects. You can also simplify the Report Wizard for your users by removing fields that they don't care about from the list in the report wizard.
If you are building the Custom Report Types based on a set of different objects, the main four objects that are related as master-detail or through lookups will be queried using Inner Joins. You can find out more about join types in the article from May: Types of Joins - a Primer .
Custom Report Types let you create the view that users will see in the end user report wizard. When users see all the choices of Report Type in the report wizard, they will also see the new types you can create.
The setup tree
First step is to login as an administrator. You'll probably want to start with this, but an as administrator of your salesforce.com account (somebody with "modify users", "modify all data", and "modify billing") you can decide to let other users access the Custom Report Types by setting the permission on their profile. If you've signed up for pre-release, you'll be the administrator, and you can do what you'd like with the data and objects in the Organization you've created. You can sign up by going to the prerelease homepage.
Once you've started, go to the setup, and in the setup tree you'll see the entry for Custom Report Types:
once we're in the Custom Reports Types section (and after a splash page if it's your first visit), you'll see the list of Custom Report Types:
Creating the Custom Report Type
After clicking "New Custom Report Type", we get a page to choose what kind of report type this is going to be:
The Primary Object choice is the first object in the list of objects in the Custom Report Type.
The other objects chosen can either be:
- objects that would be a related list (or related list of an objects that's a related list)
- objects that are detail to this object's master
- objects that are chosen in a lookup field in this object, or one of it's related lists
So if you are going to report on Accounts and their contacts, we'll choose Accounts here.
The Report Type Name is the name that users will see in the Report Wizard (as shown at the top of this page). Users expect to see things like "Accounts with Opportunities". You can name your report type something different if it's important to your business. In salesforce, we might have one with "AppExchange applications with trials"
The description will appear to the right of the choice of Report Type by the end user. This is mandatory, because usually the report type name alone will not tell the end user what kind of report and what data they will get. On a report with Accounts and Contacts in it, the description might be:
This Report Type will show the list of accounts that have contacts, and for each of the accounts, the list of contacts in that account.
The Category is the kind of objects that are involved in the reports. You can't edit this list yet, but you can choose any of the standard report type categories, for instance to put your report type based on Accounts in the Accounts category.
You can then choose if end users will be able to see the Report Type in their report wizard. If you choose "In Development", only users with the "Manage Custom Report Types" will be able to see it.
You should choose "Deployed" unless you are scared somebody will come to your cubicle and tell you off and take your stapler.
You can then click next, after your page looks like this:
And you might be excited at the possibilities of linking many of your objects together. But you might be annoyed - asking yourself
Choosing the objects and records returned
Now you should have a page a lot like this:
- Where is Owner?
- Where is User?
- Where is "Parent Account"?
and so they don't appear here - they are with the other fields, in the next step.
All of the choices here are either:
- detail objects, where Account is the Master
- objects that have a lookup to the Account object
I can choose Contacts though - because it's a detail. So I choose Contacts, and hit save, and now I have:
Adding fields from lookup objects:
Fields from lookup objects can be added in next. For example, these are fields from:
- Account Owner
- Account Owner Role
- Account Owner Reports To Role
- Creator
- Parent Account
Click "Edit Layout", and you'll get a page that looks a lot like the "Page Layout" editor for an object or record type:
This lets us remove fields, to make the page simpler, or re-arrange fields. I'm going to deal with that later, so for today, I'm going to get some more fields - from the Owner of the Account.
I click "Add fields related via lookup"
and I can see the list of lookups available in Account. If I'd wanted the lookups from the Contact object, I'd choose "Contact Fields" in the drop-down, and then click the link, to get the lookup list:
and we can choose the Owner by clicking on it, and see the list of fields:
I can choose the ones I want, then after clicking OK, I can see the fields appearing in the Account section on the page layout:
These are visible as lookup fields by the small magnifying glass icon. If you place your mouse cursor over the tile, you will see where they have come from:
Now we can hit save, and head to the Reports tab, and "Create a New Report" to admire our handiwork.
Next week, I'll look at some simple CRTs that are useful that you can create. After that, we'll look at some ludicrously complicated ones that only serve to show that where there's a will, there's often a way
-
Users only running reports - Restricted users
Oct 6, 2006Who are your users?
When you are building reports, you think about the users of the report - what will they want to see, in what format, what will they want to do next. But what about your user population - how much control does each segment need?
Winter 07
A new analytics feature in Winter ‘07 is the ability to set a profile for users not to be able to change a report. That means certain people can only see the reports that are saved.
Feature detail
Users with this profile setting can still run reports. In fact, the "Run Reports" profile setting (in Enterprise, Developer, and Unlimited Editions) is split in two with Winter ’07. Administrators can control who can run a report as well as the who cannot do the following:- Create new reports
- Customize reports
- Change parameter values
- Drill (and so add columns)
- Delete reports
- Save a report
So what would a restricted user see?
Users who have been restricted using this profile setting will see a different view of reports:
Who would use this?
If you have users that you either do not want to see all data or who should have limited choices (perhaps new users just getting familiar with the application), then this feature may be for you. Note that this feature does not require a switch to a private sharing model so other users are able to see everything. However, it will stop users with this profile setting from being able to report on all data.
Best Practices:
- Make sure these users can only see one folder of reports (that contains reports bounded by their conditions)
- Make sure that users' view of all the world is limited
Bookmark
