Dashboard Data

This section looks into the different features of dashboards and tiles that help us control the data shown on the dashboard.

Linking to Sheets

A tile can be used to display a sheet by specifying the content type of the tile as sheet. The unique sheet Id of the sheet to be displayed should also be specified in the model file. Following code shows the method to incorporate a sheet in a tile.

    tile EfficiencyChart
        content sheet
            ID N~axRyuuYDj

Here, the sheet Id N~axRyuuYDj can be retrieved from the URL of the sheet in view mode. For example the URL for this sheet would be

<ApplicationURL>/app#!/workspaces/W~WPWaJbsxR/branches/master/versions/1573630990020/notebook/default/note/N~ROm3a_St5.

There is no constraint on the type of sheet that can be displayed using this method. The sheet being displayed may be tabular, graphical or a metric sheet. Formatting or appearance of the sheet itself cannot be controlled in the bippDash model and should be specified in the sheet itself. The sheet definition may be modified independently after it has been linked to the dashboard. The dashboard always shows the latest version of the sheet.

Channels for communication

In order to effectively visualize data on the dashboard it is necessary that the individual tiles on the dashboard be able to communicate with each other. bippDash allows tiles to pass messages to each other through a construct called Channels. Channels provide a mechanism for capturing click events on tile/sheet data and passing the selected value to other tiles or pop-ups for enhanced visualization or drilldown. bippDash allows two types of channel on the dashboard.

Unnamed Channels

An implicit unnamed channel is created by bippDash for every element from the underlying data set. This implies that every table-column pair visible on the dashboard corresponds to an unnamed channel. Thus a click on any data element would be propagated across the dashboard through an unnamed channel. Consider a simple visualization with two tiles, a table and a graph corresponding to the same data as shown below. Clicking on any element of data in the table would result in a filter which is automatically applied to both the sheets. For example, clicking on the value Jan, 2018 in the first sheet (table) in the following picture results in a the filter MonthYearNamedFormat = Jan, 2018 being applied to both the sheets (table and graph).

No explicit code is required to define an unnamed channel. The channel provides an implicit filtering mechanism for all the tiles on the dashboard. Thus, unnamed channel act like broadcast channels for dashboards.

Named Channels

Let us say that instead of broadcasting the filter value across the dashboard, we want it to be applied only on specific other tiles on the dashboard. This is where bippDash allows for the use of named channels. A named channel is defined as a clickevent in the bippDash model file. It may be created for any of the table-column pairs in the underlying dataset as follows.

dashboard StockGoodsDb
	clickevent MonthClick
        table stock_goods
        column MonthYearNamedFormat 

Any of the incorporated sheets that contain the table-column pair of stock_goods-MonthYearNamedFormat can now publish to this channel, by incorporating the following code in the tile definition

tile stockgoodsData
	content sheet
        ID N~dAJ_C8Vw2
	publish MonthClick			 

Specific tiles can now subscribe to this channel to consume the event. This can be achieved using the subscribe keyword in the code as follows.

tile stockgoodsGraph
	content sheet
        ID N~H4WCfdMwK
	subscribe MonthClick

This implies that whenever the column MonthYearNamedFormat is clicked on the tile stockgoodsData, the clicked/selected value for MonthYearNamedFormat will act as a filter for the tile stockgoodsGraph. In this example the channel MonthClick is the named channel that provided this explicit filtering mechanism

Note that, after defining the named channel, the filter is only applied on the stockgoodsGraph tile. This is because the tile stockgoodsData has not subscribed to the named channel MonthClick. It also blocks the previous unnamed channels. Thus publish and subscribe functionality can be defined as below.

• publish: A click propagates to the named channel, but not the unnamed channel.
• subscribe: The named channel blocks the unnamed channel.

Filtering data with Filter Widgets

bippDash allows dashboard developers to create filter widgets on the dashboard. These filter widgets provide richer filtering functionality compared to simple click and select of data that can be achieved through channels.

Filters can be created on the columns available in dataset corresponding to any of the sheets included in the dashboard. One of the sheet Id needs to be specified for this along with the table and column name on which the filter is being created. The following code shows a tile with a sheet and another tile with a filter based on that sheet.

tile FilterTile
  content filterwidget
    ID N~ROm3a_St5     //Sheet Id
    table stock_goods  //Table name
    column Batch       //Column name 

tile GoodsTable
	content sheet
        ID N~ROm3a_St5

By default, this filter would be applied to all sheets included in the dashboard. If the dashboard shows data from more than one table in the data model (bippLang) then a join should be defined between those tables and the table identified in the filter widget (stock_goods in the above example). This will ensure that the filter is correctly applied across all relevant tiles.

Following are the type of dropdowns that may be applied in the widget.

• single_value_dropdown
• single_value_list
• multiple_values_dropdown
• multiple_values_list
• datetime
• number
• auto

Sample use

content filterwidget
   type single_value_dropdown

Filter Widgets Settings

Following are the other settings that may be applied to tiles with content type filterwidget.

Following is the complete code for a filter widget with the settings listed in this section.

tile filterTile
  basis 70.00
  title Filter Data
  content filterwidget
    ID N~ROm3a_St5
    table stock_goods
    column Batch
    type multiple_values_list
    settings _
      apply_button show
      auto_fetch false
      include_values false
      order_by asc
      limit 100

Filter widgets with auto_fetch true may also have the default value set, so that the dashboard sheets will already be filtered using the default value for the column, when it loads initially. This can be set as follows.

