cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Apply a different currency/distance/temperature/weigh/speed measurement to each user

Ophir_Buchman
Sisense Team Member
Sisense Team Member

Introduction

The following article answers a use case where each user/group in your organization should see their data in a different format.

This logic can be applied to various measurements:

  • Currency (USD/NIS/EUR/etc.)
  • Distance (KM/Mile)
  • Temperature (C/F)
  • Weight (Kg/Pound)
  • Speed (KMH / MPH)

Article Scope

The scope of this article will cover the end-to-end implementation (data model & dashboard design)
The document will discuss the currency use case. However, it can be applied to any of the above.

Assumptions

The following assumptions are in place:

  1. You are well familiar with data modeling and are using a fact/dimension table structure
  2. You have the appropriate permissions to model data and set data security
  3. The setting you want to have is fixed per user (e.g., a user would always use the same currency)

Data Model Preparation

Perform the following tasks on your data model:

  1. Make sure all the financial data is aligned to a single base measurement (e.g., All prices in USD)
  2. Create a new table that contains the following columns:
    1. Measurement name (e.g., USD / NIS / EUR / GBP / etc.)
    2. Measurement conversion rate (set the base currency to 1)
    3. Measurement sign (e.g., '$', 'โ‚ฌ', etc.)
    4. A fake key (should be set to "1" for all rows)
  3. Create a fake column on each fact table with the value "1" on all rows
  4. Connect each fact table with the new table using the two "1" fields
  5. Perform a build

Your 'Dim_Currency' table should look somehow like this:

Ophir_Buchman_0-1655465048124.png

Your data model should look somehow like this:

Ophir_Buchman_1-1655465100461.png

Data Security Preparation

Next, we'll lock each user/group to a single value.
Make sure not to forget a default value - Otherwise, users may lose access to all data.

To do so, use data security. Your settings should look somehow like this:

Ophir_Buchman_2-1655465239846.png

Dashboard Preparation

The implementation on the dashboard side will be done by:

  1. Multiply the "conversion rate" by any financial figure in the dashboard
  2. Modify each financial figure on the dashboard to "Currency" mode
  3. Add a dashboard script that updates the "Currency" character

Multiplying Figures by the Conversion Rate

By multiplying your financial figures by the conversion rate, you will ensure that each user sees the price in the correct currency. Here is an example of a "Total Revenue" calculation:

Ophir_Buchman_0-1655471886081.png

Modify the Figure to a "Currency" Mode

Refer to this article (Link)

Adding a dashboard Script

The script is built out of two parts:

  • Extracting the user's assigned currency sign
  • Applying the currency character setting to all widgets

Part #1

The first part of the script uses this community article.
This script defines how to create an additional JAQL call within your script, and the JAQL call we require:

function runHTTP(jaql) {
const $internalHttp = prism.$injector.has("base.factories.internalHttp") ?
prism.$injector.get("base.factories.internalHttp") : null;

const ajaxConfig = {
url: "/api/datasources/" + encodeURIComponent(jaql.datasource.title) + "/jaql",
method: "POST",
data: JSON.stringify(jaql),
contentType: "application/json",
dataType: "json",
async: false
};
const httpPromise = $internalHttp ? $internalHttp(ajaxConfig, true) : $.ajax(ajaxConfig);

return httpPromise.responseJSON;
};
function getCurrencySign(dashboard) {
const query = {
"datasource": dashboard.datasource.fullname.split('/')[1],
"metadata": [
{
"jaql": {
"dim": "[Dim_Currency.Sign]" // Change this table/column based on the data model
}
}
]
};

return runHTTP(query);
}

Part #2

The second part of the script instructs the dashboard to update all widgets with the correct currency character:

dashboard.on('initialized', function(dash,filters) {
newSign = getCurrencySign(dash).values[0][0].data

dash.widgets.$$widgets.forEach(function(widget) {
widget.metadata.panels.forEach(function(panel) {
panel.items.forEach(function(item) {
if (item.format !== undefined && item.format.mask !== undefined && item.format.mask.currency !== undefined)
item.format.mask.currency.symbol = newSign
})
})
})
})
User A User B
Ophir_Buchman_1-1655472577729.png Ophir_Buchman_2-1655472613840.png
0 REPLIES 0