Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
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 Miglena (Salesforce.com) 
Steve MolisSteve Molis
An #AWESOME bonus tip from @Jeremiah Dohn to turn the Ultimate Parent into a Hyperlink to that Account

Datatype: Formula 
Result: TEXT 
Formula: 
 
HYPERLINK('/' + 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), Parent.Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), Parent.Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Parent.Name)), Parent.Parent.Id, 
IF(NOT(ISBLANK(Parent.Name)),Parent.Id, 
Id))))), 
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))))))

 
Evan PonterEvan Ponter 
I've figured out a workaround for those interested in grouping report results by a date field and want to count the number of "groups" between the highest date value and the lowest date value, inclusive. This also includes any groupings in between those date values that aren't showing any records (desirable in some situations, not all. Just something to be aware of). While this isn't truly counting "the number of groups that you see in your report", it does work for a lot of situations where you have sequential groups of dates with data - e.g. records with January, February, and March dates on them and your report time frame is set to only show January, February, and March.

I will point out that Salesforce might be developing something to address this natively (https://www.salesforce.com/blog/2019/02/ideaexchange-formulas-dates.html). They mention that:
[The] team paired the two ideas together when scoping the feature, which helps report users organize and summarize data to answer questions such as:
  • When was the last time we contacted a customer?
  • How long, from the project start date, did it take to close an opportunity?
  • What’s the average number of records created within a given timespan?
The article goes on to say:
The initial beta functionality for the Summer ‘19 release will allow comparisons via date/time functions, and is currently in development for the Lightning Experience. Additional functions will be supported upon the general availability of the feature, planned for the Winter ‘20 release.

Which sounds like we should cross our fingers for a Winter '20 release. Until then, try this out:

You will need to create a formula field to correspond with the time frame for your group (see #1 below). This formula field assigns a number sequence to the record based on the date field. The starting number and actual values are arbitrary - this just needs to be a sequential series of numbers that are consistent for all records on the object so that you can calculate a difference between two records.

1. Create a formula field on the object in question.
Formula Field

Use the "number" data type with no decimal places.
Number Formula Options

Anything surrounded by brackets [ ] (including the brackets) needs to be replaced with your value.

- - - 1a. For fiscal year groupings, name the field "Fiscal Year of [date field name]" and use this formula. I'm using a fiscal-year-ending model here. You may want to use fiscal-year-beginning if your organization does so that there's no confusion (see below the code snippit), but it really doesn't matter since we are interested in the difference between two records. Also, I recommend storing your fiscal year start month and start day in custom settings so you don't hardcode those values here:
YEAR([date_field_API_name])
+
IF(
    MONTH([date_field_API_name]) > [Fiscal Year Start Month]
    ||
    (
        MONTH([date_field_API_name]) = [Fiscal Year Start Month]
        &&
        DAY([date_field_API_name]) >= [Fiscal Year Start Day]
    ),
    1,
/*ELSE*/
    0
)
For Fiscal Year Beginning, swap the plus sign for a minus sign on line 2, then swap the 1 and 0 on lines 10 and 12

- - - 1b. For calendar year groupings, name the field "Year of [date field name]" and use this formula:
YEAR([date_field_API_name])

- - - 1c. For calendar quarter groupings, name the field "Sequenced Quarter of [date field name]" and use this formula (works for fiscal quarters too as long as your fiscal months start on Jan 1, Apr 1, Jul 1, and Oct 1 - e.g. your fiscal year starts on Apr 1 and you have 3-month quarters resulting in fiscal months starting on Apr 1, Jul 1, Oct 1, and Jan 1):
IF(!ISBLANK([date_field_API_name]),
    CEILING(MONTH([date_field_API_name]) / 3)
    +
    4 * YEAR([date_field_API_name]),
/*ELSE*/
    NULL
)

- - - 1d. For calendar month groupings, name the field "Sequenced Month of [date field name]" and use this formula (works for fiscal months too as long as your fiscal months start on the 1st of each calendar month - e.g. Apr 1, May 1, Jun 1, etc):
IF(!ISBLANK([date_field_API_name]),
    MONTH([date_field_API_name])
    +
    12 * YEAR([date_field_API_name]),
/*ELSE*/
    NULL
)

- - - 1e. For calendar week groupings, name the field "Sequenced Week of [date field name]" and use this formula. This is for Sunday - Saturday weeks. To change which day of the week you want to start on, see below code snippit. I chose January 1, 1905 as my magic date because it's the beginning of a year pretty far in the past that falls on a Sunday. Even if your date value is before January 1, 1905, the calculations will still work since we are only concerned with the difference between two records:
IF(!ISBLANK([date_field_API_name]),
    FLOOR(([date_field_API_name] - DATE(1905,1,1)) / 7,
/*ELSE*/
    NULL
)
To start the week on Monday, replace line 2 with:
FLOOR(([date_field_API_name] - DATE(1905,1,2)) / 7,

To start the week on Tuesday, replace line 2 with:
FLOOR(([date_field_API_name] - DATE(1905,1,3)) / 7,

To start the week on Wednesday, replace line 2 with:
FLOOR(([date_field_API_name] - DATE(1905,1,4)) / 7,

To start the week on Thursday, replace line 2 with:
FLOOR(([date_field_API_name] - DATE(1905,1,5)) / 7,

To start the week on Friday, replace line 2 with:
FLOOR(([date_field_API_name] - DATE(1905,1,6)) / 7,

To start the week on Saturday, replace line 2 with:
FLOOR(([date_field_API_name] - DATE(1905,1,7)) / 7,

- - - 1f. For daily groupings, name the field "Sequenced Day of [date field name]" and use this formula:
[date_field_API_name] - DATE(1905,1,1)


2. In your report, create a custom summary formula.
To calculate the date groupings between the highest date and lowest date, specify the MAX of your formula field from #1 above, then subtract the MIN of your formula field from #1 above, then add 1 (since we want to count both the first and last group, not just what's in between).
[YourObjectHere].[YourNewSequenceFieldHere]:MAX
-
[YourObjectHere].[YourNewSequenceFieldHere]:MIN
+
1
Here's my example - I want to know how many "week" groups are in my report (Just for demo purposes to prove this method is calculating the right number):
Custom Summary Formula Counting Weeks
(this can be displayed at all levels or only at the grand total level)

Here's what it calculates when you run the report:
Report Showing Number of Weeks

Then here's a formula to calculate Average Number of Records per Week:
RowCount
/
(
    [YourObjectHere].[YourNewSequenceFieldHere]:MAX
    -
    [YourObjectHere].[YourNewSequenceFieldHere]:MIN
    +
    1
)
Display this at the grand total grouping only. You should see an accurate average calculation that takes into account how many date groupings you have in your report. Here's what mine looks like:
Average Records per Week

Hope this helps anyone out there trying to calculate something similar in their reports. Comment below with suggestions for improvement, or to celebrate when Salesforce builds this in natively.

Shout out to SteveMo for suggesting I post this. Also his tweet (https://twitter.com/SteveMoForce/status/1130904510833090560) was my motivation to buckling down on this endeavor
Best Answer chosen by Evan Ponter
Evan PonterEvan Ponter
Couple of improvements since I wrote this. Here are revised formulas for step 1. PLUS a few bonuses...


1a "Fiscal Year of [date field name]"
For consistency, this should only resolve to a value if there is a date in your date field.

I'm using a fiscal-year-ending model here. You may want to use fiscal-year-beginning if your organization does so that there's no confusion (see below the code snippit), but it really doesn't matter since we are interested in the difference between two records. Also, I recommend storing your fiscal year start month and start day in custom settings so you don't hardcode those values here:
IF(!ISBLANK([date_field_API_name]),
    YEAR([date_field_API_name])
    +
    IF(
        MONTH([date_field_API_name]) > [Fiscal Year Start Month]
        ||
        (
            MONTH([date_field_API_name]) = [Fiscal Year Start Month] 
            &&
            DAY([date_field_API_name]) >= [Fiscal Year Start Day]
        ),
        1,
    /*ELSE*/
        0
    ),
/*ELSE*/
    NULL
)
For Fiscal Year Beginning use:
IF(!ISBLANK([date_field_API_name]),
    YEAR([date_field_API_name])
    -
    IF(
        MONTH([date_field_API_name]) > [Fiscal Year Start Month]
        ||
        (
            MONTH([date_field_API_name]) = [Fiscal Year Start Month] 
            &&
            DAY([date_field_API_name]) >= [Fiscal Year Start Day]
        ),
        0,
    /*ELSE*/
        1
    ),
/*ELSE*/
    NULL
)


1b "Year of [date field name]"
For consistency, this should only resolve to a value if there is a date in your date field.
IF(!ISBLANK([date_field_API_name]),
    YEAR([date_field_API_name]),
/*ELSE*/
    NULL
)


1c. "Sequenced Quarter of [date field name]"
No changes needed. Same description from above:

For calendar quarter groupings, name the field "Sequenced Quarter of [date field name]" and use this formula (works for fiscal quarters too as long as your fiscal months start on Jan 1, Apr 1, Jul 1, and Oct 1 - e.g. your fiscal year starts on Apr 1 and you have 3-month quarters resulting in fiscal months starting on Apr 1, Jul 1, Oct 1, and Jan 1):
IF(!ISBLANK([date_field_API_name]),
    CEILING(MONTH([date_field_API_name]) / 3)
    +
    4 * YEAR([date_field_API_name]),
/*ELSE*/
    NULL
)


1d. "Sequenced Month of [date field name]"
No changes needed. Same description from above:

For calendar month groupings, name the field "Sequenced Month of [date field name]" and use this formula (works for fiscal months too as long as your fiscal months start on the 1st of each calendar month - e.g. Apr 1, May 1, Jun 1, etc):
IF(!ISBLANK([date_field_API_name]),
    MONTH([date_field_API_name])
    +
    12 * YEAR([date_field_API_name]),
/*ELSE*/
    NULL
)


1e "Sequenced Week of [date field name]"
This should really be using the MFLOOR function to handle dates before January 1, 1905. Plus my original post was missing a parenthesis on line 2 (oops!!). Use this instead for a Sunday - Saturday week:
IF(!ISBLANK([date_field_API_name]),
    MFLOOR(([date_field_API_name] - DATE(1905,1,1)) / 7),
/*ELSE*/
    NULL
)
Here's line 2 for a Monday - Sunday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,2)) / 7),
Here's line 2 for a Tuesday - Monday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,3)) / 7),
Here's line 2 for a Wednesday - Tuesday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,4)) / 7),
Here's line 2 for a Thursday - Wednesday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,5)) / 7),
Here's line 2 for a Friday - Thursday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,6)) / 7),
Here's line 2 for a Saturday - Friday week:
MFLOOR(([date_field_API_name] - DATE(1905,1,7)) / 7),


1f "Sequenced Day of [date field name]"
For consistency, this should only resolve to a value if there is a date in your date field.
IF(!ISBLANK([date_field_API_name]),
    [date_field_API_name] - DATE(1905,1,1),
/*ELSE*/
    NULL
)


AND NOW INTRODUCING...
1g "Sequenced Business Day of [date field name]"

That's right, claiming the 1g spot is the "Sequenced Business Day of [date field name]" field. This will allow you to count the number of Business Days (Monday - Friday) in your report. You are still creating a number field with no decimal places, and you'd use this formula:
IF(!ISBLANK([date field name]) && WEEKDAY([date field name]) > 1 && WEEKDAY([date field name]) < 7,
    (MFLOOR(([date field name] - DATE(1905,1,1)) / 7) * 5)
    +
    WEEKDAY([date field name]),
/*ELSE*/
    NULL
)
Saturdays and Sundays will evaluate to a null value - which means they will be ignored completely even if they appear in your report. The weekdays will each be numbered in sequence. I checked this one for syntax, dates before 1/1/1905, etc so it should be an easy copy-and-paste for you. Then follow step 2 from the original post to create the custom summary formula in your report.


BONUS TIP 1:
You don't need to group your report results by the timeframe you are averaging. Confused? Here's my example. I am calculating the average number of records per week and I want to know that "weekly average" for a time frame spanning the whole fiscal year. I don't need to group my report results by week, only by fiscal year. The custom summary formula will take care of calculating the correct averge for each fiscal year, and the overall time frame in the total column
Average Per Week for Fiscal Years


BONUS TIP 2:
When using multiple groupings, you can use the custom summary formula as-is to find the difference between the maximum and minimum dates in each grouping, or use the PARENTGROUPVAL function to get the maximum and minimum dates from a higher grouping. This allows you to factor in the entire timeframe regardless of the data present in each grouping.

This example is for a report with row and column groupings (formerly known as a Matrix Report). This will need to be displayed at a grouping level (in my case Hostel/Community for the row and Start Date for the column).
RowCount
/
(
    PARENTGROUPVAL([YourObjectHere].[YourNewSequenceFieldHere]:MAX, ROW_GRAND_SUMMARY, [YourColumnGroupingFieldHere])
    -
    PARENTGROUPVAL([YourObjectHere].[YourNewSequenceFieldHere]:MIN, ROW_GRAND_SUMMARY, [YourColumnGroupingFieldHere])
    +
    1
)

There have been 9 weeks in FY 2020. Austin had a record each week, their average per week is 1. Nice.
Eastham had a record in each of the first 3 weeks, but none after that. The calculation for Eastham uses 3 as the numerator (the number of records for that group) and 9 as the demoninator (which is taken from the row grand summary for that column) and results in 0.33. For the completed years, each hostel is getting an average calculated based on a 52-week year regardless of having records in the first and last (or any) week of that year.
Average per Week for Entire Time Frame
Shauna AndersonShauna Anderson 
Im at a loss on this one. I have a joined report that has 2 blocks. One is an Opportunties report type and the other is an Opportunities with Products report type. I can see the whole report (both blocks) when I view the report but some users can only see the Opportunities block, the other one doesnt even show. Does anyone have any suggestions on what I can check to fix this? Thank you! 
Best Answer chosen by Shauna Anderson
Ankit KumarAnkit Kumar
Hi Shauna!

Can you please check if other users have access to Products object (or records under products) that you have address under the block for the users to see?
Charlotte YasinCharlotte Yasin 
Hi everyone,

I hope you have an idea on how to solve this. I would like to show the date of the first ever closed won opportunity on an account. This date shall not be overwrited by new closed wons.

Is there any way to do that without any developer involved?

Thanks for helping!
Best Answer chosen by Charlotte Yasin
Sunil SarillaSunil Sarilla
Hi Charlotte,
Yes, this is possible using the Rollup Summary fields.
Create a Rollup Summary field on the Account object
set the roll-up summary field as below
User-added image
 
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.
Katherine AllisonKatherine Allison 
I'm confused as to why the answer is marked wrong as the manager only needs to delete/rename dashboards within the folder and not actually delete the folders themselves. 

User-added imageUser-added image
Best Answer chosen by Katherine Allison
Jonathan FoxJonathan Fox

I would agree with your answer. If it said renaming the actual folder then I could see why B would be correct. 


Where have you found these questions? I know that people have reported before that places like quizlet are not always correct.

Christophe BoutillierChristophe Boutillier 
Hello,

Sorry not sure if this is in the right Topic, please move it as needed,

I'm using a bunch of salesforce report through Excel ( get Data > Online Services > Salesforce Report)
Until today I was able to login with my salesforce credentials and it worked perfectly.

The login popup windows in Excel goes to https://login.salesforce.com/services/oauth2/authorize (at least the title of the pop up says it),
User-added image
after entering my credentials it does not to recognize the password and goes to https://c.salesforce.com/login-message/promos.html
User-added image
and eventually
User-added image

I have tried to adding the Custom domain for my company, and even reinstalled Office. Same issue.

>Getting a bit despaired now, anyone would have an idea ?

Thanks, Best
 
Best Answer chosen by Christophe Boutillier
John KeepJohn Keep
Hi all. We found a workaround for this at our org. It's a registry hack that forces Excel to emulate IE 11 mode when it normally defaults to IE7/compatibility mode which is no longer compatible with the Salesforce login process. If you're not an admin on your machine, you'll have to have someone else make the change but give them this info:
  1. Open Registry Editor and navigate to HKEY_CURRENT_USER\SOFTWARE\Microsoft\Internet Explorer\Main\FeatureControl\FEATURE_BROWSER_EMULATION
  2. Right click --> Select 'New' --> DWORD
  • Name: Excel.exe
  • Value: 2af8 (hex)
Ian PajaIan Paja 
Hello,

I am an admin at a newer organization that uses Lightning and we have extensive reports that users load and operate off of for their workflow. Our administrative specialists open up a report for inbound calls to audit them, the report shows a list of case numbers, and other details from the object. The auditor clicks the case number and it opens a new workspace tab automatically after the click that the auditor can then interact with the case record. Once done, they close it and are back on the report page.

​​​​​​​However, this morning, the auditors opened the report as usual and found that clicking the case number now opens a subtab under the page tab header that requires an additional click. I can see the option to open the tab as a workspace tab, but I want this to happen automatically when the user clicks the case number visible in the report - see below for what it looks like now:

Shows the subtab next to the case case with the option to make a Workspace tab manually.

I want the result of clicking the case number to appear like this, like it was less than 24 hours ago:
User-added image

I've dug through the App Manager settings, but those only show the object settings for whether related records are opened as workspace tabs or subtabs and the settings all appear correct. I haven't changed anything and the only other active systems admin is out of town.

Is there something I am missing related to how Reports are handled in Salesforce? Was there an update that happened to Salesforce automatically that could have changed these settings? I am at a complete loss. Please help! Thank you!
 
Best Answer chosen by Ian Paja
Anne-Marie MessineoAnne-Marie Messineo
This is a new change in Summer 20.  I'm not a fan and on here toooking to see if there's a way to revert it back to using Workspace tabs.

From Release Notes
In Salesforce console, report links open in a new subtab
When you open a link in a report from the Salesforce console, the link now opens in a new subtab instead of in a new workspace tab.

There's also a Known Issue because Focus does not change when you click into a record on the report.
https://trailblazer.salesforce.com/issues_view?id=a1p3A000001SpbrQAC&title=summer-20-focus-stays-on-report-tab-when-user-clicks-a-link-in-a-report-or-dashboard
Dee DeeDee Dee 

Hi there,

I'm working in a new org and I'd like to create a proper folder structure. Do you have suggestions on folder structure templates? I would certainly appreciate a picture if possible, showing the sub-folders.

Any suggestions on naming conventions would also be helpful!

Best Answer chosen by Dee Dee
Lakhan MeghaniLakhan Meghani
Hi Dee
I cant cake a statement on folder structure because folder structure is purely depends on your sharing setting which we often do via manual sharing to share our reports to particular users and to give them access.

I will say different folders for different objects as we can do sharing from parent folder.
So create different folders for each object to save their respective reports with naming say Lead Reports, Activity Reports etc.

Save their respective reports in their folder. While saving your report, write a description of report which explains its logic of report. YOU can follow naming convention of reports say "Lead: Marketing reports" . Likewise for different objects reports , you can give same naming convention. 
By this, you can search all lead reports if you type Lead in search bix of all reports.

The structure of your folders will be build automatically based on your sharing setting requirement
You can later move your reports to other folders.

Let me know if i can help you more

Thanks
 
Iris MintzIris Mintz 
I created an Opportunity Joined Report but am unable to Drill Down by Rep.  Is this a limitation for Joined Reports? If yes, what is the best workaround to Drill Down?
Thanks!
Best Answer chosen by Iris Mintz
Iris MintzIris Mintz
Drum roll please.... The answer is drill down option is not available for joined report because it is not yet supported by the standard Salesforce functionality.
Iris