Skip to main content
I need a report that shows me accounts that have had 0 CTAs fired based on specific filters. There are certain accounts where I know at least 1 CTA should have fired by a certain date, however sometimes due to gaps in logic or one-off situations an account has fallen through the cracks. It appears when I 'count' on CTAs in a report, anything that results in a count of 0 is just excluded from the results, however these are specifically the accounts I want to see. Any way around this limitation?
Hi Tim,





This is tricky case because of the way that joins work in Salesforce (SOQL).  [We implicitly join tables like CTA with Account/Customer Info but this is an Inner Join so you only get data when there is a match on both sides.]





You CAN make it work in Rules Engine using our special feature called 'Absence of Data.'  See a tutorial on it: https://support.gainsight.com/hc/en-us/articles/219043648-Identifying-an-Absence-of-Data-via-Rules


With this feature you can make the query in Rules Engine and then either 'tag' an account with no CTAs (which makes it easy to create the report) or create a table in MDA and write the data you need to create the report.  Or just skip the Report and create a CTA for an Account that doesn't have one. 





Hope this helps!





-Karl
Hi Tim,





Another tedious workaround that you may try is - 





Step 1. Create a number field on "Customer Info" object, name it something like - "#Open CTAs"





Step 2. Create a rule on Customer Info object and load zeros to "#Open CTAs" field


Setup Rule :








Action :








Step 3. Create another rule on "Call to Action" object, load #Open CTAs count to "#Open CTAs" field (Filter this rule to fetch only open CTAs).


Setup Rule : 








Action :








Schedule these two rules in order (Step 2 Rule to run first).





Step 4 : Create a Report On "Customer Info" object and add a filter - "#Open CTAs = 0".








Thanks,


Rajesh
Thanks Rajesh. I think I came up with a solution that was a variant of this.

Reply