How to get visualization configurations for custom drilling

[Lobo-Google]

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!

[Beto]

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 !?

[Lobo-Google]

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]

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.

[Lobo-Google]

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]

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

[Lobo-Google]

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]

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]

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

[lakshmi_prasann]

@sri75 Awesome. Thanks Sri. As you said, for pivoting when appended the pivot dimension, then it worked. Thanks alot

[BIDevSonu]

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

}

[eliott]

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

[Lobo-Google]

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

[eliott]

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!

[davidu]

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

[Samyamoy]

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]

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 

[Lobo-Google]

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++!

[priyanath]

Awesome. Thank you for letting me know. We are too hesitant to use 3rd party tool.

[grgabj]

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)