Skip to main content
Topics#Reports & DashboardsPregunta de Philipp Mathis
Philipp Mathis preguntó en #Reports & Dashboards
30 oct 2019, 10:11

filter on certain parent account attributes without deluting child records in reporting

Hello

 

We are using the concept of target accounts and can label an account record as "target". But since we are using account hierarchies, it gives us a hard time when reporting.

 

As e.g. a parent account can be target but that account has several child and maybe not all of them are targets.

 

But when running a report to see/get all the parent account that have the rating target but still also would see all their child ->and here whether they are target or not, then obviously I have a problem. Since I can apply the filter only globally, which then will exclude child accounts that are not marked as target.

 

Is there a way to solve this? Ideally I'd like only to filter on the parent account target=true but not on the child account...but as they are the same object, I dont know how to achieve this.

 

Thanks for your help,

 

Philipp
19 respuestas
  1. Eric Praud (Polsource)
    1 nov 2019, 14:08
    Hi Philip,

     

    I've come too far to give up now!!!! ;)

     

    So this time I actually tested my solution, and you're right, that doesn't work, so scrap what I said, here's another way to do it. It should work if I understand your requirement properly:

     

    I created 10 test accounts and related them all in 2 groups. Accounts 1-5, where 5 is the ultimate account, and Accounts 6-10 where 10 is the ultimate account

     

    Account 10 has a Hot Rating, whereas Account 5 hasn't.

     

    I created this formula that returns text (I called it Ultimate Parent Rating):

     

    IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Parent.Name)),TEXT(Parent.Parent.Parent.Parent.Parent.Rating),

     

    IF(NOT(ISBLANK(Parent.Parent.Parent.Parent.Name)), TEXT(Parent.Parent.Parent.Parent.Rating),

     

    IF(NOT(ISBLANK(Parent.Parent.Parent.Name)), TEXT(Parent.Parent.Parent.Rating),

     

    IF(NOT(ISBLANK(Parent.Parent.Name)), TEXT(Parent.Parent.Rating),

     

    IF(NOT(ISBLANK(Parent.Name)),TEXT(Parent.Rating),

     

    TEXT(Rating))))))

     

    I then ran a report grouped by Ultimate Parent Account with this filter only (the rating formula does not need to be in the report):

     

    Ultimate Parent Rating equals Hot

     

    Here's the report without filter so you can see that Account 5's rating is not Hot:

     

    Hi Philip, I've come too far to give up now!!!! ;) So this time I actually tested my solution, and you're right, that doesn't work, so scrap what I said, here's another way to do it.

     

    Here's the report after the filter:

     

    User-added image

     

    I hope this is what you wanted

     

     
Escribir una respuesta...
0/9000