Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Ranveer BarooahRanveer Barooah 

Is there a way I can possibly add a variable Baseline/Target line within a Column/Bar Graph that's displaying some Data Sets...? Using CSF in the Report I believe it's not possible as the Target is variable and not a static one hence, the line would not be straight and rather it will a fluctuated one!The red line that I want to represent as a Target LineThe second instance through a Line Graph

Please advise how this can be achieved. Tagets are not captured as a field so need to perform the customization in relation to the report!
Best Answer chosen by Moderator (salesforce.com) 
David CarnesDavid Carnes
Hi Ranveer,

Salesforce does not give us any way of setting target amounts within Reports or Dashboards (save for gas gauges).  One way that we've been able to get close to what you are asking for is to leverage Record Types (available in EE, UE) and store target records within the same Object that you are comparing against.  For example, if we have two Opportunity Record Types (Standard and Target), and all records must have an Owner, Close Date, Amount, and Account (the Account the Target record is associated with can be used or ignored for reporting purposes).  This would allow us on a Report to group by Record Type and show both on the same Report with a timeline (Create Date or Close Date), and y axis value (Record Count or Amount), with both target and actual on the same chart.

A side benefit of leveraging Record Types here is that we can lock down the page layout for the Target Record Type.  When doing this we'll often set the Org Wide Defaults on the Opportunity Object to Private, allowing only the owner and those above the owner in the Role Hierarchy to see the Targets.  Generally we'll also make all the fields read only and remove any delete capability on the Target records, so that only a sales ops or finance team member can adjust the Target records. 

Best of luck!

David Carnes
OpFocus, Inc.
www.opfocus.com/blog
@opfocusinc
Andrew StevensonAndrew Stevenson 
Situation is that we are using Person Accounts and a Related list object called Connections.  I want to create a report that shows the Person's name, email and ContactID where there is a particular entry in the Connections object.

I can get Name, Email and AccountID by using a cross filter on Accounts to the Connections Object.

But I can't find any way to surface the PersonContactID.

(This is to use the report as a data source in Marketing Cloud, which will only accept Contact and Lead IDs.)
Best Answer chosen by Andrew Stevenson
Andrew StevensonAndrew Stevenson

Thanks Ines, Yes, I knew person accounts are a accounts and contacts mushed together.  I would avoid it to, but unfortunately that's how it's been set up here. 

But after following various leads in answers to related queries, and trying various things, I can now answer my own question.

  • Add a new formula field to Accounts called Contact ID (I tried more descriptive names like PersonContactIDforMC, but Marketing Cloud rejected them: the field literally has to be either "Contact ID" or "Lead ID"
  • Set the formula for Contact ID to CASESAFEID(PersonContact.Id) (I tried just =PersonContact.id, but that's a 15 character ID, and Marketing Cloud seems to want the 18 character version that CASESAFEID gives you.)
  • That Contact ID will now work for email sends from Marketing Cloud.
Alex KeallyAlex Keally 
I want to generate a report for all of our existing customer contacts, i.e., all contacts at all accounts that have at least one Closed Won opportunity. Then we can upload to Constant Contact to email with our existing customers (versus prospective customers). So far I haven't been able to figure this out with report types.
Best Answer chosen by Alex Keally
Geoffrey FlynnGeoffrey Flynn
Hi Alex,

You can definitely use a Cross-Filter for this.
https://help.salesforce.com/HTViewHelpDoc?id=reports_cross_filters.htm&language=en_US (https://help.salesforce.com/HTViewHelpDoc?id=reports_cross_filters.htm&language=en_US)

You would also need to quickly create an Accounts with/without Contacts custom report type.  Then it's as simple as this:
User-added image
Ross PeetsRoss Peets 
Hi all, 

What is the best way to go about getting the average in a report? 

I have fields that calculate the number of days in between stages but I need the average of these fields to truly automate the report. I currently have to export the data and finish the math in excel and it would be a lot easier to report on if it was in SF. 

Let me know if I need to include any more information and thanks for any help you all can give!
Best Answer chosen by Ross Peets
Mayank SrivastavaMayank Srivastava
Ok in that case just do the following:

1. Hit Customize
2. 

User-added image

3.

User-added image

Do that for each needed column.
Soumya GampaSoumya Gampa 
Hi!!

I am trying to pull  Account&Contacts report, and have the columns of Billing Street, Billing City, Billing Zip, Billing State likewise Mailing address columns as well.

The problem here is I would like to compare them and get only one column data.
If Billing address data is empty then populate with Shipping Address details on new column and vice versa.

If both Billing address and shipping address details are blank then the new column's data should also be populated as blank.

I am using a row level formula at report level -BLANKVALUE(ACCOUNT.ADDRESS1_STREET,ACCOUNT.ADDRESS2_STREET)

Example:

Billing Address    Shipping Address     Correct Address
123 West             456 East                   123 West
Blank                   456 East                   456 East
123 West             Blank                        123 West
Blank                   Blank                        Blank

Appreciate your response!
Best Answer chosen by Soumya Gampa
Steve MolisSteve Molis
You could do something like this 

BLANKVALUE( BillingStreet , ShippingStreet )+ ", " + 
BLANKVALUE( BillingCity ,ShippingCity ) + ", " + 
BLANKVALUE( BillingState ShippingState ) + " " + 
BLANKVALUE( BillingPostalCode , ShippingPostalCode ) + " " + 
BLANKVALUE( BillingCountry, ShippingCountry ) 


 
Daniel GonzalezDaniel Gonzalez 

Hi SFDC Community,

I'm having a bit of trouble creating a percentage formula for my report. In looking at the screenshot below, here's what I want to do: I want to show the percentage each rep has in each stage in relation to their respective totals. For instance, you'll see that Ashley has a Grand Total of Total Price at $241,216.70 and that she has $10,500.00 in Stage 6. What I want to show is the percent of her total that's in each stage - stage 6 would show 4.35%, Closed Won would show 92.48%, and Closed Lost would show 3.17%

So far, I've only been able to create a formula that shows percentages in relation to the Grand Total, which is not what I'm trying to do.
 

Thanks!User-added image

Best Answer chosen by Daniel Gonzalez
Sunil SarillaSunil Sarilla
Hi Daniel,
You will need to setup the custom summary formula field as below
User-added image

report screenshot
User-added image
Custom Summary formula
AMOUNT:SUM/
PARENTGROUPVAL(AMOUNT:SUM, FULL_NAME, COLUMN_GRAND_SUMMARY)
Just replace the AMOUNT:SUM in the above formula with the Total Price SUM , click on insert summary fields select Total Price and select SUM
 
Pierre FEVRIERPierre FEVRIER 
Hi, I would like to make a chart like bellow (grouping by month to be able to show evolution between years). Anyone to help me ?
Image ajoutée par l'utilisateur
Best Answer chosen by Pierre FEVRIER
Chris EdwardsChris Edwards
This is possible wherever you have two fields to store the date. This is because one of the fields needs to form the chart's X axis and the other can form its bar groupings.

If you're trying to do this with a field like Lead Created Date or Opportunity Close Date, then the standard leads and opportunities report types already give you two date fields to use. You'd then just need to group them in different ways using the Group Dates By function as below:

User-added image

If you're not lucky enough to be given these separate fields by default in your report, you can create an additional formula field on the object in question to copy the date you need - that will then give you the same date across two fields, and you can group them as above.

Hope that helps. 
 
Steve MolisSteve Molis 
Here's a Formula I built to create an "Ultimate Parent Account" field that you can use to create Opportunity Pipeline reports that roll up all Opportunities under the top Account in the hierarchy. 

*** in this example I'm testing up to a 5 Tier Account Hierarchy (Compiled size: 342 characters) ***

Datatype: Formula 
Result: TEXT 
Formula: 
 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Name,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Name,
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Name,
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Name,
IF(NOT(ISBLANK(Parent.Name)),Parent.Name,
Name)))))

 
Best Answer chosen by Steve Molis
Jeremiah DohnJeremiah Dohn
Marrying Ultimate Parent ID + Ultimate Parent Name with levels can create some great reporting.  With the below, you can see all levels of the hierarchy and what they are bringing in from a forecast perspective.

