You can customize the following with XMD:
- Format measures. Example: Show the decimal and grouping separators for currency.
- Add prefixes and suffixes to measures. Example: Show the percent symbol (%) after each percentage.
- Multiply measures by a factor. Example: Multiply by 100 to convert a decimal to a percent.
- Change display labels for dimensions and measures.
- Customize colors in charts based on field values.
- Define the first day of the week for the calendar year.
- Add action menus to dimensions that let dashboard viewers invoke actions from lenses and dashboards.
Once a dataset created, by default the XMD will have no values, let's look at this. You can download XMD (which is also in JSON format) by editing the dataset.
Open the JSON file and here is the content
{"dataset":{},"dates":[],"derivedDimensions":[],"derivedMeasures":[],"dimensions":[],"measures":[],"organizations":[],"showDetailsDefaultFields":[]}
Tips: use jsoneditoronline.org to reformat the file into a human-friendly format
{
"dataset": {},
"dates": [],
"derivedDimensions": [],
"derivedMeasures": [],
"dimensions": [],
"measures": [],
"organizations": [],
"showDetailsDefaultFields": []
}
In this blog, I'll share how to rename field name with XMD for all dashboard and lenses, or in particular dashboard with SAQL.
Setup field format and label
Let us format Amount field to be in currency format $9,999.99
- Click the Dataset to open as a Lens
- Click Fields
- Set Amount format
- Rename label Amount to Initial Amount, and Region to Sales Region
- Click Save button
- Change the Lens into Values Table and now you will see Amount is in currency format with header Initial Amount and Region become Sales Region
Note that this change will change all existing dashboard and lens.
Back to XMD
Let's us re-download the XMD file and see what how it looks like now:
{
"dataset": {},
"dates": [],
"derivedDimensions": [],
"derivedMeasures": [],
"dimensions": [
{
"conditionalFormatting": {},
"customActions": [],
"field": "Region",
"label": "Sales Region",
"members": [],
"recordDisplayFields": [],
"salesforceActions": []
}
],
"measures": [
{
"conditionalFormatting": {},
"field": "Amount",
"format": {
"customFormat": "[\"$#,##0.00\",1]"
},
"label": "Initial Amount"
}
],
"organizations": [],
"showDetailsDefaultFields": []
}
** you can use jsoneditoronline.org to re-format it.
Edit Amount with SAQL
In case that we need to change the Amount header for a particular dashboard, you can edit the lens/widget with SAQL. In this example, we are going to change from Initial Amount to MRR.
- Open the dataset with Lens
- Change to Values Table
- Click SAQL Mode button
If you notice now MRR does not have currency format we have set earlier. To enable this, we need to edit the XML file again. We need to add following conditional formatting in XMD under derivedMeasures.
A measure is a quantitative value, like revenue and exchange rate. You can do the math on measures, such as calculating the total revenue and minimum exchange rate.
Derivated measures are the ones that you get from the measures, it is calculated fields so they don’t really exist in your dataset.
A measure is a quantitative value, like revenue and exchange rate. You can do the math on measures, such as calculating the total revenue and minimum exchange rate.
Derivated measures are the ones that you get from the measures, it is calculated fields so they don’t really exist in your dataset.
{
"dataset": {},
"dates": [],
"derivedDimensions": [],
"derivedMeasures": [
{
"conditionalFormatting": {},
"field": "MRR",
"format": {
"customFormat": "[\"$#,##0.00\",1]"
},
"label": "MRR"
}
],
"dimensions": [
{
"conditionalFormatting": {},
"customActions": [],
"field": "Region",
"label": "Sales Region",
"members": [],
"recordDisplayFields": [],
"salesforceActions": []
}
],
"measures": [
{
"conditionalFormatting": {},
"field": "Amount",
"format": {
"customFormat": "[\"$#,##0.00\",1]"
},
"label": "Initial Amount"
}
],
"organizations": [],
"showDetailsDefaultFields": []
}
"dataset": {},
"dates": [],
"derivedDimensions": [],
"derivedMeasures": [
{
"conditionalFormatting": {},
"field": "MRR",
"format": {
"customFormat": "[\"$#,##0.00\",1]"
},
"label": "MRR"
}
],
"dimensions": [
{
"conditionalFormatting": {},
"customActions": [],
"field": "Region",
"label": "Sales Region",
"members": [],
"recordDisplayFields": [],
"salesforceActions": []
}
],
"measures": [
{
"conditionalFormatting": {},
"field": "Amount",
"format": {
"customFormat": "[\"$#,##0.00\",1]"
},
"label": "Initial Amount"
}
],
"organizations": [],
"showDetailsDefaultFields": []
}
Upload back the XMD file from Replace menu in Dataset edit. Let's reload the lens/widget.
Now realize that Amount now called MRR for the particular lens and it has current formatting too. Check Fields from Lens and now MRR is added as Derived Measures
As you see, actually we can add Derived Measures directly when you edit the dataset from the lens, without the need to edit the XMD manually.
Reference:
