SAP Analytics Cloud & SAP Datasphere Combined to the rescue.... (Drill Down - Part 2)

[IngoH]

In the first blog we talked about the general concept and what we trying to achieve - a basic drill down option for SAP Analytics Cloud without the mandatory need for a hierarchy.

There have been a few questions on why not using any of the existing options, so before going into more details, lets look at those options.

• Linked Analysis is a great feature, but is designed to create an interaction between different visual components, so it doesn't fit our use case.
• Opening the Navigation Panel via API might be a workaround, but first of all it only exists for Tables and not for charts, and (my view) it requires too many steps to get to our wanted output. 
• Opening the Data Analyzer is similar to using the Navigation Panel and would require too many steps for what we trying to do.
• Using a Hyperlink wouldn't work as it is designed for navigation between stories or from a story to an external URL, and in addition we can only have one hyperlink per widget.

so... we are going to create our own solution using the scripting capabilities from the Analytics Designer and some configuration tables within SAP Datasphere to keep it dynamic.

Now why adding SAP Datasphere into the mix ?

Because I would like to have the option to define the list of dimensions that are available for a Drill Down. You might have a dataset with several dimensions, and some of them are suited for a deeper analysis, and some are not. Think about a dataset with employee details, or customer address details - would you want those kind of dimensions being available for a Drill Down ? Perhaps the answer is "No" and therefore having the option to define the list of available Drill Down dimensions in form of a simple configuration Table in SAP Datasphere is a simple solution for that problem.

So I created a simple dataset in SAP Datasphere:

and I created a configuration table for our Drill Down operation.

The configuration table contains:

• WIDGET_NAME containing the technical name of the widget.
• DATASOURCE containing the technical name of the datasource used for the widget.
• DIMENSION_ID containing the technical ID for the Dimension that can be used for a drill down.
• DIMENSION_LABEL containing the description for the Dimension that can be used for a drill down.
• FEED containing the Feed configuration for chart widgets. The drill down dimensions will be added to this feed.
• HIERARCHY in case instead of a flat presentation there is the need to add the dimension with a configured hierarchy.

Now let's put the pieces together for the Drill Down operation.

First I create a new SAP Analytics Cloud Story with a chart pointing to the dataset and I am adding a table, pointing to the configuration table.

In the real scenario you would hide the table as we only need it for picking up the configuration information.

Now we need to know (1) which widget has been selected to perform a drill down and (2) what the widget type is. So there is currently no API to get the "active widget", so I will make this easy and simple add 2 lines of code for the onSelect event for the chart itself.

SelectedWidgetName ='StoryChart01';
SelectedWidgetType ='Chart';

These two variables are configured as global Script Variables of type string.

As next step we need to:

• Filter the table based on the Widget Name
• Retrieve the list of Dimension ID and Dimension Label as valid Drill Down dimensions.
• Check the list against any used dimensions and remove those from the list.
• Provide the user an option to open the list of drill down dimensions, select one, and perform the drill down.

In addition to the chart and the table, we will add 2 buttons (Drill Down and Remove Drill Down) and we will add a 2 Listboxes for the option to list the dimensions for the Drill Down and for removing the drill down. In SAP Analytics Cloud there is no component to create a menu, therefore I will be using a Listbox to kind of create a menu structure.

As part of the onSelect Event for the Drill Down button I use this code:

//Retrieve Selected Widget
var SelectedWidget  = Application.getActivePage().getWidgets(
	{
	type: WidgetType.Chart, searchPattern: SelectedWidgetName
});

//Getting used Dimensions from the chart
var ChartDimensionList = SelectedWidget[0].getDimensions(Feed.CategoryAxis);

//Getting list of Dimensions from Table
Table_1.getDataSource().setDimensionFilter("WIDGET_NAME",SelectedWidgetName);
var TableResult = Table_1.getDataSource().getResultSet();


//Check which Dimension is used by the chart, which can be used for Drill Down
var filteredArrayID = ArrayUtils.create(Type.string);
var filteredArrayLabel = ArrayUtils.create(Type.string);

for (var z=0; z < TableResult.length; z++)
	{
		var check = ChartDimensionList.includes(TableResult[z]["DIMENSION_ID"].id);
		if (check === false)
			{
				filteredArrayID[z] = TableResult[z]["DIMENSION_ID"].id;
				filteredArrayLabel[z] = TableResult[z]["DIMENSION_LABEL"].id;
			}
	}
//Adding values to Listbox
listbox_DrillDown.removeAllItems();

for (var i=0;i < filteredArrayID.length; i++)
	{
		listbox_DrillDown.addItem(filteredArrayID[i],filteredArrayLabel[i]);
	}

listbox_DrillDown.setVisible(true);

and as part of the onSelect event from the Listbox I use the following code:

//Retrieve Selected Widget
var SelectedWidget  = Application.getActivePage().getWidgets(
	{
	type: WidgetType.Chart, searchPattern: SelectedWidgetName
});

SelectedWidget[0].addDimension(listbox_DrillDown.getSelectedKey(),Feed.CategoryAxis);

listbox_DrillDown.setVisible(false);

As you can see in the scripting the selected widget is being retrieved using the .getWidgets() option. Depending on your own story design, you can also "hardcode" that part to the chart / table ID in your story.

Based on the scripting from above, the user can select the chart by either selecting a bar or a label, and then use the Drill Down button to retrieve the list of dimensions that are available for the option to Drill Down.

So now there is only two steps left. We need to add the scripting for the Remove Drill down button:

//Retrieve Selected Widget
var SelectedWidget  = Application.getActivePage().getWidgets(
	{
	type: WidgetType.Chart, searchPattern: SelectedWidgetName
});

//Getting used Dimensions from the chart
var ChartDimensionList = SelectedWidget[0].getDimensions(Feed.CategoryAxis);

//Adding values to Listbox
listbox_RemoveDrillDown.removeAllItems();

for (var i=0;i < ChartDimensionList.length; i++)
	{
		listbox_RemoveDrillDown.addItem(ChartDimensionList[i]);
	}

listbox_RemoveDrillDown.setVisible(true);

and we need to add the scripting code for removing the selected dimension from the selected chart:

//Retrieve Selected Widget
var SelectedWidget  = Application.getActivePage().getWidgets(
	{
	type: WidgetType.Chart, searchPattern: SelectedWidgetName
});

SelectedWidget[0].removeDimension(listbox_RemoveDrillDown.getSelectedKey(),Feed.CategoryAxis);

listbox_RemoveDrillDown.setVisible(false);

With all of this, you should now have a simple application that shows how you can retrieve a list of dimensions from a configuration table in SAP Datasphere for the chart, provide the list of dimension to the user for Drill Down, as well as offer the option to remove any of the dimensions from the Drill Down.

SAP Analytics Cloud & SAP Datasphere Combined - Drill Down in SAP Analytics Cloud Part 1

SAP Analytics Cloud & SAP Datasphere Combined - Drill Down in SAP Analytics Cloud Part 2