When working with dashboards and analytical applications we only want to retrieve the data that is necessary for our analysis or display. For this reason, we want to pass filters to our data sources to limit the amount of data that we are working with. In this post, we are going to first see how to pass a standard filter value to a HANA live connection.
Work in HANA
The first thing that we will need to do is create a HANA model that will accept a filter value. I will be using a data source call RETAIL_DATA, but you can use any source that has dimension values. Below is the model that will be used in the blog:
It is a simple model where we include our filter dimension as low as we can in the model. In this case our filter dimension is COUNTRY, so lets place a filter on COUNTRY. To do this, we simply right-click on the COUNTRY dimension, in either the table or the Output, as shown below, and select Apply Filter.
In the Apply Filter dialog, lets enter United Kingdom, and then click OK.
Now, if we double-click the Expressions object in the Output section, we should be able to see the syntax for our filter.
So our filter expression is simply (“Country” = ‘United Kingdom’). However, we want our filter to be dynamic, so we want to replace ‘United Kingdom’ with a variable. To do this, we will first create an Input Parameter. In the Output section, right-click on Input Parameters and then select New…
Define the Input Parameter as shown below:
Actually, this how I will define my variable. If you are working with different data, then simply follow the general format. Many people start the name of their input parameters with the prefix IP_, as this makes parameters easily identified. The Parameter type is Direct. Direct parameters are really fast, because they do not have to present, or limit themselves to a list of values. It is often a good idea to give a default value to input parameters, because if a value is not provided, then predictable data will still be returned.
Now, lets revisit our filter on COUNTRY. To do this, right-click on COUNTRY and then select Edit Filter, as shown below.
In the Apply Filter dialog, we need to replace the value United Kingdom with our IP_COUNTRY input parameter. An easy way to do this, is to first click on the ellipsis at the end of the Value field, as shown in the screenshot below. In the Value Help Dialog, select Input Parameters as the Type, and then select IP_COUNTRY from the list and click OK,
In the screenshot below, it is shown that the use of an input parameter requires it to be enclosed within double dollar signs – $$IP_COUNTRY$$.
Click OK to modify the filter. Now, we simply bring up the fields to the Semantic node and activate the model.
When we run the model, we will be prompted for a value for country, and the default value is United Kingdom, as shown below.
Work in SAP Analytic Cloud
We now need to create a SAP Analytic Cloud model. Open SAP Analytic Cloud and select Create>Model from the menu.
For the data in the model select Get data from a datasource, and then select Live Data Connection.
In the Live Data Connection dialog select SAP HANA as the system type. The connection will have to be predefined. Please ask your administrator if you do not know the connection to your HANA system. For the data source, enter the name of your model. In my case, the model was called FILTER_DISCUSSION. Finally click OK.
After clicking OK to create the model in SAP Analytic Cloud, you will be placed in the Measures view of the model. Here, you should see your measures. NUM_CARTS is my measure.
You can also click the All Dimensions menu item to see your dimensions. To see the Variables in the HANA model, click the wench in the menu, and then select Variables, as shown below.
We should see the variable, which in my case is IP_COUNTRY.
Click Cancel to dismiss the Variables dialog, and then click the save button in the menu to save the model. Now, select Create>Analytic Application from the menu, so we can utilize our model in an application.
For our application, lets place a chart that will list all of the countries that are available and a chart that will list products and quantities. We will also place a text field to show the selected country.
We will select a country from the table, and then pass it to the data source for our chart to filter the product and quantities to the selected country.
The application will contain four widgets, as shown above. The data source for the Country chart is not the model that we created in HANA. The data source simply contains a list of countries. It is assumed that you will be able to place the widgets, so we will skip ahead to placing the Products chart chrtProduct.
When the FILTER_DISCUSSION model is added to the chart, we will be presented with the following dialog:
We can see our HANA model default value of United Kingdom. There are also two other checkbox options. You can set these as you prefer, as they will not affect how our application will run.
Now that we have the widgets placed on the canvas and our model being utilized in a chart, lets write some code to make it all work. We will place our code in the onSelect method of the chrtCountry chart.
In the onSelect, write code similar to the following:
// Get the selection object for the Country chart
var selCountry = chrtCountry.getSelections();
//Place country text in our text field
Note: If you are unsure of how to write the above code, then please read my blog titled
Retrieving Selections from a Chart (Analytics Designer).
This code will place the selected value from the chart into a text field on the canvas of your application. With a chart, you can have many selections. However, we are only looking at the first selected. If more than one country is selected, then this application will only recognize the first selected. If there is more than one selection, and countries are deselected until there is only one country selected, then that country will be recognized as selected.
Next, we will enter the code that will set the variable in our model to the selected country.
I placed this bit of code in a code box, so we can view it in a single line. With this code we are retrieving the data source object for chrtProduct. We then set the variable IP_COUNTRY to the selected value in the Countries chart.
Above are two states of the application. One with United Kingdom selected and the other with Germany selected.
The advantages of passing parameters to filter is that we can place the burden of calculating and summarizing on the HANA server, which is something that it is very good at. In addition, we can also limit the amount of data that our Analytic Application has to process. By reducing the calculation burden and the amount of data in our applications, our applications can run much more efficient.
I have not mentioned how to use wildcards in your parameter filters. I will write that blog next, and it will be titled: Wildcards in HANA Parameters in SAP Analytic Designer.