How to truncate long text with customized ellipsis position in Looker Studio

To fit to columns width in table charts or axis size, truncating long texts at the right – like proposed inside Looker Studio – is not always the best visualisation solution for dimension values.

On the other hand, the Looker Studio (formerly known as Google Data Studio) built in option in the style tab of chart – turning off text wrapping – is not available for all chart types.

So, let’s create an home made solution to take care about our long text values. Let’s provide to report designers a new chart option to display the more relevant part of data of long text values on their charts. Let’s give also the possibility to control the number of characters we wish to keep visible.

---

---

The result

In the video below, I take the example of Page views by Page path. It is a good example of long text values. Feel free to apply this technique for any kind of data source and for any dimension with long texts requiring to be reduced for visualisation and chart zoning purposes.

---

---

---

Ingredients

To build this chart, we need the following elements:

• A parameter storing format options of the custom truncation
• A parameter to fill the adapted number of characters we wish to display
• A calculated field altering the original value of the dimension, following the parameters above
• A table (or any chart) to use our custom mechanism of chart options

---

1. Parameters

Custom ellipsis

Create the Looker Studio parameter “Custom ellipsis” as a text with a permitted list of values, filled with the following string as values and labels :

• “abcdef…”: to truncate the end of the text
• “abc…def”: to truncate the middle of the text
• “…abcdef”: to truncate the beginning of the text

---

---

Number of characters

Then, add the second parameter “Nb of characters” to control the maximum number of characters to display (ellipsis excluded). It is a number data type permitting any value, with 0 as the default value.

---

---

2. Calculated field

Now, we create the calculated field corresponding to the final dimension value of your chart. You can create it at the data source level or at the chart level.

Some explanations:

• We consider the value “0” for the Nb of characters as an indication to not truncate the text
• The text is truncated only if its length is higher than the max length configured for the chart
• SUBSTR function allows to use negative number as position, so it avoids intermediary calculation (really nice to go fast)
• To cast number as integer (required to use ROUND function after number calculation with number type), we specify INT64 as data type (not officially documented but available)

---

Truncated page

CASE
 WHEN Nb of characters = 0 OR LENGTH(Page) < Nb of characters THEN Page
 WHEN Custom ellipsis = "…abcdef" THEN CONCAT("…",SUBSTR(Page,-Nb of characters,Nb of characters))
 WHEN Custom ellipsis = "abcdef…" THEN CONCAT(SUBSTR(Page,1,Nb of characters),"…")
 WHEN Custom ellipsis = "abc…def" THEN 
 CONCAT(
     SUBSTR(Page,1,CAST(ROUND(Nb of characters/2,0) AS INT64))
     ,
     "…"
     ,
     SUBSTR(Page,CAST(ROUND(-Nb of characters/2,0) AS INT64),CAST(ROUND(Nb of characters/2,0) AS INT64))
 )
 END

---

---

3. Chart configuration

At last, only one thing needs to be verified on the chart configuration: Be sure to uncheck “Wrap text” in the table body section of chart style tab, and so turn off the default truncation at the right of long values.

Then, use your calculated field as a dimension.

---

Final thought and other example

Because we choose to use created parameters on the chart configuration as an option of design, we can imagine using these same parameters for several calculated fields, truncated more than one case of dimension with long texts. Here another example with Product dimension, not displaying all items by default on a bar chart. Voilà.

---