function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Philip BriningPhilip Brining 

PrevGroupVal & ParentGroupVal

I've got a report I am trying to write and I need to use one or other of Prev/Parent Group Val custom formlae I think.  I've read the SFDC help and resources on this forum but still can't get my head around what I need to do to get this to work.

I have a matrix report Accounts with Orders and Products.  Orders is a cusom object called Order.

Account has a custom field called Region__c which is a pick list and represents the region that an account is in.
Order has a custom field called X9L_Equiv__c which reprents the 9 litre equivalent of an order.  We're talking alcohol here - the 9L= of a 12 x 750ml case of Gin is 1: the 9L= of a 6 x 500ml case of Cherry Vodka is 0.33.  An order containing 6 cases of Cherry Vodka would have a Count of 1, Quantity of 6, 9L= of 2.  Hopefully I've clarified not complicated!

My matrix report is like this:

                    MONTH 1                                         MONTH 2                                  Grand Total
Region         Gin      Cherry Vodka        Total       Gin      Cherry Vodka     Total    GT
London           2             5                         7           6               4                   10        17
South                             3                         3           2               5                     7        10
North              8             1                         9            3                                     3         12
GRAND TT   10            9                        19          11              9                   20        39

What I'd like is
                     MONTH 1                                         MONTH 2                                  Grand Total
Region         Gin      Cherry Vodka        Total       Gin      Cherry Vodka     Total    GT
London          5%            13%                   18%     15%               10%          26%      44%
South                               8%                      8%       5%               13%          18%      26%
North              21%            3%                   23%                             8%            8%      31%
GRAND TT   26%          23%                     49%    21%              31%            51%    100%

I cannot figure out the syntax of the two formulas in respect of referencing fields that I want to group by.

PARENTGROUPVAL(Order__c.X9L_Equivalent_of_Order__c:SUM, ACCOUNT.REGION__c, PRODUCT.NAME) gives me a field does not exist error on ACCOUNT.REGION__c

Hope this makes sense and someone can help me.
Thanks

Jason Curtis NBSFDGJason Curtis NBSFDG
Hi, Philip, the 2nd & 3rd parameters of the PARENTGROUPVAL should be the part of the report (row/column) that you using for your calculation.
PARENTGROUPVAL(summary_field, parent_row_grouping, parent_column_grouping)
Can you do a screenshot of your report?
For instance, here is a formula I used to calculate how much percentage of sales each month. I'm taking the sum of cases ordered and dividing by the total from the summary fields.
Order_Item__c.Cases_Ordered__c:SUM/PARENTGROUPVAL(Order_Item__c.Cases_Ordered__c:SUM, ROW_GRAND_SUMMARY, COLUMN_GRAND_SUMMARY)
Philip BriningPhilip Brining
Thanks for your input Jason.  Attached is a screen shot of my SFDC report
So the only fields you can work with are those that are summarised or numerical - I was trying to reference Region (Picklist).  I'm still not sure what to do to get what I want.
Thanks
Phil
User-added image
Philip BriningPhilip Brining
I need to try to get a report which shows the sales for each Region in each month by product expressed as a percentage of the sales of that product in that month across the entire UK.  Below is an excel sheet.  i.e. London had 33% of all the Aquariva sales in July 2013.  We're reporting on 9L equivalents not record count, not quantity  and not sales value.  Hope this makes sense.
Thanks
Phil

User-added image
Jason Curtis NBSFDGJason Curtis NBSFDG
I worked in the wine business for years, so 9L equivalents make sense! 
  • You need to make a matrix report to acomplish this (like your screenshot shows)
  • One important note, you'll need to have the 9L equivalent qty in a field on the object (if you have a product object for instance that has a field for bottle size in ML, and a case count, you can make 9L a formula field based oon those two). You don't want to try to calculate 9L on the report itself (and, I don't think you can). (Looking at your screenshot looks like you have this.)
  • On your report you might need a filter for country, if for instance you have sales in multi-compaines but you only want to report on regions in the UK.
  • The way you have your region and product look good. Another side-note, if you don't like when it says Product: Product Name you can change this label in the Report Type that this report is using.
  • Do you want to have columns for every day there was sales?
  • Add a custom summary field into the body of the matrix
  • Give it a name
  • Format: Percent
  • Select At a specific row/column grouping level
  • For A chose Product: Product Name
  • For B chose Order Month
  • Then the formula you'll want to enter
    • From Summary Fields choose 9L Equivalent and insert it
    • Divide by "/"
    • Then insert the PARENTGROUPVAL
      • The first value should be the 9L Equivalent summary field
      • Second will be Region (use the selectors when the formulat insert provides to choose)
      • Third will be Order Date
  • This works for me and creates a report that shows Region and Products along the side
  • The sum of cases sold for the Order Month
  • Underneath that shows percent that those cases order represent for the region as a total

Let me know if that works for you!
Philip BriningPhilip Brining
Hi Jason, thanks so much - I got there in the but would have made no progress without your help and input.  This is what I ended up with.  ParentGroupVal settings

The report now looks like this:

User-added image

I have verfied that the results are correct - 97% of Berkeley Square Gin orders were in London in September 2013, adn the remaining 3% were in Scotland.

Many thanks
Phil
Jason Curtis NBSFDGJason Curtis NBSFDG
Phil, glad you got where you needed to be. you probably know this but you can remove "record count" from a matrix report under the "Show" menu drop down next to Add Chart when customizing a report.
Also, not sure how to remove "#Error". I think if you filter on orders that only have values that might do it.
And thanks for sharing the challenge, forced me to dig deeper into some areas I haven't looked that hard at!
Philip BriningPhilip Brining
Thanks Jason - yes I ended up taking out Qty, and Record count - thanks for the tip.  I'm sure the Error could probably come out the way you have suggested.  Not sure how to flag this question up as "Answered"...
Thanks
Phil
Jason Curtis NBSFDGJason Curtis NBSFDG
You know, I've never asked a question on the forum, only answered them, so I'm not exactly sure how it looks, but there should be someway to mark this thread "Solved" so that it doesn't show up in the unsolved view.
SteveMo__cSteveMo__c
+1 Jason Curtis NBSFDG (nice job!) 

The #Error is the result of a Divide by Zero error in your formula, basically you're "breaking The Laws of Math" to remove the #Error, you'll need to amened your Formula with an "exit" that basically says IF(Value = 0, 0, else continue on with the rest of the formula...   
SteveMo__cSteveMo__c
@Jigen San 

Of all the things to do in the world, literally ALL of the the options available   

(here's just a few)    

Help an eldery person
Teach a child to read 
Donate to a food pantry
Plant a tree

Instead, you chose to be a Spammer  

Your parents must be so ashamed of you