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
.
Setting | Description of the setting | Possible Values | Sample Use |
---|---|---|---|
Apply Button | Can be used to show/hide a Apply button in the filter widget. | show, hide | apply_button show |
Auto Fetch | This setting indicates if values should be pre-populated in the filter widget when the screen loads or should be shown once you start typing. | true, false | auto_fetch true |
Include Values | This indicates if the values selected in the filter should be included or excluded from the result | true, false | include_values true |
Order By | This is used to indicate the order of the values shown in the filter | asc, desc | order_by asc |
Limit | This indicates the maximum number of values that will be shown in the filter | Any number | limit 10000 |
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
.
- loop_range: This is the range of values that should be passed as filters to the sheet for each instance of the tile.
- 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)]