Dynamic Tables in CRM Analytics

In this awesome blog, written by Rikke, we learned about the union statement, and how can use it to create a multi-dataset query to display different data grouped by a common field (in this case, Account) in a stacked bar chart. The next step would be: what if we want to go further, and explore this feature to the most granular level, in a table? Is there a way to display the bar segment records in a values table when each bar segment is a different dataset? This is what we are going to learn today.

What are we going to build today?

We will build a multi-dataset query that powers a stacked bar chart and a details table. What’s so special about it? Well, when we click on a single segment of the stacked chart, we’ll see results in the detail table as per the selection made. Also, the table will dynamically display each different dataset’s columns and rows depending on the bar segments we select on the stack bar chart. We will not use the faceting behavior of CRM Analytics widgets so we can completely hide the table when no bar segments are selected.

I have used three datasets, with grains at Contact, Cases, and Opportunities. The queries in the blog use these datasets. You can download the CSVs and create datasets in CRM Analytics.

Please follow the steps below to build our solution.

Step 1: Create Stacked Bar chart

First, we’ll create a blank dashboard. Now, let’s create our multi-dataset query. Click the blue button “Create Query” and select one of your datasets to start. Then, in the query edit mode, select “Add Dataset” at the top left and add the other two datasets to your query.

Let’s now figure out how many Opportunities, cases, and contacts exist for each account. You can either follow the query below or go through aforementioned Rikke’s blog for more details:

q = load "Opportunity_dataset";
q = group q by 'Account';
q = foreach q generate 'Account', count() as 'count', "Opportunities" as 'Record Type';
q1 = load "Case_Dataset";
q1 = group q1 by 'Account';
q1 = foreach q1 generate 'Account', count() as 'count', "Cases" as 'Record Type';
q2 = load "Contact_Dataset";
q2 = group q2 by 'Account';
q2 = foreach q2 generate 'Account', count() as 'count', "Contacts" as 'Record Type';
z = union q,q1,q2;
z = group z by ('Account','Record Type');
z = foreach z generate 'Account', 'Record Type', sum('count') as 'count';
z = order z by ('Account' asc ,'count' desc);

Change the visualization to a stacked bar chart, and you’ll have this:

Save the query with the label you prefer, and disable the “broadcast selections as facets” checkbox.
After saving your stacked bar query, in the query properties tab, select “multiple selection”, so we can filter our table by more than one account at a time.

Step 2: Create the Table

Now, let’s create the table that will display our bar chart segments’ records. It’s also a multi-dataset query, although the syntax will be a little different:

q = load "Opportunity_dataset";
q1 = load "Case_Dataset";
q2 = load "Contact_Dataset";
z = filter q by 'Account' == "Account 3";
z = foreach z generate 'Account', 'Name', 'Close_Date', 'Stage', 'Amount','Type';
z = order z by 'Amount' desc;

Notice that we are loading the three datasets, but only projecting one of them, using the variable in the filter line. In this case, we used the “q” variable to load the opportunity dataset. So, to be coherent, we must project the opportunities fields in the projection line.

Insert the resulting table in the dashboard, below your stacked bar chart.

Step 4: Let’s do the bindings

To display the data in the table dynamically, we need to make use of interactions (fka bindings), and we also must make sure only one bar segment is displayed each time, so our data doesn’t get scrambled. To achieve this, we must manipulate the previous table query, so when we click on a bar segment, we project it in the table:

• The correct variable for the filter line
• The correct account(s) filter for the filter line
• The correct fields for each different bar segment (dataset) we want to display
• The correct field for ordering our table

For this, we’ll need to create columns in our stacked bar query:

q = load "Opportunity_dataset”;
q = group q by 'Account';
q = foreach q generate 'Account', count() as 'count', "Opportunities" as 'Record Type', "q" as 'Variable', "'Account', 'Name', 'Close_Date', 'Stage', 'Amount', 'Type'" as 'Fields', "'Amount'" as 'Order';
q1 = load "Case_Dataset”;
q1 = group q1 by 'Account';
q1 = foreach q1 generate 'Account', count() as 'count', "Cases" as 'Record Type',"'Account', 'Name', 'Created_Date', 'Status','Reason','Close_Date', 'Account_Industry'" as 'Fields', "q1" as 'Variable', "'Created_Date'" as 'Order';
q2 = load "Contact_Dataset";
q2 = group q2 by 'Account';
q2 = foreach q2 generate 'Account', count() as 'count', "Contacts" as 'Record Type', "'Account', 'Account_Industry', 'Active', 'Age', 'Email', 'Last_Activity_Date', 'Name', 'Preferred_Contact_Method', 'Title'" as 'Fields', "q2" as 'Variable', "'Last_Activity_Date'" as 'Order';
z = union q,q1,q2;
z = group z by ('Account','Record Type');
z = foreach z generate 'Account', 'Record Type', sum('count') as 'count', first('Fields') as 'Fields', first('Variable') as 'Variable', first('Order') as 'Order';
z = order z by ('Account' asc ,'count' desc);

Note: Pay attention to your data stream names and which variable contains the data from which dataset. This is what is going to display the correct data in our table when the bars are selected.

We created 3 new columns: 

• The column ‘Fields’, contains a string with the fields we want to display in our table. Notice that the fields are different for each dataset loaded, but they are projected as a single string in the ‘Fields’ column. 
• The column ‘Variable’, contains the variable we’ll use to filter our table query line. 
• The column ‘Order’ selects the field we are going to order our table depending on the selected bar segment.

Now, we’ll use these columns to manipulate our table’s variable (dataset), account filter, fields projected, and order.

Open the table query in the advanced editor mode and let’s edit it with our interactions:

q = load \"Opportunity_dataset\";
q1 = load \"Case_Dataset\";
q2 = load \"Contact_Dataset\";
z = filter {{coalesce(cell(lens_1.selection, 0, \"Variable\"),\"q\").asString()}} by 'Account' in {{coalesce(column(lens_1.selection,[\"Account\"]),\"[]\").asString()}};
z = foreach z generate {{coalesce(cell(lens_1.selection, 0, \"Fields\"),\"Account\").asString()}};
z = order z by {{coalesce(cell(lens_1.selection, 0, \"Order\"),\"Account\").asString()}} desc;

Notice that, since we are using selection bindings, if no bar segment is selected, the table query will result in an error, so we used the coalesce function to set default values for our bindings. 

Note: We are using a values table to display data to the most granular level, if limits are not applied to the query, depending on your data size, you can have performance issues with your dashboard. The default limit for the values table is 100 rows.

Now we are good to test it!

Step 4: Let’s test the solution

Save your dashboard and set it to preview mode. Your table will display no result when no bar segment is selected. Test the dash by clicking on the bar segments, and see your table displaying the records for that segment! You have done it!!

Pro-tip: If you want to change the labels of the fields displayed in your table, use the dataset XMD for it. Check the first dataset you loaded in your table query (in this case, opportunities). You need to insert all the fields from the other datasets as derived measured/dimensions in this dataset XMD so you can relabel the fields.

Written by

Phillip Schrijnemaekers

Tableau CRM consultant at riverBI

See author's posts