In the first part of this series, we discussed developing an anonymous leaderboard at the individual contributor level. We created a bar chart that caused the dashboard to show the user who was viewing the dashboard their information, and make everyone else in the leaderboard anonymous. If you haven’t read part 1, check it out here.
Now, imagine you’re a manager who goes in to view the dashboard. You don’t have any opportunities in your own name, but you manage a team of 4 reps. If you pull up the dashboard we created last time and take a look at the leaderboard, you’ll just see a list of anonymous rep names in a bar chart. That’s no good! You want to see where all the reps you manage are in the leaderboard!
Fear not, we have a solution for this! This time, we’re going to start off in the data recipe using the same dataset we created our last leaderboard with. The image below shows the recipe I used for this example:
The recipe is simple and includes only two joins: a self-join of the manager to the user they manage via the ManagerId, and a join of the user to their opportunity via the OwnerId. What we’ll be focusing on here is the transformation between the two joins, where we will “flatten” the hierarchy between the user and their manager.
What a flatten transformation does is gives you a list of all users who show up in a specific hierarchy. For example, if we use the flatten on the User’s Id, the flattened field will give you a value consisting of something like this: “User’s Id/Manager’s Id/Manager’s Manager’s Id.” We call this a path. To add this transformation, click on the transformation node following your manager to user join and select the “flatten” button from the top bar.
The below image shows the settings chosen for the transformation. We’re going to want to flatten the user’s hierarchy with their manager, which is why we chose User ID and Manager ID as the Record and Parent ID columns. This will give us the path we’re looking to create.
Let’s run our recipe and open up the dataset we created to see if the path field looks how we’d expect.
Open up the opportunity dataset you’re using and open a new lens. Let’s go to a compare table, and group by Owner.Name, Owner.Id, and Owner.Id_Flatten. We should be seeing something like this, where some users may have just one id in their path while others may have more than one separated by a “\”.
This solution will allow us to filter to show the individuals in the leaderboard for only the users where the manager’s id appears in a user’s id path.
Now that we have that information, we’ll take a look at the query we created last time. We created two data streams, one for the logged in user and another for all the other users anonymized. See the previous query below:
This time, instead of looking at the Logged in User, we’re going to look at the user hierarchy path we just created.
Before we do this, we’ll need to make sure our Salesforce Direct Query is working as expected. If you didn’t complete the tutorial in the last post, you can create a Salesforce Direct Query by clicking anywhere in the dashboard and select “Create Query” in the top right.
Select “Salesforce Object” and then find the User object. Let’s add a filter to this query by selecting ‘Id’ equals any Id from the list. Like last time, we’re going to have to replace it. Click query mode and replace the Id we just selected “!{User.Id}”. This will allow us to look up the user who logs into the dashboard’s Id, and use it to fitler our leaderboard.
To do this, we’ll need to replace the ‘Opportunity.Owner’ == “Logged in User” with the following query: ‘Opportunity.Owner.Flatten_Path’ matches “!{User.Id}”. Previously our query looked at whether the logged in user was the Opportunity Owner. Our new query will look at whether the logged in user is within the hierarchy of the logged in individual. Because of this, the users will see all the users they manage in the leaderboard, while keeping the remaining bars anonymous.
Make sure to use “(‘Opportunity.Owner.Flatten_Path’ matches “!{User.Id}”)” for the logged in user and “!(Opportunity.Owner.Flatten_Path’ matches “!{User.Id}”)” for all other users. This ensures that we filter out the users whose ids aren’t contained in the hierarchy for all other users, and keeps those users when the manager logs in.
Take a look at the new query below:
Let’s go back to our dashboard and test this out. Log in as a manager that you know has some reps below them, and take a look at the dashboard. For myself, I’ve assigned myself as the manager of Laura Garza, Irene Kelley, Bruce Kennedy, and Chris Riley. Take a look at the chart now and you’ll see that only those four names appear in the leaderboard, while the rest of the users remain anonymous:
Great work! Now managers can only see their team’s information when they log into the dashboard!
But wait a second, what if Irene Kelley logs in to the dashboard? Since we removed the previous filter for Owner.Name = “!{User.Name}” won’t she just see an anonymous list?
Think again! Because we set up the hierarchy to show the user’s information if they appear in the hierarchy path, if Irene logs in, she’ll be able to see anyone who appears below her in her hierarchy. Since she doesn’t manage anyone, she’ll just see her information, making this a well-rounded solution for any user!
Like our content? Learn more about how we can help you get the most out of Tableau CRM.