tile filterTile
  content filterwidget
    ID N~dAJ_C8Vw2
    table stock_goods
    column MonthYearNamedFormat
    default_value 'Feb, 2018' 
    settings _
      auto_fetch true

In this case the dashboard sheets will be filtered using the condition MonthYearNamedFormat = 'Feb, 2018' on load.

Filter Widget Conditions

By default the filter widget is populated with all the unique values from the specified table-column combination. Developers can however restrict the values populated in the filter widget by defining a filter for the values in the filter widget. Following example demonstrates the use of a filter for values populated in the filter widget.

tile filterTile
	content filterwidget
        ID N~dAJ_C8Vw2
    table stock_goods
    column MonthNumber
    filter _
        comparator <
        value 3
        type int

The filter widget defined by filterTile will populate all the values from the column stock_goods.MonthNumber which are less than 3.

The filter applied may also be with respect to values from another table-column combination. This can be achieved by defining the filter as follows.

tile filterTile
	content filterwidget
        ID N~dAJ_C8Vw2
    table stock_goods
    column MonthNumber
        filter _
        table t2
        column c2
        comparator =
        value 3
        type int

In this case the table stock_goods would be joined with the table t2 using the join defined in the data model and MonthNumber would be populated such that t2.c2 = 3

Two or more filter conditions may be applied on the filter widget using logical operators AND or OR as shown in the following snippet of code.

    filter _
        comparator >
        value 1
        type int
        logical_operator AND 
    filter _
        comparator <
        value 6
        type int

In this case the resulting condition MonthNumber > 1 AND MonthNumber < 6 will be applied on the values in the filter widget.

Filter Widgets With Parameters

Columns which have been defined as parameters with allowed values in the underlying dataset may also be used to populate the values in a filter widget. This can be achieved by setting the is_param property of the filter widget to true. These could be used to filter sheets which include columns based on the same input parameter. For example, if the underlying bippLang dataset has a parameter defined as follows.

parameter paramDays
  allowed_value 100
  allowed_value 200
  allowed_value 365
  default_value 365
  type int

And a column that uses the parameter is also defined as

 column period
    sql " (case when ($paramDays = 100) then 'quarterly' else 'yearly' end) "
    type string

If a sheet which displays the column period is now incorporated in the dashboard, it may be filtered using a filter widget defined as follows.

tile _
  basis 100.00
  content filterwidget
    ID N~NN4sSYLu8
    table period_metric
    column paramDays
    is_param true

Setting up Column Aliases

Since the filter widget applies to all sheets on the dashboard by default, absence of a join between the filtered column and the source data for the other tiles, can lead to an error. To avoid this issue, bippDash allows for the definition of aliases between columns. Aliases may be defined from the column being used in the filter widget to the column that should be joined in the sheet that would get filtered. For example, if the filter on the column stock_goods.Batch should also be applied to a tile SGLastYear that shows data from the table stock_goods_lastyear, then the following alias should be defined for the column Batch in the table stock_goods_lastyear.

tile SGLastYear
  content sheet
    ID N~o1A5yNuoO
  alias sgalias
    from _
      table stock_goods
      column Batch
    to _
      table stocked_goods_lastyear
      column Batch

Aliases can only be defined between columns with the same data type. Aliases can also provide better performance over joins as the selected filter value would be directly applied on the column stocked_goods_lastyear.Batch instead of joining the two tables stock_goods and stocked_goods_lastyear.

Filters and Channels.

By default when a filter widget is created, a named channel automatically gets created corresponding to the table-column pair defined in the filter widget. The filter widget automatically publishes to this named channel. Even if two filter widgets specify the same table and column, their named channels will be distinct. Additionally a filter widget may also be created with a user defined named channel as follows.

tile _
	basis 100.00
	content filterwidget
    ID N~dAJ_C8Vw2
    table stock_goods
    column Batch
	publish SGBatchChnl

Here the channel SGBatchChnl has been defined as

clickevent SGBatchChnl
	table stock_goods
	column Batch

Using this definition we can control how the filter will be applied across various tiles on the dashboard. In this case the filter will only be applied to those tiles which have subscribed to the channel SGBatchChnl.

Auto Fetch filtered data

When values are selected in the filter widget or through a channel, the corresponding dashboard would be updated immediately if the auto_fetch property for the dashboard is set to true. This property is available at the dashboard level and applicable to all filter widgets and sheets included on the dashboard. By default auto_fetch is false. When auto_fetch is set to false, a Fetch button will be displayed on the dashboard. The filters will be applied only when the Fetch button is clicked.
Following is the code for setting this property.

dashboard WarehouseDb
	auto_fetch true

Avocado

Avocadoes are special tiles that can be replicated in the dashboard. The avocado feature in bippDash can be used to apply filters on a sheet in such a way that the sheet gets replicated for different values of specific parameters. You are required to specify the values for the following variables in bippDash to create a tile with content type avocado.

1. loop_range: This is the range of values that should be passed as filters to the sheet for each instance of the tile.
2. loop_var: This is the parameter which will get one of the values from the defined range for each instance of the replicated tile.

Following sample illustrates the code for an avocado tile which should be repeated 3 times for different values of the the column Country as specified in the loop_range

tile 4
	basis 33.00
    height 500px
    content avocado
        ID N~rNCkCjBvq
        filter _
        table locations
        column Country
        value $x
        type string
    title $x
    loop_var x
    loop_range [(India),(China),(Japan)]