Ultimate Parent ID: 
BLANKVALUE(Parent.Parent.Parent.Parent.Parent.Name, 
BLANKVALUE(Parent.Parent.Parent.Parent.Name, 
BLANKVALUE(Parent.Parent.Parent.Name, 
BLANKVALUE(Parent.Parent.Name, 
BLANKVALUE(Parent.Name, 
Name)))))

All hierarchy level names:
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)),
Parent.Parent.Parent.Parent.Parent.Name & " - " & Parent.Parent.Parent.Parent.Name & " - " & Parent.Parent.Parent.Name & " - " & Parent.Parent.Name & " - " & Parent.Name & " - " & Name,
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Name & " - " & Parent.Parent.Parent.Name & " - " & Parent.Parent.Name & " - " & Parent.Name & " - " & Name,
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Name & " - " & Parent.Parent.Name & " - " & Parent.Name & " - " & Name,
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Name & " - " & Parent.Name & " - " & Name,
IF(NOT(ISBLANK(Parent.Name)),Parent.Name & " - " & Name,
Name)))))
Note that we can't use BLANKVALUE as the base for the formula because "-" would be not blank for each level. Thus using the nested IF().  I'm sure we can optimize all the same to reduce compile size.

User-added image
Hiro SaHiro Sa 
Just to confirm, if there is a job in Scheduled Jobs with a Type value of Report Notification, these are reports users Subscribed to (different from scheduling a future report run)? Also, I noticed that every Job Name for these Report Notification types are in jumbled alpha numberic values. How can you tell what report this is refering to?
Best Answer chosen by Hiro Sa
Jeff MayJeff May
That is correct.   And, there is no good way to match the job name with the report name.
Andrew NerneyAndrew Nerney 
Here is what I hope is a really dumb question:

How do I remove fields from a custom report's field layout? I can add fields without any problem, but when I try to drag a field off the layout, it isn't removed. If I hit any of the "delete" links, I get warnings that I am about to delete an entire section.

There has to be a simple explanation to this (probably user error).

Is there some kind of permission setting that I need to have tweaked on my profile? Or am I just not skilled with the ole mouse and keyboard?
Best Answer chosen by Jayson (salesforce.com) 
Chris McCallionChris McCallion
It wouldn't work for me in Chrome at first, but try this...drag your field over to the "Available Fields" box on the right and then drag it up just a little to the top over the edge where it turns gray and says "previous page / next page"; or drag it to the right off of the box a little.  By doing that I was able to get the heavy black outline and it took my field.  Now each time I remove a field I have to drag it around a little bit beyond the bounds of the box and something triggers in chrome or javascript to wake up and eat my field.