Reporting on Null Values

  • 10 November 2015
  • 6 replies

Userlevel 5

Wondering if this is possible. One of the reports I want to create for the execs is for them to see accounts who are renewing in the next 60 days that perhaps have not started working on a particular Strategy 2 and so on. I can see accounts that have strategies but cant bring back all accounts that do not have strategies active.

Any ideas?



6 replies

Userlevel 6
Hi Naquiyah, the viability of reporting on nulls depends on how your data is structured. 

In your example, the 'Strategy' data could take two forms in association to the Account:
1. 'Strategy' data is a field on the Account record: In this scenario there's simply a field on the Account Object that holds the data about the strategy. This should be easy to report on because you can just report on the Account object and put a filter on this field for null/not null.
2. 'Strategy' data is stored in a separate object with a child relationship to the Account object: In this form, the 'Strategy' data would have the same form and relationship as the 'Opportunity' or 'Contact' object to the account. If an Account has executed a Strategy, it will exist as its own child-record to the Account. In this scenario, it will be very difficult to report on Accounts that don't have a strategy because you are trying to report on [i]the absenceof records. It's similar to trying to find Accounts that don't have any associated Contacts.

The sneaky way to get around scenario #2 would be to create a 'roll-up' strategy field on the Account:
- Create a field to hold the # of strategies an Account has on the Account (or CustomerInfo) Object. I'd probably recommend this being a # type field but it depends on the information you want to capture.
- Create a rule in rules engine based on the 'Strategy' object
- Pull the 'Strategy' ID field into the 'Show' box on the rule and put a 'COUNT' aggregation on it
- Create an Action to write the COUNT of Strategy records into the Account/CustomerInfo object
- If there are 'strategies' associated with the Account, the rule will write the # of associated strategies into the field. If there aren't any associated strategies, then the field will remain null
Then in your report, you can report on the Account object and use 'roll-up' field as a filter

That was along response, so let me know if that answers the question!
Userlevel 7
Just adding to Marie's response, there are two more potential ways to get there:
  1. Using a "Roll-Up Summary" field to extract a count (full or filtered) of records from the child object
  2. Using MDA if both objects are synced (can dive into this option, if we have the data in MDA)
Userlevel 5
Hey Manu and Marie

Thanks for both your responses.

I may have gotten the strategy field idea wrong here.

So this is regards to Success Plans. We want to track users that are not adoption this option and therefore do a number of accounts with 0 Success Plans activated.

I believe the option highlighted above could still work on the rollup part.

So if i heard you right you basically mean to

1) Create a custom field in SFDC
2) Adding this field to the Account Object as a number field
3) Create a rules engine to allow the aggregation by count of the field (in this case Success Plan Type Name) which is under the CTA Group Object.
4) In the reports 2.0 we can roll this up and therefore show all accounts with 0 Success Plan Type Name created under them.

In regards to MDA Manu this is the CTA Group data source where the field Success Plan Type Name count is needed and don't think this in MDA as its a new feature.

I'll start working on this and cross my fingers hoping its not as complicated as my brain is making it to be :)

Thanks again

Userlevel 5
Hey Marie

So I created the steps you mentioned for option 2 by the tee but looks like its counting the number of success plans on active vs draft. below I have the filter for the Number of Success Plans field and I do get numbers but the nulls that are here are for the plans that created but not moved from draft to active.

I want to understand which accounts have not even created success palns to begin with and therefore are blank.

Yes if GS had a function where we could prepopulate all accounts with a set of success plans we believe users should start working on then we could report active vs draft.

So back to square one unless im missing something.


Userlevel 6
Hi Naquiyah, Try building your report on the CustomerInfo object instead of the CTAGroup object. If that doesn't work, can you post screenshots of your rule config?

Userlevel 5
Nice works thanks!