Download Column Switcher Plugin

INTRODUCTION

The Column Switcher Plugin (CWP) allows a user to use a dashboard filter to switch between columns, instead of rows, for calculation.

Challenge

A designer of a dashboard may want to calculate the revenue for a product over time for an international audience. In common convention, the fact table of this scenario may have the transaction amounts in different columns for different currencies. The designer wants the end-user to toggle between different currencies.

Traditionally, to achieve this, aggregation from a dimension table with daily conversion rates is required. This involves complex modeling and formula building.

Solution

With the CWP enabled, you can apply a dashboard-level filter that allows you to select which column will be used in a calculation. With this approach, modeling is not required. This solution involves using a simple aliased formula with a specific naming convention and a detached custom SQL table used for the dashboard filter.

SETUP

Download and place the plugin folder into the Sisense plugin folder.

Program files > Sisense > app > plugins

1. Elasticube Setup

Setup is based on this fact table example

1. Create a custom SQL table and name it ‘columnToUse’. The table does not need to be joined to any other table.
   [If you want to name the table something else, update the column_select_table variable in the javascript file]
2. Create a record for each column you want to switch between using. The values should reflect the column names.

  SELECT 'USD_Revenue'
  UNION ALL
  SELECT 'CAD_Revenue'

2. Dashboard Setup

Writing the Aliased Function

1. Write a function that adds the column’s aggregations you want to swap between.
   They don’t need to be the same aggregation type

[Total USD_Revenue] + [Total CAD_Revenue]

1. Save the function as an Aliased function using the function editor’s star icon. Prefix the function with ‘swap_’
   [you can configure what the prefix should be in the javascript file by editing the variable special_alias_prefix]
2. Use this aliased function where you want to swap the columns
3. On the dashboard apply a filter from the columnToUse with single selection.

USAGE AND LIMITATIONS

Usage with other values

[swap_currencySwap] / COUNT([product_id])

If the aliased function is used in conjunction with other values then no further setup is required. Simply add the aliased function and write the formula.

Usage independently

[swap_currencySwap]

If the aliased formula is the only element of the formula then an extra step must be taken to avoid Sisense flattening the formula and removing the aliased function format.

Sisense automatically flattens aliased functions when they are used independently, so you have to options to avoid this unwanted behavior:

1. Add a 0 (e.g. [swap_currencies] + 0 ) so that it is not flattened. 
2. Otherwise, rename the function and add a dummy suffix. This will also prevent it from being flattened.

Current Version (1.0) Limitations

• The plugin can only select one column from each table
• This means you cannot select CAD_costs and CAD_revenue for comparison if they are in the same table using the plugin. For this use case, another fact table is required.
• This is a Community Plugin and is not officially supported by customer support.
• For inquiries about having this plugin supported, please reach out to your dedicated CSM and artem.yevtushenko@sisense.com

PLUGIN CODE WITH COMMENTS

const column_select_table = 'columnToUse';
const special_alias_prefix = 'swap_';

prism.on('dashboardloaded', function (args) {
    // when a widget queries execute the code
    args.targetScope.dashboard.on('widgetbuildquery', (a, b) => {

        // find the filter element who's base table matches the column_select_table and get the filter member that is selected. In V1 it is assumed the user set the selection to single select.
        let filterColumnSelector = args.targetScope.dashboard.filters.$$items.filter(i => i.jaql.table === column_select_table)[0].jaql.filter.members[0];
        
        // in the widget, run this code on every mesaure
        b.query.metadata.filter(val => val.jaql.type == 'measure').forEach(i => {

            // get the function context for the function.
            let queryContext = i.jaql.context;

            // go through every context element of the query
            for (let [k, v] of Object.entries(queryContext)) {
                
                // if an element has a title it is either renamed or an aliased function. Find a function that has the special alias prefix
                if (v.title.includes(special_alias_prefix)) {
                    
                    // in the matched function, it has a context element for each of its aggregations.
                    for (let [k2, v2] of Object.entries(v.context)) {

                        // Find the function who's column comes from the name present in the filter.
                        if (v2.column == filterColumnSelector) {

                            // set the entire aliased function formula to equal the single aggregation.
                            v.formula = k2;
                            // console.log(i.jaql, 'updated successfully');
                        }
                    }
                }
            }
        })
    })
})