Rollup Sum NOT EQUAL to Rollup Sum: Reporting Solutions – Jenny’s Admin Tip #53

We all know the power of Salesforce reports. The ability to gain a visual representation of your data, no matter how in-depth or basic that may be, is one of the greatest features that Salesforce offers to its users. However, there are times when reporting can be more difficult than creating a standard ‘Show All Account and Contacts Report’, as you shall see below.

In a recent post I provided a solution to an report filtering issue that I had come across – Field NOT EQUAL to Field, which you can find here. As you will find from that post you are unable to compare data in reports, an issue but one that is easily remedied. Recently I came across another filtering issue, this time with Rollup Summaries.

This was the report request:-

Show all Net Total of ALL Invoices which are NOT EQUAL to the Total Amount on ALL Order Products per Order record (an Order can have multiple Order Products and multiple Invoices).

Part of the solution here would be to introduce Rollup Summaries (if they do not already exist) to sum up the total net amount of invoice records and the total amount of Order Products from the related lists on the parent record (Order) detail page.

Rollup Sum Field NOT EQUAL to Rollup Sum Field – Formula Field.

The first thing to note is that we would only need to create one Rollup Summary for Net Total Invoice Amount. This is because the field, Total Price, ( a System Generated Formula on Products) is basically a Rollup Summary field to the Order Amount standard field on the Order object thus giving the combined total of all Order Products per Order.

(To know how to create Rollup Summaries click here, but for now I will assume that all RollUps exist.)

So the criteria is to show where the (RollupSum) – All Invoice Net Total is  NOT EQUAL to the (Rollup Sum) – Total Amount of  Order Products.

Ok so you may think that we can continue to create a formula field (like the one attempted in the Field NOT EQUAL to field blog above ), that would state:-

IF( Order.TotalAmount  <>  Order.AllInvoiceNetTotal__c,  “NOT EQUAL”, “EQUAL”) 

However, you would soon learn that it is NOT possible to use RollUp Summaries in a formula field.

Again to save you having to do the following in the future VOTE up the idea to have Rollup Summaries in Formulas here!

Ok so we have learned that we are unable to create this required report using a Formula field as the fields required are Rollup Sums. Therefore, we have to look at other options to give us the required report results.

Rollup Sum NOT EQUAL to RollupSum – Process Builder.

The only other option that came to mind to gain the required report data was to create a process with Process Builder. The process would update a custom field based on the formula that was described in the rule.

So this is what I did (again there are variations to how you can create this Flow).

1. First I created a new Checkbox field (Invoice<>OP) on the Orders object. Setup>Customise>Order>Fields>Create>Field>Checkbox

2. Then I continued to add the field name and made sure the Default Value is Unchecked.

3. I added this field to the page layout and made sure it is visible to the profiles who need to see the information on the report. If a field is Hidden at Field Level Security then users will not be able to view that data on the report.

Now onto Process Builder

4. Go to Setup>Create>Workflow & Approvals>Process Builder >New

5. Choose Object and Specify When to Start the Process – In this case the Object will be Order and the process will start ‘when a record is created or edited’. 

6. Continue to define your Rule Criteria for the action group. We want our formula to show where All Invoice Net Total is NOT EQUAL to the Total Order Amount. We can put the following:-

Criteria Name> Criteria for Executing Actions> Formula: [Order].All_Invoices_Net_Total_c<>[Order].TotalAmount

7. Click Save.

8. Continue to define the Immediate Action – in this example we want to update a field. So select Update Record>Add the Action Name> Select the Record> Select the Criteria for updating the record >Set new field values for the records you update. 

   

9. Click Save
10. Now of course we will  need a counter rule to the above Process to set the flag off when it is equal i.e. if Invoice <> OP then set true else set false. So continue to add another Rule Criteria remember that we want the AllInvoiceNetTotal = Order Amount.

Criteria Name> Criteria for Executing Actions> Formula: [Order].All_Invoices_Net_Total_c =  [Order].TotalAmount

11. Click Save
12. Continue to define the Immediate Action – in this example we want to update a field. So select Update Record>Add the Action Name> Select the Record> Select the Criteria for updating the record >Set new field values for the records you update. 

Tah Dah! Now we have built a Process that will update the new Checkbox to True when the Rule Criteria equals AllInvoiceNetTotal<>TotalAmount and False when the AllInvoiceNetTotal=TotalAmount. Therefore, this gives us a field in which we can filter on in the report.

But wait! Before we jump to the report there is one more thing that needs to be done.

As you may know, the Process will only fire when new or edited records meet the Rule Criteria; As stated in the Evaluation Criteria. However, we want to report on all existing records, which means we need to do an ‘Empty update’ using Anonymous Apex to fire the Process on all historical Order records.

Admins, it’s time to get your Dev cap on with this neat little trick.

Anonymous Apex to Fire off  the Process.

First of all go to YouName>DeveloperConsole>Debug>Open Execute Anonymous Window. A blank Apex Code screen will open up

Basically, the following Apex code is going to show you a list of all orders in your org and will perform an ‘update’ on the Order name (OrderName = OrderName) thus stimulating a ‘False’ update.

List<Order> allOrders = [SELECT Id, Name FROM Order];

for (Order myOrder : allOrders) {
myOrder.Name = myOrder.Name;
}

update allOrders;

System.debug(allOrders.size());

Then click Execute.

Once this has been executed we can then continue to FINALLY create the report using the new field in a filter –

Hope this helps folks, and remember to vote up the ideas!

See you next week.

Jenny