Visual Builder Component for Embedding Analytics — Part 3 — Filters
Introduction
I’ve previously described how to embed Oracle Analytics Canvases into a VB application using a component available in the exchange. In this blog, I will describe how to pass filters to the embedded analytics canvas in two scenarios, the first will be directly referencing values to apply as filters and the second scenario will use a VB selector component to allow an end user to choose from a list of values and then the selector passes the chosen value as a variable referenced by the component.
You’ll find some examples of how to create the filter JSON that is required to be passed to the OAC component within the description of the component itself. This can be found by viewing the description page of the component when the component has been installed into a VB application.
How to Directly Reference a Filter Value
Let’s take an example of an embedded analytics canvas that shows profit and sales by product sub category.
In order to add a filter, programatically to this embedded canvas, the following JSON can be added to the filters parameter (highlighted in the above screenshot).
You can access the expression editor shown below by clicking on the ‘fx’ that appears when you mouse over the Filters section of the component.
I’ve included the JSON example below so it’s easier to copy into a text editor and edit according to your own use case.
[
{
"sColFormula": "XSA('<OAC-USER>'.'Sample Order Lines').\"Columns\".\"Product Sub Category\"",
"sColName": "Region",
"sOperator": "in",
"isNumericCol": false,
"isDateCol": false,
"bIsDoubleColumn": false,
"aCodeValues": [],
"aDisplayValues": [
"Bookcases",
"Computer Peripherals",
"Office Machines",
"Tables"
]
}
]But where do you obtain the JSON?
This is a link to the Oracle Analytics product documentation on embedding where you can find a description of the JSON to pass a filter. The value for the ‘sColFormula’ parameter can be found in the ‘Developer’ menu of an OAC project, under the JSON tab. As seen in the screenshot below, you need to use the value for the “expression” in the highlighted section.
In the screenshot below, you can see the output from applying those specific filter values. You will also see those values appear in the viz-level filter bar but in these scenarios, that would typically be hidden using the checkbox available in the component.
Using a Selector and Variable to Apply a Filter
In order to make this capability more useful and enhance the user experience of your application it’s possible to add a selector component to the application page and then use a VB variable to reference the values to be used in the filter JSON.
In the example below, I’ve added a ‘Single Select’ to the application page (labelled ‘Select Prod Category’, that will set the value of a variable ‘vFilterValue’ that is then referenced by the embedded Analytics project, using the method described above for the parameter ‘aDisplayValues’ in the filter JSON.
Viewing the properties of the ‘Single Select’ in more detail, you’ll see the list of values is obtained from a VB business object called ‘BoProdCat’ and the selected value is passed to a variable ‘vFilterValue’.
The following screenshot shows the JSON used in the analytics component expression editor, where the variable ‘vFilterValue’ is referenced.
As before, I’ve included the JSON in a format that’s easier to copy for your own usage:
[
{
"sColFormula": "XSA('<OAC-USER >'.'Sample Order Lines').\"Columns\".\"Product Category\"",
"sColName": "ProductCategory",
"sOperator": "in",
"isNumericCol": false,
"isDateCol": false,
"bIsDoubleColumn": false,
"aCodeValues": [],
"aDisplayValues":[$variables.vFilterValue]
}
]Using a Multi-Selector to Apply a Filter
When using a selector that can pass multiple values to an OAC component, we need to use an array to store those values. I’ll describe an example of how to do this for a UI component (oj-select-many) that allows the user to select multiple cities and filter the analytics visualization, as seen below. Note that to help illustrate this, there is a text area included below the visualization to illustrate what cities have been chosen.
As seen in the properties UI below, we’re obtaining the list of values from a business object, specifically I imported a spreadsheet into a VB business object in order to provide a source for the list of cities that a user can choose from.
The selected values are stored in an array — vCityArray. We can now reference that array in the JSON used to apply the filter to the visualization referenced in the OAC component (note that there is no need for square brackets [ ] in the reference to the array:
[
{
"sColFormula": "XSA('mike.durran@oracle.com'.'Sample Order Lines').\"Columns\".\"City\"",
"sColName": "City",
"sOperator": "in",
"isNumericCol": false,
"isDateCol": false,
"bIsDoubleColumn": false,
"aCodeValues": [],
"aDisplayValues": $variables.vCityArray
}
]The result is shown below:
Multiple Filters
In some cases, you may want to apply filters to multiple columns in the analytics visualization simultaneously. In that case, you need to reference the associated variables (or literal values) in the JSON as follows (this is also an example of the JSON required for filtering on numeric values) :
[
{
"sColFormula": "XSA('mike.durran@oracle.com'.'Sample States').\"Columns\".\"Latitude\"",
"sColName": "Latitude",
"sOperator": "greaterOrEqual",
"isNumericCol": true,
"isDateCol": false,
"bIsDoubleColumn": false,
"aCodeValues": [],
"aDisplayValues":[$variables.vCeilLat]
},
{
"sColFormula": "XSA('mike.durran@oracle.com'.'Sample States').\"Columns\".\"Longitude\"",
"sColName": "Longitude",
"sOperator": "greaterOrEqual",
"isNumericCol": true,
"isDateCol": false,
"bIsDoubleColumn": false,
"aCodeValues": [],
"aDisplayValues":[$variables.vCeilLong]
}
]In this example, I was experimenting with the VB ‘Get Location’ function available to use in an action chain. The end user clicks the button ‘What’s Near You?’ which runs an action chain that obtains the users location, assigns the resulting latitude and longitude to some variables, calls some JavaScript functions to manipulate the data to be easier to use in a filter with the result that the geospatial visualization shows you points of interest to the North East of your location (since the operator being used is “greaterOrEqual”).
Another, and perhaps more useful example is to create a filter that takes a range as input from a user and then calculates the values of latitude and longitude to use in a between operator, essentially filtering on a ‘box’ with your location in the centre. The JSON used to define this filter is as follows:
[
{
"sColFormula": "XSA('mike.durran@oracle.com'.'Sample States').\"Columns\".\"Latitude\"",
"sColName": "Latitude",
"sOperator": "between",
"isNumericCol": true,
"isDateCol": false,
"bIsDoubleColumn": true,
"aCodeValues": [],
"aDisplayValues":[$variables.vMinLat,$variables.vMaxLat]
},
{
"sColFormula": "XSA('mike.durran@oracle.com'.'Sample States').\"Columns\".\"Longitude\"",
"sColName": "Longitude",
"sOperator": "between",
"isNumericCol": true,
"isDateCol": false,
"bIsDoubleColumn": true,
"aCodeValues": [],
"aDisplayValues":[$variables.vMinLong,$variables.vMaxLong]
}
]This example illustrates how to apply a filter to multiple columns, in this case latitude and longitude as well as using multiple variables to work with a between operator. Also note that in this case, we’re not using just the whole number part of the lat/long and thus need to set “bIsDoubleColumn” to true.
Summary
In this blog, I’ve described various scenarios for applying filters to an embedded analytics canvas in a VB application using the OAC component. Extending this approach to then use a UI selector to provide an interactive experience for application users to select from a list of values can add a lot of value and flexibility to your analytic applications.
