If you are using Excel to work with salesforce data, you'll know there are three ways:

   
1.      salesforce.com connector for Office
   
2.      The Excel Add-in   
   
3.      exporting CSV files from reports   

Number one is based on this piece of software you'll find the in the setup section of salesforce:

Number 2 is the free software available from this page: http://blogs.salesforce.com/features/2006/05/excel_connector.html

if you mix number 2 and the other 2, you'll have seen a problem - one has an 18 character ID field - coming from the API. The others have a 15-character ID field, that you'll often see in the UI and in the URL line of salesforce.com

If you use just options 1 and 3, and you are working in Excel, you may have found a more insidious problem. Excel's VLOOKUP() function isn't case sensitive. Or you might have not noticed. That might be worse.

An enhanced version of the Excel Connector is QueryAmp/Pro - from ForceAmp - http://www.salesforce.com/appexchange/detail_overview.jsp?NavCode__c=&id=a0330000002excyAAA

15-character IDs

Salesforce's ID structure is normally (for 15 character IDs):
XXXYYYYYYYYYYYY

where
XXX is the object's identifier
   YYYYYYYYYYYY is the ID of the object.
these values are case-sensitive: 00a000000a12345 is not the same thing at all as 00a000000A12345 or worse, 00A000000a12345

In Excel, this character data comes across ok. It will be loaded in as part of a CSV file from a salesforce.com report, or as part of the Office Connector, when it imports a salesforce.com report directly into Excel.

But Vlookup?

Vlookup normally doesn't see case differences. Excel's help will tell you.
So for vlookup, : 00a000000a12345 is the same thing as 00a000000A12345, even though in salesforce.com, it's not. So if you are using it to gather data or join sets of data, it will wrongly join some of the data.

Microsoft has a ready-built solution to help you through these problems. Like a kind of 12-step program for people with distressed, over-sensitive data, it provides a great example of the formula you need to do the case-sensitive lookup:
http://support.microsoft.com/kb/214264

18 character IDs and how to get them

You may just want to get the 18-character IDs, to make your life easier.
To get and 18 character ID from a 15-character one, there's a method described in the salesforce.com help system - "How do I convert an id from 15 to 18 characters?" if you want to search for it. I can't give you a URL because you have to be logged in to see help, and your URL depends on your instance... but you get the idea.

If you are using Excel, and have the Excel Connector described as number 2 above, you'll be able to use a "User-Generated function" to convert to 18 characters.
You may need to use the Excel Connector before the function will become available in Excel.

You can now insert the FixID into a cell and have it translate the 15 character ID from the screen, Office Connector, or CSV file, to an 18-character ID to be used in the API or with the Excel Connector, or a partner product.

Once you have an 18-character ID, you can compare it against the API's 18-character ID, or use VLOOKUP in Excel to gather, group and join the data.

The limits of Excel

Bizarrely, there is a limit to Excel and it's number is also 15. As much as Excel can take text strings of almost any length, it isn't too happy about numbers. Let's suppose you wanted to import this report:

Well, in the exported file, we'd save this data:

But when Excel opened it, you'd see this:

But why is this truncated?
Excel is doing what it's designed to do, and in the event of somebody exceeding the IEEE 754 math limits, it makes it 15 digits of precision. It does this when importing.

If you save the CSV file, then open it through the import wizard in Excel

You can now find all the recordings and the powerpoints from the Dreamforce sessions online. If you missed Dreamforce, or just think you missed an interesting session (given there were 10 tracks simultaneously). :
One presented by Jennifer Wobser and myself:
Visibility and Control with Sales Analytics
- http://blogs.salesforce.com/dreamforce07/2007/10/visibility-and-.html

Others that may be of interest:

These are all sessions at Dreamforce'07 relating to reporting, analytics, dashboards or data quality.

Best Practices for Measuring Critical Support Metrics - http://blogs.salesforce.com/dreamforce07/2007/08/best-practice-1.html

Performance Measurement and Executive Dashboards - http://blogs.salesforce.com/dreamforce07/2007/08/performance-mea.html

Forecasting Excellence: How to Achieve Pipeline Visibility and Forecast Accuracy - http://blogs.salesforce.com/dreamforce07/2007/08/forecasting-exc.html

Advanced Segmentation and Reporting - http://blogs.salesforce.com/dreamforce07/2007/10/advanced-segmen.html

