Solved

With and Without Report Functionality

  • 15 April 2022
  • 2 replies
  • 25 views

Hello,

My management team has asked for a report that shows which Companies have no Timeline activities with with the  type of Meeting in the past 60 days.  In SF you can create a report type of with and without, I have not found that kind of functionality in GS, but I’m sure there is some way to do it.  Can anyone tell me how can I accomplish this? Thanks!

Rhonda

icon

Best answer by bradleymcg 15 April 2022, 17:36

View original

2 replies

Userlevel 6
Badge +4

Probably the most straightforward route would be to use Data Designer. 

You will need your Company and Activity Timeline objects.

 

Make sure on Timeline to at least pull in:

Created Date

Meeting Type

Activity ID

Company>GSID (make sure you expand your company lookup on the Timeline Activity object and select the GSID field for this)

 

You will need to do a transformation step on the Timeline Activity to find your MAX activity Date, and since you are looking for a specific meeting type, you will want to group by that field and the Company GSID field.

 

You will need to merge this data set back to your original Activity timeline query with an inner join on

Activity ID.

 

The reason for this is so you can pull in fields like subject, owner, etc. from timeline and not have to set them to ‘Count’ or ‘Max’.

 

This data set should be merged with your Company object with a left join on the Company object (that is, retain all records in Company object and only Timeline records that match your join condition).

For your join condition, you can use the Company GSID (make sure you expand your company lookup on the Timeline Activity object and select the GSID field for this). 

 

To know if a company has or has not had an activity of a certain type, you will look on this last merge to see if ActivityID (from your Activity Timeline data set) is populated. 

 

There is some more filtering and transforming you can do to make reporting tidier, and you may have to play around with this to fit your needs (I’m doing this mostly from memory) but that is hopefully enough to get you started.

@bradleymcg Thanks so much for the input.  This did get me started.  I don’t have it completed quite yet, but am getting there.  I appreciate you taking time to respond :). 

Reply