I would like to try and replicate some of the behavior of the duration dimension_group. Such as, the resulting values have a label appended to them. For example, if my Explore has a Duration dimension_group:
dimension_group: TravelTime1 {
label: "Travel Time 1"
type: duration
sql_start: ${TABLE}."START_TIME" ;;
sql_end: ${TABLE}."END_TIME" ;;
intervals: [ hour, minute ]
}
The resulting query appends the name of the interval to the values. If I select ‘Hours’ the resulting query includes “hours” in the results:
The nice thing about this is that the underlying data is still numeric. i.e. - I can apply numeric filtering, and can use numeric functions in table calculations.
Unfortunately, dimension_groups are not available for every need. They also have some other limitations. So I would like to replicate this with a standard Dimension that has some dynamic functionality:
parameter: p_UnitOfMeasure_Distance {
label: "Unit of Measure - Distance"
type: string
description: "Use with 'Distance' Dimension (default is Miles)"
default_value: "Miles"
allowed_value: {
label: "Miles"
value: "Miles"
}
allowed_value: {
label: "Kilometers"
value: "Kilometers"
}
}
dimension: Distance {
label: "Distance"
description: "Typical driving route (use with 'Unit of Measure' Filter-Only Field)"
value_format_name: nvf_Quantity_def
type: number
sql: CASE {% parameter p_UnitOfMeasure_Distance %}
WHEN 'Miles' THEN ${TABLE}."DISTANCE" / 1609.344
WHEN 'Kilometers' THEN ${TABLE}."DISTANCE" / 1000
END ;;
}
The data type needs to remain as a number. I would only like the data labeled somehow so users are clear on what unit of measure is being used.
I have considered creating my own Dimension Groupings (see below). But I would much prefer a solution to the example above.
dimension: Distance_miles {
group_label: "Distance"
group_item_label: "Miles"
label: "Distance - Miles"
description: "Typical driving route"
type: number
value_format_name: nvf_Quantity_def
sql: ${TABLE}."DISTANCE" / 1609.344 ;;
}
dimension: Distance_kilometers {
group_label: "Distance"
group_item_label: "Kilometers"
label: "Distance - Kilometers"
description: "Typical driving route"
type: number
value_format_name: nvf_Quantity_def
sql: ${TABLE}."DISTANCE" / 1000 ;;
}
Solved! Go to Solution.
Solved with html.
Hint: Use {{rendered_value}} to retain the value_format. Use {{linked_value}} to persist the value_format, and also retain the clickable options in results.
parameter: p_UnitOfMeasure_Distance {
label: "Unit of Measure - Distance"
type: unquoted
description: "Use with 'Distance' Dimension (default is Miles)"
default_value: "Miles"
allowed_value: {
label: "Miles"
value: "Miles"
}
allowed_value: {
label: "Kilometers"
value: "Kilometers"
}
}
dimension: Distance {
label: "Distance"
type: number
description: "Typical driving route (use with 'Unit of Measure' Filter-Only Field)"
value_format_name: nvf_Quantity_def
sql: CASE '{% parameter p_UnitOfMeasure_Distance %}'
WHEN 'Miles' THEN ${TABLE}."DISTANCE_METERS" / 1609.344
WHEN 'Kilometers' THEN ${TABLE}."DISTANCE_METERS" / 1000
ELSE ${TABLE}."DISTANCE_METERS"
END ;;
html: {{rendered_value}}
{% if p_UnitOfMeasure_Distance._parameter_value == 'Miles' %}
Miles
{% elsif p_UnitOfMeasure_Distance._parameter_value == 'Kilometers' %}
Kilometers
{% else %}
Meters
{% endif %};;
}
Solved with html.
Hint: Use {{rendered_value}} to retain the value_format. Use {{linked_value}} to persist the value_format, and also retain the clickable options in results.
parameter: p_UnitOfMeasure_Distance {
label: "Unit of Measure - Distance"
type: unquoted
description: "Use with 'Distance' Dimension (default is Miles)"
default_value: "Miles"
allowed_value: {
label: "Miles"
value: "Miles"
}
allowed_value: {
label: "Kilometers"
value: "Kilometers"
}
}
dimension: Distance {
label: "Distance"
type: number
description: "Typical driving route (use with 'Unit of Measure' Filter-Only Field)"
value_format_name: nvf_Quantity_def
sql: CASE '{% parameter p_UnitOfMeasure_Distance %}'
WHEN 'Miles' THEN ${TABLE}."DISTANCE_METERS" / 1609.344
WHEN 'Kilometers' THEN ${TABLE}."DISTANCE_METERS" / 1000
ELSE ${TABLE}."DISTANCE_METERS"
END ;;
html: {{rendered_value}}
{% if p_UnitOfMeasure_Distance._parameter_value == 'Miles' %}
Miles
{% elsif p_UnitOfMeasure_Distance._parameter_value == 'Kilometers' %}
Kilometers
{% else %}
Meters
{% endif %};;
}