There are some situations where you will want to have a Drop-down list Property Control display fixed values. This is easily accomplished by using the ‘Fixed values’ option for setting the property value, and then entering your fixed values and their display names.
In other situations, you may want those fixed values to update depending on certain conditions. It could be that you only want to show some of them at any given point in time, or it could be that you want the values to completely change based off some other logic in your analysis file. This can also be accomplished. It does require more work, but does not necessarily require a Script Control. We will learn how to do this in this tip. NOTE: If you want the Property Control values to update dynamically but show values in a column (as opposed to fixed values), this can be accomplished another way. Please see this tip for how to do that.
Let’s assume for this example you will want to update fixed values displayed in one Property Control based off a selection in another Property Control. The first Property Control will display the following fixed values:
“All time”, “Year”, “Quarter” and “Month”
We then want to create a second Property Control. In that Property Control, the values should also be fixed values, but if the first Property Control has “All time” selected, the second Property Control should show all possible values. If the first Property Control has “Year” selected, the second Property Control should show “Year”,”Quarter”, and “Month”. If the first Property Control has “Quarter” selected, the second Property Control should show “Quarter” and “Month”. Finally, if the first Property Control has “Month” selected, the second Property Control should only show “Month”. This type of scenario is good when you are trying to do complex calculations, like Year over Year or Year over Month, when only certain combinations are valid.
To setup this second Property Control, we need to first create a calculated column. Ideally we would do this in a new data table, but it can be in the existing data table. Call the column PropertyControlValues, and use the following calculation, assuming ${dd1} is the name of the Property attached to your first Property Control you just created.
Case when "${dd1}" = "All time" and RowId() = 1 then "All Time"
when "${dd1}" = "All time" and RowId() = 2 then "Year"
when "${dd1}" = "All time" and RowId() = 3 then "Quarter"
when "${dd1}" = "All time" and RowId() = 4 then "Month"
when "${dd1}" = "Year" and RowId() = 1 then "Year"
when "${dd1}" = "Year" and RowId() = 2 then "Quarter"
when "${dd1}" = "Year" and RowId() = 3 then "Month"
when "${dd1}" = "Quarter" and RowId() = 2 then "Quarter"
when "${dd1}" = "Quarter" and RowId() = 3 then "Month"
when "${dd1}" = "Month" and RowId() = 3 then "Month"
else
null
end
When this is created, the calculated column will update when the Property Control is updated.
Then you can create another Drop-down list Property Control. This one should display values using the ‘Unique values in column’ option and point to the calculated column that we just created.
The end result is that the second Property Control will only display values that are valid based off the first Property Control.
As another example suppose as part of our Guided Analysis, we want to update some attributes like what unit of measurement to use. By changing the unit of measurement, we will have to apply a calculation to all our relevant data. Suppose you have many different attributes you need to measure, like Temperature, Weight, Time, Distance, etc… Rather than creating a unique drop down combination for each, you can use the approach in this tip to create one group of drop downs and then update the values accordingly.
In the image below, the user has selected to update the temperature, so the second property control shows both ‘Fahrenheit’ and ‘Celsius’.
If the user has selected to update the weight, the second Property Control will show ‘Kilograms’ and ‘Pounds’.
The expression used for this calculated column, assuming the first Property Control is called “whichMeasurement” is:
Case when "${whichMeasurement}" = "temperature" and RowId() = 1 then "Celsius"
when "${whichMeasurement}" = "temperature" and RowId() = 2 then "Fahrenheit"
when "${whichMeasurement}" = "weight" and RowId() = 1 then "Pounds"
when "${whichMeasurement}" = "weight" and RowId() = 2 then "Kilograms"
else
null
end
For more information on using Property Controls and other advanced techniques such as these, please consider registering for our Author Bootcamp.
