Salesforce Joined Report: Add Cross-Block Summary Formula

We shared how to add the same report type in Joined Report (in Classic) back in 2013. Despite the hate and love of joined reports, there is a feature that is not utilized by many users in the joined report, called Cross-Block Summary Formula.

Cross-Block Summary Formula is pretty similar to Summary Formula, with only different fields to be calculated from across blacks.

Not going to talk much about what is a summary formula is, but here is the sample on using Cross-Block Summary Formula. In this blog, I am going to calculate the percentage of Closed opportunities compare to All opportunities by the Opportunity Owner.

Add from Cross-Block Summary Formula, the field will be added to the selected block

Percentage number of closed opportunities compare to all.

formula B0#RowCount/B1#RowCount

My first block is filtered by closed opportunity, and the second block is all opportunity.

Percentage Amount of closed opportunities compare to all.

formula B0#AMOUNT:SUM/B1#AMOUNT:SUM

To move the fields to a new block on their own, add a new block and add a name field. To avoid duplication, you can remove the fields from created earlier.

Here is the complete report

Here is the result

Here are a few notes related to cross-block custom summary formulas:

• You can add a cross-block formula to any block in the report.
• In Lightning Experience, cross-block formulas are automatically added to the report when you create them. In Salesforce Classic, cross-block formulas aren't automatically added to the report when you create them. To add a cross-block formula in Salesforce Classic, drag it to a block.
• When you add cross-block formulas to a block, they appear to the right of standard ones in the order in which you add them to the block.
• The results of cross-block formulas are affected by the filter options applied to the blocks in the report. As a result, a cross-block formula can yield different results when you change filter options.
• Each joined report can have up to 10 cross-block custom summary formulas.
• Deleting a block that’s used in a cross-block formula also deletes the formula from both the Fields pane and any remaining blocks containing it.
• Each cross block formula must have a unique name. However, standard and cross-block custom summary formulas can have the same name.
• Cross-block formula names can't include brackets (“[“ or “]”).

Reference:

• Add a Summary Formula to a Joined Report
• Summary Formulas with Joined Reports

Salesforce: Personalize Order of items in Related List

Salesforce admin can set the order of the related list in page layout. The screenshot below is an example from the Account page.

How can I change Opportunities as the 1st tab instead of Contacts? You can personalize this for yourself.

1. Click your avatar, select Settings

2. Click Display & Layout, then Customize My Pages

3. Select Accounts from dropdown, then click Customize Page button

4. Move Opportunities above Contacts

5. Click the Save button

Now you have personalized your Opportunities in the related list above Contacts, and this does not impact anyone else.

Salesforce: Extract Fields using Report

As a Salesforce admin, sometimes we need to extract the list of all fields from an or multiple objects, for audit, clean-up, documentation, checking, and so on. 

In Classic, we can simply copy and paste the fields list to an Excel file, but in Lightning, it is not so easy when you have lots of fields in an object. What is the alternative?

• Use 3rd party app, such as Field Footprint, Field Trip, Config Workbook, Salesforce Inspector.
• Report, yes we can run a Salesforce report on objects and fields.  

This blog will discuss how to create Salesforce reports to get the list of objects and fields.

1. Create a Report type

Select the Primary Object = Entity Definitions, then relate to Field Definitions. Deploy it if you want to share the report with your users.

2. Create a Report

Create a new report as normal, use the report type created in (1), and of course as per normal Salesforce report, you can export the report to an Excel file.

In the screenshot below, I want to get all fields from the Opportunity object, add a filter to the report Master Label = Opportunity. make sure the Master Label in the filter is from Entity Definitions, not from Field Definitions.

Few interesting information of field can be gathered here:

- Relationship Name: what object does the field refer to

- Calculated Formula Field References: list of all fields used in the formula 

You also can add Description, Last Modified Date, if the field is sortable, filterable, calculated,  indexed, etc.

One of the drawbacks of using a report to get fields in the object, if your Profile does not have access to the field (Field-Level Security), you will not see those fields in the report.

Tableau CRM: Adding Row Number

Adding a row number would be something simple in Tableau CRM, but sometimes we just forget how to add it.

In a Compare Table

Here is the step:

1. Open the dataset in a lens or dashboard widget

2. Add "Group By" a field

3. Select the value for "Column", by default is "Count of Rows", you can change to Sum or Average or others

4. Sort the "Count of Rows" descending or ascending

5. Click the "+" button under "Count of Rows" and click Add Formula link at the top right corner of the window

6. In the new window, enter "Row Number" at the Column Header, you need to click Apply button to see the changes

7. Under "Calculation", select f(x) then "Rank Within Group"

8. Under "Function", select "Row Number"

9. Now we have Row Number in the compare table 😊

In a Values Table

This is much simpler, we just need to enable the Show Row Index Column under the "Other" parameter.