How to get visualization configurations for custom drilling

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.

  1. Enable the Visual Drilling labs feature.
  2. Enter development mode and navigate to the project and LookML file containing the field to which you’d like to add the visual drill. Add the drill_fields parameter within the definition of the field, and specify the fields to be included in the drill visualization.
  3. Go to an Explore containing the field and drill into it. You will see a default visualization in the drill modal.
  4. Click Explore from Here from the drill modal.
  5. In the Explore that you are redirected to, modify the default drill visualization as needed to create the desired visualization. For example, choose a different type of chart, change the colors, add grid lines, etc.
  6. Once you have the visualization you want, click the Explore’s gear icon, select Share, and copy the Expanded URL.
  7. Paste the Expanded URL into a URL decoder (such as MeyerWeb) and “decode.”
  8. Copy the decoded URL, paste it into a JSON string escaper (such as FreeFormatter), and “Escape.”
  9. Copy the part of the escaped output after `&vis={` and before `}&filter_config=`

  10. Go back to the LookML project with the drill field definition, add the following, replacing "[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"
        }
  11.  Save, validate your LookML, and push your changes to production when you are ready!
4 18 8,281
18 REPLIES 18

Beto
Participant II

Hello @lauren_boltz 

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!

Beto
Participant II

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!

sri75
Participant III

@lauren_boltz  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!

sri75
Participant III

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. 

sri75
Participant III

@lauren_boltz  @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

BIDevSonu
Participant I

@lauren_boltz : 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"
        
      }

}

eliott
Participant II

@lauren_boltz 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? 

eliott
Participant II

Hi @eliott !! To confirm, you would like to select or configure the default visualization in the drill modal? 

@lauren_boltz 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.

priyanath
Participant I

Hi @lauren_boltz  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.

DECODE.pngng  "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.

Top Labels in this Space
Top Solution Authors