Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Michael FaberMichael Faber 
I am trying to write a report formula that will return the % of one option from a picklist.

Lets say we have 10 items returned in an activity report, and we are grouping by Type (picklist) and Sub Type (picklist). The Sub Type picklist contains options Inquiry and Transfer

We are trying to create a column that will return the percentage of rows that have selected Sub Type Transfer, irrespective of Type.

User-added image

Is this possible without configuration changes?
Best Answer chosen by Michael Faber
Steve MolisSteve Molis
These are the Formulas that I used (keep in mind I uswed an Opportunity Report in my example to approximate the data)

Formula A
IF(PARENTGROUPVAL(RowCount , CREATED_DATE, COLUMN_GRAND_SUMMARY) = 0, 0 ,
RowCount
 / 
PARENTGROUPVAL(RowCount , CREATED_DATE, COLUMN_GRAND_SUMMARY)
)

Formula B
IF(PARENTGROUPVAL(RowCount, TYPE, COLUMN_GRAND_SUMMARY) = 0, 0,
RowCount / 
PARENTGROUPVAL(RowCount, TYPE, COLUMN_GRAND_SUMMARY))
Formula C 
IF( PARENTGROUPVAL(RowCount, ROW_GRAND_SUMMARY, Opportunity.Do_you_owe_SteveMo_a_beer__c) = 0, 0, 
RowCount / 
PARENTGROUPVAL(RowCount, ROW_GRAND_SUMMARY, Opportunity.Do_you_owe_SteveMo_a_beer__c))


​​​​​​​
Rohini KondreddyRohini Kondreddy 
Hello everyone,

We have a custom object Quote Request with Status as a picklist field. We need to create win/loss ratio report on it. 

I have  created bucket fields on the status to achieve this.
Won (picklist values -  PO received/Complete ) 
Loss (with the picklist values as declined)
Closed (picklist value - closed)
I can't use these bucket fields in the summary formula. How can I achieve the win/loss ratio?

Ideas are much appreciated!

Thanks
Rohini
 
Best Answer chosen by Rohini Kondreddy
Steve MolisSteve Molis
Are they mutually exclusive?  Or are some of the Picklist values in more than 1 Bucket? 

Again, I would replace the Report Buckets with custom Formula(Checkbox) or Formula(Number) fields and use them in your Report Summary Formula like this 
 
( Quote.Won__c:SUM / Quote.Closed__c:SUM )

 
Anand SubramanianAnand Subramanian 
Hi,
I went to SetUp->Data Export->Schedule Data Export. However I dont see the option to schedule weekly. Is there somethingn I am missing here?

Data Export Scheduling
Best Answer chosen by Anand Subramanian
Steve MolisSteve Molis
PS.  Winter'18 is not an "editon" of Salesforce, it is a release.  Your edition of SF does not support weekly data backup

DOCUMENTATION
Export Backup Data from Salesforce 

Available in: both Salesforce Classic and Lightning Experience
Weekly export available in: Enterprise, Performance, and Unlimited Editions
Monthly export available in: All editions, except for Database.com
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
Mary O'DonovanMary O'Donovan 

I am getting stuck on the hands on challange Customize Record Highlights with Compact Layouts, getting error message Challenge Not yet complete... here's what's wrong: Could not find a compact layout with the label 'New Oppty Compact Layout'.

challange is
Create a custom Opportunity compact layout
When the Ursa Major Solar salespeople are on site with a customer, there are a few key fields they need to see right at the top of an opportunity record when they access Salesforce. Create a compact layout that will help them do that.

Create a new compact layout for the Opportunity object with the label New Oppty Compact Layout
Include these fields, in this order: Opportunity Name, Probability (%), Close Date, Stage, Amount, Opportunity Owner
Make it the primary compact layout

I know i am proberly missing something quiet basic but cannot see what it is.
Thanks for some help

 

Best Answer chosen by Ed (salesforce.com) 
Ryan WayRyan Way
Ms White, 
I did the same thing! You are in the wrong area though, that was where you needed to be for the example Create a Compact Layout section. For the challenge, everything is about the same except this:

User-added imageUser-added image

Hope that helps ! 
Aniqa MoinuddinAniqa Moinuddin 
Does anyone know how I can define a formula that returns Monday/Tuesday/Thursday etc from a date field? The only formula I can think of requires that I define a variable ( Sunday = Day (Date expression) + 7x; where x= integer between 0 and infinity)

Thanks!
Best Answer chosen by Moderator (salesforce.com) 
Sami EllongSami Ellong
Try this:

CASE( MOD( Date__c - DATE(1900, 1, 7), 7), 0, "Sunday", 1, "Monday", 2, "Tuesday", 3,
"Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday","Error")
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.