I'm attempting to create a report that pulls in company data along with activity timeline data that would show the notes for the latest activity of type 'X'. In order to get the maximum activity date for that specific activity type, there has to be a 'by' clause but then that prevents one from bringing in any string fields, specifically the notes field, which I realize can't be included in a report if there's a 'by' statement.
My use case for this is that i'm trying to replicate the customers tab in a report, since that tab will soon be deprecated.
Thanks for any information you may have.
Page 1 / 1
I have a CSM with a similar ask. Wanting a report of only her [latest] Timeline Entries - for the accounts she is posting on. I've been able to create the report that shows me her last entry for an account - but as soon as I add Subject, it includes all entries - rather than the latest [max]. Following this post. 🙂
I got around this by adding some fields to Customer Info. For example, we have a Training activity type, so I created a field called Last Training, and I'm using the Rules Engine to populate that field based on the last date an activity of type Training was logged. Then, I can display this value in reports or currently the Customer's tab.
This is useful. However, in addition to the knowing when the last training was, I'd like to see any comments that the CSM had about it.
Hi Shane, in this case you would need to accomplish this via a Bionic rule and an MDA table, as every additional field you add in the "by" area is used to create a composite selection for what is considered unique.
Here's what I mean, if you ask the report to Show me: Max of Activity Date, By: Account Name, you'll get one record per Account Name.
But if you ask the report to Show me: Max of Activity Date, By: Account Name, and Subject, you'll get one record per unique combination of Account Name AND Subject, which will most surely be more than one record per Account.
Add any other fields to the By: area and the idea of pulling the most recent data becomes untenable.
What you need to do in the Bionic rule is first fetch the most recent timeline entry (filtered for the type of activity you need) for each account with a simple fetch that pulls Account ID and Max of Activity Date (with appropriate filters). This will give you one date per Account ID. Then fetch the details that you want to appear in your data in another data task. Merge those two together. Then optionally perform another fetch to pull in all accounts (if you want to show blank values for accounts that did not have any entries of the type you want to show), and then merge that all account data with your merged data from step 3.
It would look something like this (fetch newest instead of oldest in this example):
Here's what I mean, if you ask the report to Show me: Max of Activity Date, By: Account Name, you'll get one record per Account Name.
But if you ask the report to Show me: Max of Activity Date, By: Account Name, and Subject, you'll get one record per unique combination of Account Name AND Subject, which will most surely be more than one record per Account.
Add any other fields to the By: area and the idea of pulling the most recent data becomes untenable.
What you need to do in the Bionic rule is first fetch the most recent timeline entry (filtered for the type of activity you need) for each account with a simple fetch that pulls Account ID and Max of Activity Date (with appropriate filters). This will give you one date per Account ID. Then fetch the details that you want to appear in your data in another data task. Merge those two together. Then optionally perform another fetch to pull in all accounts (if you want to show blank values for accounts that did not have any entries of the type you want to show), and then merge that all account data with your merged data from step 3.
It would look something like this (fetch newest instead of oldest in this example):
I do enjoy how you come up with solutions Dan! I have a question - would this then be turned into a 'report' that can be seen on a Dashboard, but not updated in Real Time, but only updated every 2 hours?
That's correct, Sagan. The update of the data could be at most frequent, every 2 hours, based on rule scheduling.
Thanks Dan! This should be a fun one to implement, always appreciate your help.
Hey Dan, on the initial fetch of the latest activity, if I don't include a filter (aka I want to pull the latest activity regardless of the type) with the three fields being Account ID, Activity ID and MAX of Activity Date it brings in multiple records per account. Is that because I'm including the Activity ID? I do that in order to bring additional activity information.
Hi Shane,
Sorry for the slow reply - I was out on PTO last week.
Yes, the reason you are getting more than one record per Account ID is because you are including the Activity ID, which makes your "uniqueness criteria" for looking for MAX of Activity Date the combination of Account ID and Activity ID.
In that first fetch, just include the Account ID, so you get one record per account ID.
That's why in my example I have a second fetch for grabbing the rest of the details of the specific activity (including the activity ID). Then merge this back with the oldest record with a left join where account ID and date/time stamp match. This will allow you to now have the activity ID (and other details) for the single record per account that you pulled in the first merge.
Sorry if this seems a little tricky, but data management when you're doing aggregations, merges and transformations needs to be very precise otherwise you'll get the wrong data. Ironically, the computer is very good at doing EXACTLY what you tell it - even if what you tell it isn't really what you intended to tell it. ;-)
Sorry for the slow reply - I was out on PTO last week.
Yes, the reason you are getting more than one record per Account ID is because you are including the Activity ID, which makes your "uniqueness criteria" for looking for MAX of Activity Date the combination of Account ID and Activity ID.
In that first fetch, just include the Account ID, so you get one record per account ID.
That's why in my example I have a second fetch for grabbing the rest of the details of the specific activity (including the activity ID). Then merge this back with the oldest record with a left join where account ID and date/time stamp match. This will allow you to now have the activity ID (and other details) for the single record per account that you pulled in the first merge.
Sorry if this seems a little tricky, but data management when you're doing aggregations, merges and transformations needs to be very precise otherwise you'll get the wrong data. Ironically, the computer is very good at doing EXACTLY what you tell it - even if what you tell it isn't really what you intended to tell it. ;-)
A report layout is different from the timeline layout which is what we would like add to the c360 so our exec team does not have to search for it.
Hi @aparimala - If I understand the “Section/Embed Page” section of a C360 properly, I don’t believe that your desired output could be designed soley in Report Buillder. You’d have to use the Rules Engine to do transformations on Timeline Activity data first and output a new dataset that could then be reported on. A potential workaround could be to feed the latest entry of a specific activity type into the Customer Info / Account object but that could get messy depending on how large the entry is.
You could then create a report off of your transformed and filtered data and show that in the C360 section.
Reply
Sign up
If you ever had a profile with us, there's no need to create another one.
Don't worry if your email address has since changed, or you can't remember your login, just let us know at community@gainsight.com and we'll help you get started from where you left.
Else, please continue with the registration below.
Welcome to the Gainsight Community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.