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))))))

 
Jill ArensonJill Arenson 

I am trying to create a dashboard for top 10 frequently viewed Knowledge Base Articles.

(Note:  AppExchange Knowledge Report package already downloaded.  Existing report not working).

Below is a snapshot the dashboard as it exists today.  Note: it does not accurately display the top 10 viewed articles because the report as it exists is sorted by “created date”.  What it is actually displaying is the first 10 most recently created Knowledge Articles and their Total Views, sorted by total views.
Dashboard

 

In order for this dashboard to be a table, I needed to create a report that was a table with a row limit.  I’ve done this and at this point, I am running into a very frustrating error loop.  

The report is Tabular and the Row Limit is set to sort by Total Views, descending.  
Report

 

The Dashboard Settings were set:

Dashboard Settings

However, when the report attempts to run, there is an error which returns me to this screen:
Report Error on Sort

The error instructs that I must choose a sort column (even though I have already done that in my Row Limit filter - which was sort by # views). And, the value of # Views is not available as a sort here.

Choosing any other value sorts the report by that value which results in an incorrect dashboard, as seen in the first screenshot.

It seems that this simple report and dashboard for Knowledge should be possible.

Can anyone give advice on how to successfully create this type of report?

Thanks!
Jill

 




Best Answer chosen by Moderator (salesforce.com) 
Francois LopitauxFrancois Lopitaux
Hi, 

An easy way to create a dashbaord with Top 10 View Articles is:
 - Create a summary report using Knowledge Article + Knowledge View Object
 - Drag & drop the column that you want to display (aka Channel, Top View, ...)
 - Group by the Title column
 - Summarize by the Total Views
 - Add the criteria that you want for example on Channel column to display the gobal number view cross Channel
 - Save the report

Go in Dashboard and edit the one that you want to update:
 - Add Table Widget
 - Click on Formating and select Sort by 'Value Descending'

And Voila !!

Does that work for you?

Notice that Dashbaord included in appexchange sort by the score which is a normalized number of view. More info can be find here:
https://help.salesforce.com/apex/HTViewHelpDoc?id=knowledge_custom_reports.htm&language=en
Best Answer chosen by Sha-Keria Sadiq
Bhavna BanodhaBhavna Banodha
Hi Sha,
This Trailhead Module is good for the beginners regarding Reports (will give you complete insigt)
https://trailhead.salesforce.com/modules/reports_dashboards
https://help.salesforce.com/articleView?id=000113375&r=https%3A%2F%2Fwww.google.nl%2F&type=3
Chris GordonChris Gordon 
Hi,

I am attempting to build a stage duration report to show the length of time opportunities average in each stage.  Anyone have any tips?

Best Answer chosen by Miglena (Salesforce.com) 
Steve MolisSteve Molis
1. Create an Opportunity History Report.
2. Then select Summary or matrix as the Report format.
3. Group the Report by Opportunity History: From Stage
4. Select the Stage Duration field and Summarize(Average)
5. Get me a beer (because you owe me) 
User-added image
Kari DickersonKari Dickerson 
I have a report that pulls the case detail, the owner and the names of the Case Team members, but I can't get a report that will add the field for Case Team Member Role. When I tried editing, and then creating, a report type to add that field it wasn't available. Has anyone figured out how to do this? 
Best Answer chosen by Kari Dickerson
Kavya GKavya G
Unfortunately, we do not have this functionality, an idea is posted for this, check the link below.

https://success.salesforce.com/ideaView?id=08730000000l61tAAA
Tom KranzTom Kranz 
Hi – I have following requirement in a Lightning only organization.
Can you tell me if this is possible? And how it can be done?
 
A report showing all account team additions and removals by date(time). (account access changes are optional)      

Thank you. 
Best Answer chosen by Tom Kranz
Tom KranzTom Kranz
Thanks for the quick answer @SteveMolis.  Of course you were right that you can’t report on account team deletes with standard SF functionality, you are Steve Mo after all.  I wasn’t getting what I needed from report snapshots and I don't necessarily like that I need to schedule them in this case but I can see using them in the future.  I went with what @CarlosSiqueira and @ChrisSimmons were saying, spelled out  in detail with good tips from an email from @GeoffreyFlynn.  “Account Teams are always a bit of an adventure.  You are into some custom work regardless to track changes.  Should be doable but you'd need another table to track those changes.  At that point, the one thing I'd call out is you may want to have a custom account team - so you can show tracked changes right on the page layout - and then backfill the standard Account Team table in the background to support the standard functionality around reports, list views, etc.  But the custom one would be what the end users interact with.  It's fairly common to be honest to go this route once the standard Account Team table hits a roadblock.  The big thing to call out is that if you are doing anything custom you may need to update the AccountShare table as well - which is kept in sync along with the AccountTeamMember object to ensure the appropriate sharing takes place".  Thanks for your help everyone.
Bradley HoyleBradley Hoyle 
How can i make .csv the default report export type. Currently it is.xls and i have to change it every time. 

 
Best Answer chosen by Bradley Hoyle
Mayank SrivastavaMayank Srivastava
Hello Bradley,
It is not something that you could modify or change the behavior of. When you click on 'Export Details', the page will always default the Export File Format to Excel Format (.xls).
Colin JohnsonColin Johnson 
Hi all,

I have a need to produce a report that does a record count summary, but only where the record is unique (e.g. if there are multiple entries for the same record, only count them as one).

To expand a little. Assume I am trying to count how many cases per client each support team member has handled. I can run the report, group it by support team member and show all the cases logged against each team member. From this, I can do a record count, so this shows me how many cases have been logged. But, what I also want to do is calculate how many clients they relate to. 

Make sense? Look at the following example:

Support Person: Fred Jones

Cases for Fred Jones:
Acme Ltd | Case A
Smith Plc | Case B
FJ Holdings | Case C
Acme Ltd | Case D
Acme Ltd | Case E
Smith Plc | Case F

So, what I'm looking for is two summary figures:

Total cases = 6
Total clients = 3 (e.g. Acme Ltd, Smith Plc and FJ Holdings)

Hopefully, I've made enough sense.

Is this possible?

Thanks,

Colin
Best Answer chosen by Moderator (salesforce.com) 
Steve MolisSteve Molis
Create a custom field on each of the objects (I would do this on every object you use in SFDC).  

Field Name: whatever you want
Datatype: Formula 
Result:  Number,0 decimal
Formula:  1  (that's it, just a number 1)

now add these custom fields to your report and Summarize(Sum) them.  You'll get the count of distinct records for each object.
Zack LoeprichZack Loeprich 
I want to create a New Customer report showing accounts with a Prior Year Sales of $0 and a YTD sales >$0. However, not all accounts have their previous year sales expressed in a digit, but rather a character: - , while some are simply $0. How can I grab accounts where their previous year sales are not a digit (or currency)? 
Best Answer chosen by Zack Loeprich
Marc WilsonMarc Wilson
Actually, try this:
YTD sales greater than 0
Prior Year Sales equals 0 
Prior Year Sales equals ""

Then apply this filter logic:
1 AND (2 OR 3)