Campaigns to Cash: Best Practices for Tracking Campaign Effectiveness - http://blogs.salesforce.com/dreamforce07/2007/08/campaigns-to-ca.html

Measuring Your Marketing: Getting the Most Out of Reporting and Dashboards - http://blogs.salesforce.com/dreamforce07/2007/08/measuring-your-.html

Best Practices: Reporting on Social Impact - http://blogs.salesforce.com/dreamforce07/2007/08/best-practice-5.html

Domain 2: Business Metrics - http://blogs.salesforce.com/dreamforce07/2007/08/domain-2-busine.html

Applied Data Quality -
http://blogs.salesforce.com/dreamforce07/2007/10/applied-data-qu.html

Data Data Everywhere! - http://blogs.salesforce.com/dreamforce07/2007/08/data-data-every.html

Seeing the Forest: Getting Started with Dashboards - http://blogs.salesforce.com/dreamforce07/2007/08/seeing-the-fore.html

For those that have a developer account (and more so, those who are designing Custom Objects and want to be able to report well on them:

Designing for Data: Best Practices for App Builders - http://wiki.apexdevnet.com/index.php/Members:Designing_for_Data:_Best_Practices_for_App_Builders

Opportunities and products

Opportunities in the salesforce.com world are filled with products. In fact, they are so important that we have a special checkbox option to make sure employees put products on an opportunity when they create one. But what makes up a product in an opportunity?

Existing reports and report types

There are an existing report we ship with salesforce.com:

Here, the complexity of the product object has been hidden from administrators. But there is much that you'd like to do - for instance, to bring in attributes of the price book chosen, or to look at parent objects of the opportunity. If you have made a custom object a parent to products - for instance, to expand on the "product family" you might want those attributes in a report.

What is in the product object on opportunity?

The product object on opportunity is just the opportunity line item, and the fields that you fill when adding an opportunity.
You see this on the screen:
And there are these fields in the record:

But those fields have just a lookup to the product. Where's the product data?
If we make a Custom Report Type from that, I get just these fields:

Where are my beloved product fields?

we can get the product and pricebook fields from their related records.
Going to the Edit Layout page, we can select "Product" from the "View" list

 

Hiding or politely staying out of the way?

Sometimes in salesforce.com, there are objects that you don't see.
Examples of these junction objects are:

• the link between opportunity and contact (the contact role)
• the partners in opportunities
• the accounts linked to an account as partners.

All of them are kind of visible in the interface and the detail pages and reports currently, but are much more visible in Custom Report Types.

Junction objects and what they do

Junction objects are part of the set of objects in salesforce that join one object to another. Specifically, junction objects are used to join many objects on one side to many on the other. For instance, if you have a Contact, it can be attached to many Campaigns. So you might expect a campaign list as a related list on the Contact detail page. But equally, a campaign can cover many contacts. So, the junction object is used to link the Campaign to a Contact. In the case of the Campaign Member - this junction object - it also has some attributes of it's own - for instance, when the recipient responded.

The Campaign Member junction objects in Custom Report Types

When you see the Campaign Member table, you see it in two ways, depending on the direction you look:
From Contacts to Campaigns, we see the Campaign History object
From Campaigns to Contacts, we see two alternatives:

• Campaign members - which is both leads and contacts, but since there is no combined object or page layout, few lookups and no related lists
• Contacts - for contacts only, and all its lookups to other objects and related lists of other records

When we look from Contact to Campaign History, the fields

• First Responded (Date)
• Responded (Y/N)

Will be present on the Campaign History.

When we look from Campaign to Contact, these same fields are on the Contact view.
But why?

To make it like the related list views you already see when looking at these records.

Other junction objects

Additional behavior of this type can be seen:
Between

• Accounts and
  • Accounts when using the Partner Account relationship
  • Users for Sales Team
  • Territories (if you are using Territory Management, and in Winter'08)
• Campaigns and
  • Leads and vice-versa
  • Contacts and vice-versa
  • Campaign Member
• Cases and
  • Solutions and vice-versa
• Opportunities and
  • Contact Roles (which is Contacts and the Role they play in the deal) and vice-versa
  • Partners (which are really accounts)
  • Products (which actually in CRT is a bit more complicated - see the post  )
  • Sales Team (back to user)

In all these cases, the Custom Report Type is behaving just like the related lists you'd see on the detail page of a record of one of those objects.

Making your own junction object

You can also build junction objects for yourself - between standard or custom objects. If you want to make your own man-many link between two objects, you can make a custom object, and add one field as a lookup to the first object, then another field as a lookup to the second object.
If you want the junction object to go away automatically when one of the parents is deleted, then make your junction object a detail of that object.
For instance, imagine you have:

• Customers
• Cars
• Test drives, where customers test drive a car.

When a customer goes away and is deleted, you don't care any more about the test drives they did, and they will be deleted too.

• you would create the Customer object (or maybe use Contact)
• you would create the Car object
• you would create a "Test Drive", and
  • add a new field of type lookup to Car
  • add a new field of type master-detail, pointing to Customer
  • you might add more fields about this test drive - for instance, the date of the test-drive, and who took them on the test drive

The Report Type for your custom junction object

Now you can make a report type. The business user decides:
We care about the Customers most and what cars they test drove. If there were cars that weren't driven, we don't care about them in the report.

We could start with Customer, or Car. Since we care most about Customers:

• Start with the primary object Customer
• in the next step of the wizard, choose "Test Drive"
• Save the Custom Report Type
• Now Edit the layout
• Choose "Test Drive" in the object chooser, and click "Add fields via lookup"
• click "Car"
• now chose the fields from there

Possible Reports for your object

using this, the business user can see:

• Which cars are the most popular (most test drives)
• Which customers are just test driving a lot of cars (and not buying)
• Which customers test drive the same car a couple of times (maybe it's time to lower the price)
• which rep used which cars most (is somebody always trying to sell the V8 with leather seats?)

And you've used a junction object to be able to answer those questions.

Now, for your business, which questions could be answered if you used junction objects to look at the relationships between things?

History

Up until the summer'07 release, there were a few standard ways of reporting on campaigns - the campaign member report from the campaign detail page, or the campaign reports using the report wizard. In Summer'07, we released the Custom Report Types feature. This allows users with the profile allowing them to "manage custom report types" to create a view on any of the objects in salesforce, using the relationships defined between them. And, you may have seen Andrea Wildt talk about this at Dreamforce - in her session "Measuring Your Marketing: Getting the Most Out of Reporting and Dashboards". You can see her talk about this in the recording, but you can't see her do the demo... (you should all bug Kinglsey about that for next time).

One report we'd not seen built-in to the system before was one showing all the campaigns that had touched a deal - a Campaign Influence report that would show, for a deal, what marketing campaigns touched the deals that have been closed. It would show that for all people involved in the deal - not just a campaign that grabbed the first lead.

The objects involved

We want to look at how campaigns influenced deals. So we start with the Opportunity object - that will show us the deal.
Opportunity has a set of Contact Roles - these are the other contacts involved with the deal, and the role they play - for instance whether they are the Business User, an Influencer, on an Executive Sponsor of a deal.

Each contact has a Campaign History - the set of campaigns that touched that user.
The Opportunity may also have a campaign attached to it. This can come when a lead is converted. If the lead was created through a campaign, that campaign information is attached to the opportunity.

Building the Custom Report Type

1. create a new type and start with opportunity as the primary object
2. choose "contact role" as the 2nd level from the list of objects which are related lists of Opportunity
3. choose "campaign history" as the 3rd level from the related lists of Contact

Now your report type should look like this:


Now we'll do the rest in the page layout editor, so save the Custom Report Type and select "Edit Layout"

1. add the lookup to campaign from opportunity, by choosing Opportunity in the object chooser, hitting "Add fields available via lookup" and then choosing the campaign lookup
2. remove many of the Opportunity and contact fields that we don't need for this report
3. save the layout
   

The Custom Report Type is ready to use

Building a report

Now we can navigate to the reports tab - and start the "Create New Custom Report" report wizard.
The field choice page now looks like this:

So we can now build the reports, show top 10 opportunities by opportunity amount and the campaigns that touched those people involved.

AppExchange Package

As usual, I've published an AppExchange package containing the CRT and two same reports - the Top-10 Opportunities report, and a report by campaign of the total amounts of touched deals. You can get it at this URL:
https://www.salesforce.com/appexchange/detail_overview.jsp?id=a0330000004IOTRAA4

You can install it, as ever, in a Developer Edition or trial, or have an administrator install it in your main organization.