Author: Won Park @wonkyoung_park
The Help Center article More Powerful Data Drilling provides a detailed overview of how to create custom drills using liquid variables. The Visual Drilling section outlines how to provide users with a visual drilling experience (other than the default data table) by using Looker’s visualization settings in a URL.
This article provides supplemental instructions for how to obtain those visualization settings and apply them to a drill field in LookML.
drill_fields
parameter within the definition of the field, and specify the fields to be included in the drill visualization.Copy the part of the escaped output after `&vis={`
and before `}&filter_config=`
"[this is where you enter the vis config details]"
: with the escaped string from step 9: link: {
label: "Show as line plot" #or your label of choice
url: "
{% assign vis_config = '{
[this is where you enter the vis config details]
}' %}
{{ link }}&vis_config={{ vis_config | encode_uri }}&toggle=dat,pik,vis&limit=5000"
}
Hello @Lobo-Google
First of all thanks for this post, this is becoming really helpful for me at the moment. However I do have some questions that perhaps you can help me with?
While following your instructions, I have now come up with the escaped string mentioned in step 9.
On step 10 you mention that I would have to replace a piece of text between brackets with the escaped string but (and forgive me, I'm quite new to looker) I didn't fully catch if I should keep the VIS keyword while copying the escaped string, I also don't know if I should keep the brackets or remove them when replacing that piece of text.
The reason for those two questions is that I am coming up with a very long escaped string and when I paste it, suddenly I end up with a long gray line while at first the text was orange so I think I’m doing something wrong or my string is just simply too long or something.
Can you help me with this !?
Hi @Beto ! Thank you so much for the comment!
We would not want to keep the [ ] around the vis config details. It should look like this:
{% assign vis_config = '{ vis config details }' %}
Also, can you confirm what you mean when you say “keep the VIS keyword while copying the escaped string”?
Thanks again!
Thanks for your reply.
What I mean with the VIS keyword is this
&vis={
that sentence is on the string on step 10 , when going to LookML do I want to keep that sentence along with the config details, or just the config details.
Thank you for the help.
Hi @Beto thank you for clarifying!! For step 10, you can replace the
this is where you enter the vis config details
with the vis config. The text this is where you enter the vis config details
only demonstrates where to place the vis config details.
I hope that helps - please let me know if you have any other questions!
@Lobo-Google Tried doing the same thing and i get a 404 error
Below is how my lookml looks like , can you let me know what i am doing wrong here?
link: {
label: "Show as bar graph"
url: "
{% assign vis_config = '{\"x_axis_gridlines\":false,\"y_axis_gridlines\":true,\"show_view_names\":false,\"show_y_axis_labels\":true,\"show_y_axis_ticks\":true,\"y_axis_tick_density\":\"default\",\"y_axis_tick_density_custom\":5,\"show_x_axis_label\":true,\"show_x_axis_ticks\":true,\"y_axis_scale_mode\":\"linear\",\"x_axis_reversed\":false,\"y_axis_reversed\":false,\"plot_size_by_field\":false,\"trellis\":\"\",\"stacking\":\"\",\"limit_displayed_rows\":false,\"legend_position\":\"center\",\"point_style\":\"none\",\"show_value_labels\":false,\"label_density\":25,\"x_axis_scale\":\"auto\",\"y_axis_combined\":true,\"ordering\":\"none\",\"show_null_labels\":false,\"show_totals_labels\":false,\"show_silhouette\":false,\"totals_color\":\"#808080\",\"type\":\"looker_column\",\"defaults_version\":1,\"hidden_fields\":[\"psl.sum_mkt_val\",\"Date.TodaysDate_date\"]}' %}
{{link}}={{ vis_config | encode_uri}}&toggle=dat,pik,vis&limit=5000 "
}
This is how when i use the drill down and link fields the url is being passed
lookerinstance/looks/99&vis_config=%22x_axis_gridlines%22:false,%22y_axis_gridlines%22:true,%22show_view_names%22:...
Hi @sri75 !! Thanks for the comment! I’m going to cc @wonkyoung_park, the content creator for this post to see if we can figure this out!
Hi @sri75 !! Thanks for the comment! I’m going to cc @wonkyoung_park, the content creator for this post to see if we can figure this out!
Hey Lauren, I figured it out. Looks like the custom drilling works only with measures and not dimensions.
@Lobo-Google @wonkyoung_park, One thing i did oberve here is that when i make a pivot on the second chart for some reason it just doesnt work. I tried multiple charts like bar/column/multiple pie but none of them bring me back the results. If i hit explore from here from the pop up i can still see my chart .
Below is a sample of my code:
link: {
label: "Column Chart"
url: "
{% assign vis_config ='{ \"x_axis_gridlines\":false,
\"y_axis_gridlines\":true,
\"show_view_names\":false,
\"show_y_axis_labels\":true,
\"show_y_axis_ticks\":true,
\"y_axis_tick_density\":\"default\",
\"y_axis_tick_density_custom\":5,
\"show_x_axis_label\":true,
\"show_x_axis_ticks\":true,
\"y_axis_scale_mode\":\"linear\",
\"x_axis_reversed\":false,
\"y_axis_reversed\":false,
\"plot_size_by_field\":false,
\"trellis\":\"\",
\"stacking\":\"normal\",
\"limit_displayed_rows\":false,
\"legend_position\":\"center\",
\"point_style\":\"none\",
\"show_value_labels\":false,
\"label_density\":25,
\"x_axis_scale\":\"auto\",
\"y_axis_combined\":true,
\"ordering\":\"none\",
\"show_null_labels\":false,
\"show_totals_labels\":false,
\"show_silhouette\":false,
\"totals_color\":\"#808080\",
\"type\":\"looker_column\",
\"defaults_version\":1 '} %}
{{link}}&vis_config={{ vis_config | encode_uri}}&pivots=SecurityClassifications.barl4_indust_name&toggle=dat,pik,vis&limit=5000&column_limit=15"
@sri75 Awesome. Thanks Sri. As you said, for pivoting when appended the pivot dimension, then it worked. Thanks alot
@Lobo-Google : Thanks for the steps. Followed it along and running into LookML validation issue.
Below is my code. Can you please help?
measure: total_episodes_drill {
label: "Test drill"
type: count_distinct
sql: ${episode_id} ;;
drill_fields: [year,quarter,total_episodes_hm]
link: {
label: "Show as line plot" #or whatever label we want
url: "
{% assign vis_config = '{
[{\"show_view_names\":false,\"show_row_numbers\":true,\"transpose\":false,\"truncate_text\":true,\"hide_totals\":false,
\"hide_row_totals\":false,\"size_to_fit\":true,\"table_theme\":\"white\",
\"limit_displayed_rows\":false,\"enable_conditional_formatting\":true,\"header_text_alignment\":\"left\",
\"header_font_size\":\"12\",\"rows_font_size\":\"12\",\"conditional_formatting_include_totals\":false,
\"conditional_formatting_include_nulls\":false,\"type\":\"looker_grid\",\"x_axis_gridlines\":false,
\"y_axis_gridlines\":true,\"show_y_axis_labels\":true,\"show_y_axis_ticks\":true,\"y_axis_tick_density\":\"default\",\"y_axis_tick_density_custom\":5,\"show_x_axis_label\":true,\"show_x_axis_ticks\":true,
\"y_axis_scale_mode\":\"linear\",\"x_axis_reversed\":false,\"y_axis_reversed\":false,\"plot_size_by_field\":false,\"trellis\":\"\",\"stacking\":\"\",\"legend_position\":\"center\",\"point_style\":\"none\",\"show_value_labels\":false,
\"label_density\":25,\"x_axis_scale\":\"auto\",\"y_axis_combined\":true,\"ordering\":\"none\",\"show_null_labels\":false,
\"show_totals_labels\":false,\"show_silhouette\":false,\"totals_color\":\"#808080\",\"defaults_version\":1,\"series_types\":{},
\"show_sql_query_menu_options\":false,\"show_totals\":true,\"show_row_totals\":true,
\"series_column_widths\":{\"look_res.quarter\":228},
\"series_cell_visualizations\":{\"look_res.total_episodes\":{\"is_active\":false}},
\"conditional_formatting\":[{\"type\":\"along a scale...\",\"value\":null,\"background_color\":\"#1A73E8\",
\"font_color\":null,\"color_application\":{\"collection_id\":\"7c56cc21-66e4-41c9-81ce-a60e1c3967b2\",
\"palette_id\":\"56d0c358-10a0-4fd6-aa0b-b117bef527ab\"},\"bold\":false,\"italic\":false,
\"strikethrough\":false,\"fields\":null}],
\"query_fields\":{\"measures\":[{\"align\":\"right\",\"can_filter\":true,\"category\":\"measure\",\"default_filter_value\":null,\"description\":null,\"enumerations\":null,\"field_group_label\":null,\"fill_style\":null,\"fiscal_month_offset\":0,\"has_allowed_values\":false,\"hidden\":false,\"is_filter\":false,\"is_numeric\":true,\"label\":\"RO Detail Total Episodes\",\"label_from_parameter\":null,\"label_short\":\"Total Episodes\",\"map_layer\":null,\"name\":\"look_res.total_episodes\",\"strict_value_format\":false,\"requires_refresh_on_sort\":false,\"sortable\":true,\"suggestions\":null,\"tags\":[],\"type\":\"count_distinct\",\"user_attribute_filter_types\":[\"number\",\"advanced_filter_number\"],\"value_format\":null,\"view\":\"look_res\",\"view_label\":\"RO Detail\",\"dynamic\":false,\"week_start_day\":\"monday\",\"dimension_group\":null,\"error\":null,\"field_group_variant\":\"Total Episodes\",\"measure\":true,\"parameter\":false,\"primary_key\":false,\"project_name\":\"ro\",\"scope\":\"look_res\",\"suggest_dimension\":\"look_res.total_episodes\",\"suggest_explore\":\"look_res\",\"suggestable\":false,\"is_fiscal\":false,\"is_timeframe\":false,\"can_time_filter\":false,\"time_interval\":null,\"lookml_link\":\"\/projects\/ro\/files\/RO%2Fviews%2Flook_res.view.lkml?line=217\",\"permanent\":null,\"source_file\":\"RO\/views\/look_res.view.lkml\",\"source_file_path\":\"ro\/RO\/views\/look_res.view.lkml\",\"sql\":null,\"sql_case\":null,\"filters\":null}],\"dimensions\":[{\"align\":\"left\",\"can_filter\":true,\"category\":\"dimension\",\"default_filter_value\":null,\"description\":null,\"enumerations\":null,\"field_group_label\":null,\"fill_style\":null,\"fiscal_month_offset\":0,\"has_allowed_values\":false,\"hidden\":false,\"is_filter\":false,\"is_numeric\":false,\"label\":\"RO Detail Quarter\",\"label_from_parameter\":null,\"label_short\":\"Quarter\",\"map_layer\":null,\"name\":\"look_res.quarter\",\"strict_value_format\":false,\"requires_refresh_on_sort\":false,\"sortable\":true,\"suggestions\":null,\"tags\":[],\"type\":\"string\",\"user_attribute_filter_types\":[\"string\",\"advanced_filter_string\"],\"value_format\":null,\"view\":\"look_res\",\"view_label\":\"RO Detail\",\"dynamic\":false,\"week_start_day\":\"monday\",\"dimension_group\":null,\"error\":null,\"field_group_variant\":\"Quarter\",\"measure\":false,\"parameter\":false,\"primary_key\":false,\"project_name\":\"ro\",\"scope\":\"look_res\",\"suggest_dimension\":\"look_res.quarter\",\"suggest_explore\":\"look_res\",\"suggestable\":true,\"is_fiscal\":false,\"is_timeframe\":false,\"can_time_filter\":false,\"time_interval\":null,\"lookml_link\":\"\/projects\/ro\/files\/RO%2Fviews%2Flook_res.view.lkml?line=136\",\"permanent\":null,\"source_file\":\"RO\/views\/look_res.view.lkml\",\"source_file_path\":\"ro\/RO\/views\/look_res.view.lkml\",\"sql\":\"${TABLE}.\\\"QUARTER\\\" \",\"sql_case\":null,\"filters\":null,\"sorted\":{\"desc\":false,\"sort_index\":1}}],\"table_calculations\":[],\"pivots\":[{\"align\":\"right\",\"can_filter\":true,\"category\":\"dimension\",\"default_filter_value\":null,\"description\":null,\"enumerations\":null,\"field_group_label\":null,\"fill_style\":null,\"fiscal_month_offset\":0,\"has_allowed_values\":false,\"hidden\":false,\"is_filter\":false,\"is_numeric\":true,\"label\":\"RO Detail Year\",\"label_from_parameter\":null,\"label_short\":\"Year\",\"map_layer\":null,\"name\":\"look_res.year\",\"strict_value_format\":false,\"requires_refresh_on_sort\":false,\"sortable\":true,\"suggestions\":null,\"tags\":[],\"type\":\"number\",\"user_attribute_filter_types\":[\"number\",\"advanced_filter_number\"],\"value_format\":null,\"view\":\"look_res\",\"view_label\":\"RO Detail\",\"dynamic\":false,\"week_start_day\":\"monday\",\"dimension_group\":null,\"error\":null,\"field_group_variant\":\"Year\",\"measure\":false,\"parameter\":false,\"primary_key\":false,\"project_name\":\"ro\",\"scope\":\"look_res\",\"suggest_dimension\":\"look_res.year\",\"suggest_explore\":\"look_res\",\"suggestable\":false,\"is_fiscal\":false,\"is_timeframe\":false,\"can_time_filter\":false,\"time_interval\":null,\"lookml_link\":\"\/projects\/ro\/files\/RO%2Fviews%2Flook_res.view.lkml?line=172\",\"permanent\":null,\"source_file\":\"RO\/views\/look_res.view.lkml\",\"source_file_path\":\"ro\/RO\/views\/look_res.view.lkml\",\"sql\":\"${TABLE}.\\\"YEAR\\\" \",
\"sql_case\":null,\"filters\":null,\"sorted\":{\"desc\":false,\"sort_index\":0}}]}}]
}' %}
{{ link }}&vis_config={{ vis_config | encode_uri }}&toggle=dat,pik,vis&limit=5000"
}
}
@Lobo-Google Is it possible to configure the default visualisation on the drill_field modal directly? I.e. not by setting a link parameter, but instructing Looker which default visualisation to show in the drill modal directly. Thank you!
Hi @eliott !! To confirm, you would like to select or configure the default visualization in the drill modal?
Hi @eliott !! To confirm, you would like to select or configure the default visualization in the drill modal?
@Lobo-Google yes that’s right. Currently the only visualisation that is displayed when a drill menu opens is a table (unless Visual Drilling is enabled, but this is not an option as I want to customise the drill menu from a Dashboard Next, and there is also no way to choose the visualisation presented).
I’m wondering if we can overwrite the type of visualisation (e.g. a single value or a scatter plot) that is presented instead of the table in a drill menu specifically (not using ‘link’, in which I know we can customise the visualisation but this opens a new page or dashboard)? Thanks!
Hello,
Has there been any update on this? I am also trying to configure the formatting of the table appearing in the drill down modal window.
Thanks
Same question, how can we modify the default modal table option? I tried with setting the row numbers set to false but the row numbers still show up.
Hi @Lobo-Google this is great. thank you for sharing. quick question on decoding the URL. Instead of using 3rd party tool can't we use Notepad++ to decode URL? Do you see any issue in using "URL Decode" available in Notepad++? I have attached the screenshot.
ng "URL
Hi there! While I am not the author of this post, I don't believe we endorse any particular third party URL decoder - you can certainly give it a try in Notepad++!
Awesome. Thank you for letting me know. We are too hesitant to use 3rd party tool.
using value format is giving a parsing error:
this is the value format i am using on the chart which is working well:
[>=1000000]0.0,,"M";[>=1000]0.0,"K";0.#
this is what im getting after json parsing:
\"valueFormat\":\"[\\u003e=1000000]0.0,,\\\"M\\\";[\\u003e=1000]0.0,\\\"K\\\";0.#\"
this is the error
Expected ',' or '}' after property value in JSON at position 937 (line 2 column 936)