Why are my drills on running_total showing only that row? (Community)

View the original community article here

Last tested: Aug 6, 2020
 

running_totals are post-generated after the query is run, and so when we drill, we only see the results generated for that row in the backend SQL Query rather than what we're seeing on the front end.

There is a way to create a drill though through the html/link parameter with some URL hacking!

These are the steps I followed for this workaround:

 

1. "Build out" the explore you want in the drill modal. i.e if the user clicks on the running_total and they want the drill to show ID and User Name, build out a new explore with just ID and User Name.
2. Apply a Custom Filter to this new Explore; since running_totals capture the current month and all months before it, we'll want to translate that into the filters for our drill. I set matches advanced on the date field and set two place holder date values:
   
   
3. Once you have the explore built out and have the generic filter, click the Gear by the Run button, select "Share" and grab the "Expanded URL".
4. Take the expanded URL and decode that bad boy; we're gonna need to use this in the "link" or "html" parameter
5. In the decoded URL, we should see the filter populate to something similar like: &f[order_items.returned_date]=before+2020/08/06,2020/08/06&limit...
   
   We'll want to take the 2020/08/06 and replace em with the Liquid value of the date field we're trying to drill into on the main explore. So something like:
   &f[order_items.returned_date]=before+{{ order_items.returned_date._value }},{{ order_items.returned_date._value }}&limit...
6. Next, create a link parameter and use a relative link for our newly hacked URL
   link: {

label: "click me 3"

url: "/explore/andrew_ecomm/order_items?fields=order_items.id&f[order_items.returned_date]=before+{{ order_items.returned_date._value }},{{ order_items.returned_date._value }}"

}

This should allow the drill to carry over the dates as expected! Note, the only thing we may need to keep in mind is passing in existing filters into this new link!