Reference dimension value into parameter

Looker Q&A Looker
1

Context: I have been asked to implement Looker Pagination on one of our dashboards. For that, we need to create two parameters to be used in LIMIT and OFFSET of a SQL query.

The solution proposed under the link above did not work but I was able to work around the issue…mostly.

derived_table: {
    sql:
      select *
      from `table`
      LIMIT {% parameter referentiel_site.number_per_page %} 
      OFFSET {% parameter referentiel_site.number_per_page %} * {% parameter referentiel_site.page %} - {% parameter referentiel_site.number_per_page %} 
      ;;
  }

  parameter: page {
    default_value: "1"
    type: number
  }

  parameter: number_per_page {
    default_value: "5000"
    type: number
  }

This does not work because the OFFSET accepts either an integer or a parameter, not an arithmetic operation.

To overcome that, I was thinking about putting the two operations above into a dimension:

dimension: offset_value {
    type: number
    sql:  
      {% parameter referentiel_site.number_per_page %} * {% parameter referentiel_site.page %} - {% parameter referentiel_site.number_per_page %} ;;
  }

After that, I would like to reference this dimension into a parameter and inject the parameter into OFFSET.

Question: Can a dimension be referenced into a parameter?

The final goal is to have a dynamic offset, so any other suggestions are welcome.