Skip to main content
Is it possible to make a graph when there are two "by" fields? I am organizing a metric by CSM name, but I also need to show the same stats for CEM name but trying not to make two graphs for this. I'd like to show CSM and CEM on the whole graph. Is this possible?
Ky, You can have 2 by fields, but it is a primary/secondary. So you could 'by' by CSM and then for each CSM 'by' CEM. Does that make sense? Also if you have such a graph available in excel or something can you please post here or email me gkotak@gainsight.com. thx
Hi Ky, glad to help. You can do this by including both fields in the "By:" section and then apply a pivot on the first metric. You'll get a nice matrix table of data. This example below is reporting on all customers who have a renewal CTA currently open and shows the cross section of CSM and Account Executive.











Then once you have this data you can create some slick visualizations (each of which you can click into to drill further into the data). 

















Is this what you had in mind?
Hi Dan and Gaurav, 


This is really helpful. So, I tried to do the pivot but it is still giving me the same result in my graph which may be because I am not trying to do a stacked column but just a singular column but its skewing everything. 





I have these broken up in to two graphs right now by CEM and CSM, but trying to get it into one graph. Each CSM and CEM should stand along with their own bar. 





If I understand correctly, each confirmed hire has both a CEM and CSM? And you'd like to have each confirmed hire appear twice in the report - once under their CEM's name and once under their CSM's name. Is this right?
Also curious if there is a way to create a dynamic report. So, in regards to this graph: 








If this was a goal for the CSM, and their goal was 20, is there a way I could have another line or some indicator that the goal has been met. Maybe a line that shows the goal line and you would be able to see if the CSM's bar has surpassed it?
Thats correct!
Ok.....so this is a tricky problem to solve because you basically have two different sets of data that you are trying to show on a single chart. One set of data is accounts by CSM owner and the other set is accounts by CEM owner. 





So....you can show it as two separate reports like you've done above. 





Or....if you want to get fancy and show it on a single report, here's how to do this (and I just had a chance to test this in a demo org to validate that it works):







  1. Create a new MDA object to hold a combined data set - support article with a field for account name and owner name.

  2. Create a bionic rule (new feature available in v5.5) to inner join on both CEM and CSM owner.









  3. In the setup action, write using insert to the MDA object you created in step 1, repeating the step for each side of the join





  4. This will give you a combined data set where you can then report using that custom MDA object as the source object:





Note - this is pretty advanced stuff and might be worth taking advantage of Gainsight's admin office hours to do a step by step in real time. 
After Spring Release we are planning to incorporate the concept of goals and this would support your use can mentioned here. 
This is super helpful, thank you! I think this would be worth doing even with the complexity as we will need to get both the CEM and CSM on the same reports going forward. Thanks so much for the help, I am going to try this on my own and if need be take advantage of office hours. Appreciate the help!
Okay, great news. But there is no work around to get something like that to work now?
I would say if you wanted to create an additional field that held the "Goal Number" you could use that to base line.





But, how does that number get determined?





There is a very "kludgy" way to do this. If you follow the instructions I provided above to create a custom MDA object, add a new row to the data called "Goal" and then add an additional step in your setup action in the rule to push this goal number. 





You can even set different goal numbers for the CEM and CSM roles.











Then the raw number and goal appears for each team member in the report if you set the aggregation on the goal number as max type and select visualization type of column line.











🙂
Wayne beat me to it while I was busy building. 🙂
Thanks Wayne and Dan! That goal number is just determined manually each quarter. So this quarter it is 30. So I just need to make goal number be 30.
lol, yours was definitely more in depth though. 





Thinking about what that 'Goal number' should be. If there is no set number perhaps doing a customer wide average (benchmarking it basically) and using that as a base line for every customer 'goal line' is a good direction. 





Also, I'm worried that your step three may need an adjustment. ***Edit*** you have it setup right. But, instead of an insert I would upsert both utilizing the Account name, user, and a hard coded role field (to account if the same user is both owner and csm) as your identifiers. This way if the CSM changes you are not creating a whole new record.





Let me know if you need to go more in depth, I can take screen shots and post them.
Yeah, I had some bad naming convention in the first set of steps. The rule does push them into the same column using insert instead of update or upsert.





If you take a look at the last report I had updated the name of that field in the MDA object to be account owner instead of CSM:











Is this what you had in mind? Or did I miss something different?
Nope, you are right. I modified my original post. But, I would do an upsert instead of an insert incase a CSM changes.
I think you might have a problem if you do an upsert as the data structure implicitly requires duplicates as the relationship from account name to account owner is 1:M.





For example, see the example below where for the same account name we have two "owners" each with a different goal number (since they have different roles):











An upset would overwrite the older of the two entries, I believe.





Since CSM and CEM assignments might change, it would make sense to periodically flush the table and just rerun the rule that populates it so that the data is always fresh.
Correct, unless you make use of the 'role' field. I built it out for screen shots. Also, not sure if bionic rules is necessary. Does the owner and CSM live on the same object? Anyhow here is how I set it up.





1) Fetching all of the data.








2) Pushing the Data for CSM, notice the identifier for the Account ID and Role(Hard Coding CSM).











3) Pushing the data for Account Owner, both account and role are identifiers again but, this time role is 'Account Owner' not 'CSM' since it does not match it will push a new record.








4) Your output looks like this








This gives you the ability to run the rule daily and never have to wipe it. 
That is a killer solution, Wayne! Love it!!





Reply


Terms & ConditionsCookie settings