In my previous article, Creating a “Dynamic Dimension in Looker”, I walked you through creating dynamic dimensions in Looker. Now we have the inverse situation. In this example, the business wants to analyze certain metrics by Category, Vendor, and Item and they only want to look at one metric at a time, rather than creating a wide table. If we were to create them as separate tables we would end up with a lot of tables, taking up an entire report. Instead, we can create dynamic measures in Looker so that we limit the amount tables needed in the report.
Goal: We would like to analyze Iowa liquor sales by Category, Vendor, and Item Description. We’d like to have tables for each dimension and be able to see one metric at a time (Sales Dollars, Liters Sold, and Bottles Sold
Challenge: We don’t want to create 9 tables, taking up a lot of space in our report. We need to be able to swap out the metrics for the three dimensions (Category, Vendor, and Item). The column names of the metrics need to change for the metric they’re displaying. In addition, we need to manage the three different value formats (Sales Dollars – usd0, Liters Sold – decimal1, Bottles Sold – decimal0).
Sales Dollars
Liters Sold
Bottles Sold
Solution: Create dynamic measures that populate with a filter on the report. Leverage html and liquid to create dynamic formatting for those dynamic measures.
Step 1: Create a parameter for the metrics. Include all the options required as allowed values. Use the “label” parameter to ensure the case sensitivity is what you want it to be. Also put them in the order you want displayed in the report filter.
Step 2: Create a dynamic measure for the YTD metrics. (Note that ytd_cy measures were previously created and are referenced in the LookML.)
- Create a dynamic label parameter using liquid.
- Format each metric in the sql parameter using liquid and your database’s appropriate syntax.
- In the html parameter use liquid and indicate the output with {{rendered_value}}. Notice the modification made to the Sales Dollars value by adding $ in front of {{rendered_value}}.
- Use the group_label parameter so you can easily find the dynamic measures when building the report.
Step 3: Create a dynamic measure for the YTD YOY metrics. (Note that the ytd_yoy measures were previously created using the ytd_cy and ytd_ly, also previously created, and are referenced in the LookML.)
- Create a dynamic label parameter using liquid.
- Format each metric in the sql parameter using liquid and your database’s appropriate syntax.
- In the html parameter use liquid and indicate the output with {{rendered_value}}. Notice the modification made to the Sales Dollars value by adding $ in front of {{rendered_value}}. These are the same as the measure created in Step 2.
- Use the group_label parameter so you can easily find the dynamic measures when building the report.
Step 4: Create a dynamic measure for the YTD YOY % metrics. (Note that the ytd_yoy_pct measures were previously created and are referenced in the LookML.)
- Create a dynamic label parameter using liquid.
- Format each metric in the sql parameter using liquid and your database’s appropriate syntax. Multiply each by 100 and round to 2 decimals to output a percentage formatted as xx.xx.
- In the html parameter use liquid and indicate the output with {{rendered_value}}. Notice the modification made by adding % at then of {{rendered_value}}.
- Use the group_label parameter so you can easily find the dynamic measures when building the report.
Step 5: Create the looks for Category, Vendor, and Item using the measures you just created. Save to a new or existing dashboard.
Step 6: Add the parameter you created as a filter to the dashboard. Rename if desired.
Now we have three tables with dynamically changing measures.
With the ability to create dynamic dimensions and measures, you can create interactive and condensed reports giving your users all the information that they need.