This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Cookie Policy. Click Preferences to customize your cookie settings.
Objective:
Cycle through the values of a DIMENSIONAL table and apply a filter to the dashboard accordingly. For each value, return: 1) an image associated with that value, 2) its relevant KPI's, 3) option to filter dashboard on that particular value (and clear, as necessary)
Sample files can be found below:
Prior to implementation - relevant architecture is as follows:
- 1 DIMENSION table containing: DimID, DimName, DimImageURL
NOTE: to obtain an ImageURL for each value in your DIMENSION table you can either:
1) Create a custom field and apply a CASE statement - one for each value in your DIMENSION table - to return the URL of an image for each value. Ex.
CASE WHEN [DimName] = 'DimName1' THEN 'http://www.dimname1.jpg'
WHEN [DimName] = 'DimName2' THEN 'http://www.dimname2.jpg'
WHEN [DimName] = 'DimName3' THEN 'http://www.dimname3.jpg'
ELSE NULL END2) Import an Excel file into your ElastiCube containing 1 record per DimID / DimName and its associated DimImageURL. Create a custom column in the initial DIMENSION table and use the Lookup() function to import its DimImageURL. Build changes
- 1 FACT table containing at least: DimID (foreign key), measures to aggregate on
Implementation:
- Create a new BloX widget in your dashboard. Paste the following code snippet into the Editor:
{
"style": "",
"script": "",
"title": "",
"titleStyle": [
{
"display": "none"
}
],
"showCarousel": true,
"body": [
{
"type": "Container",
"items": [
{
"type": "ColumnSet",
"columns": [
{
"type": "Column",
"items": [
{
"type": "Image",
"url": "{panel:ImageURL}",
"horizontalAlignment": "center",
"size": "large"
}
]
},
{
"type": "Column",
"horizontalAlignment": "center",
"style": {
"justify-content": "center"
},
"items": [
{
"type": "TextBlock",
"text": "{panel:Brand} Overview",
"size": "large",
"color": "default",
"weight": "bold",
"spacing": "small",
"horizontalAlignment": "center"
},
{
"type": "TextBlock",
"text": "TY Revenue: {panel:TY Revenue}",
"style": {
"align-self": "center",
"font-weight": "none"
},
"size": "medium",
"color": "default",
"spacing": "large",
"horizontalAlignment": "center",
"separator": true
},
{
"type": "TextBlock",
"text": "LY Revenue: {panel:LY Revenue}",
"style": {
"align-self": "center",
"font-weight": "none"
},
"size": "medium",
"color": "default",
"spacing": "large",
"horizontalAlignment": "center",
"separator": true
},
{
"type": "ActionSet",
"actions": [
{
"type": "Filters",
"title": "Filter",
"data": {
"filters": [
{
"filterName": "Brand",
"filterJaql": {
"members": [
"{panel:Brand}"
],
"custom": true
}
}
]
}
},
{
"type": "Filters",
"title": "Clear",
"data": {
"filters": [
{
"filterName": "Brand",
"filterJaql": {
"explicit": false,
"multiSelection": true,
"all": true
}
}
]
}
}
]
}
]
}
]
}
]
}
]
}2. Add the "DimName" and "DimImageURL" fields to the Items pane of your BloX widgets. In the above example, "Brand" is the equivalent of "DimName" and "ImageURL" is the equivalent of "DimImageURL". Be sure to modify the code to the naming convention of the fields in your DIMENSION table (lines 23, 38, 80, 83, and 97) - i.e - replace all instances of "Brand" with your "DimName", etc.
3. Add any relevant KPI's to the Values pane of your BloX widget. In the above example, we have "TY Revenue" and "LY Revenue". Replace "TY Revenue" and "LY Revenue" in the code (lines 47 and 60) with the name of your relevant KPI's exactly as they exist in the Values pane.
4. Save your widget and navigate back to your dashboard. Add your "DimName" field as a dashboard filter. You can lock this field if you don't want users interacting with it, but it is necessary in order for our "Filter" and "Clear" buttons to be operational. Note that the name of this filter will need to match exactly to the "filterName" value in lines 80 and 97 of the code
5. That's it!
