cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member

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 END
2) 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:

  1. 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!
Version history
Last update:
‎03-02-2023 08:52 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Developers Group:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email [email protected]

Share this page: