Google Data Studio – How to truncate long text with customized ellipsis position
To fit to columns width in table charts or axis size, truncating long texts at the right – like inside Data Studio – is not always the best visualisation solution for dimension values.
On the other hand, the 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.
In the video below, I take the example of Pageviews by Page from Google Analytics demo account. 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.
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 parameters above
- A table (or any chart) to use our custom mechanism of home made chart options
Create the 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 on the data source level or on the chart level.
- 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)
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 check “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.
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 from GA, not displaying all items by default on a bar chart. Problem solved!