Skip to Content

Introduction

In this edition of the Design Studio Innovation Series, I’m going to describe an approach for implementing a hierarchy filter based on standard and SDK Development Community components, to take advantage of complementary features of both.  With the standard functionality, we can implement cascading filters with the Dropdown Box and List Box components but neither of these cater for hierarchy structures, which is the requirement I will address here.

The Result

The solution allows compact, level-by-level hierarchical drill-down, with the option to drill back up level-by-level or go straight to the beginning.  Before going into the details, here’s the end result in the form of an animated gif (Click on the image if it doesn’t start playing immediately.  It may take a little bit of time to load).  In this example, the hierarchy filter is driving a chart:

HierarchyFilterDemo.gif

For those of you who want to get straight to it, a sample BIAPP is attached at the end.  For those interested in the details, read on!…

Background

This post was inspired by the Cascade Filter for Hierarchy Dimension question from Oscar Olivares Pous.  Since the requirement involved a fixed level hierarchy structure, I was able to propose a solution that relied only on standard functionality based on the Crosstab component.  A further thought then occurred to me about how could the solution be extended to achieve dynamic filtering for drilling down and up a hierarchy structure with many levels of varying depth, as shown below?:

ExpandedHierarchy.png

We could certainly use a Crosstab component as-is, as shown above but this can become unwieldy as nodes are expanded and collapsed, with the added need for scrolling when the structure is large.  Similarly, use of the standard Dimension Filter also requires node manipulation and scrolling, with several clicks to find the required item and then back out to apply, as shown below:

DimensionFilter.png

The Approach


The solution is centred around a standard Crosstab component because unlike the Dropdown Box and List Box, it supports display of a hierarchy structure and selection of nodes.  Drilling down a hierarchy one level at a time by applying the setFilter() method on the data source assigned to a Crosstab is relatively straight forward.  However, the problem arises when we need to implement drill-up functionality to move back in the hierarchy.  The problem here is we need to somehow “remember” the drill path and parent-child relationships.  As far as I could see, there was no easy way to achieve this with standard scripting alone.  We need to store the drill path in some kind of structure that we can manipulate and look up, so I took a look at the Design Studio SDK: Collection Utility Component from the SDK Development Community as a possible candidate and it did the trick!

Note:  The Collection Utility component currently doesn’t seem to allow deletion of specific entries so all drill paths that the user traverses are stored the first time the user goes down a particular path.  I think a great enhancement to the Design Studio SDK: Collection Utility Component would be support for stack functionality with Push and Pop methods.  This would allow a more efficient mechanism for recording and traversing drill paths and only the current drill path would need to be tracked.

So now let’s go through a step-by-step breakdown.

Data Source

In order to allow level-by-level drill-down, the Initial View of the main data source is configured as shown below, expanded to level 2:

HierarchyLevel.png

Components

My aim was to implement a modular, reusable solution, so on the UI side I have effectively created a composite component by combining a Home Button, Back Button and Crosstab within a Panel component:


CompositeComponent.png


In the outline panel it looks like this:

CompositeComponent.png


To track the drill path, I have I have used two collections implemented with the Design Studio SDK: Collection Utility Component:

Collections.png

The Hierarchy collection keeps track of the drill path and the Variable collection tracks the previous node to allow reversal back up the drill path.  I probably could have used a Global Script Variable to perform the same function as the Variable collection but thought it would help make the solution a little more modular.

For demo purposes I’ve also included the SDK Development Community Fiori App Header component.

CSS

I have adapted Haripriya Gopi’s very useful method for Default Selection in a Crosstab to implement the following functionality:


  1. Highlight the first row as the default selection on startup
  2. Highlight the first row as the default selection when drilling back to the previous level


The CSS file for the application has been named defaultselection.css and consists of the following code:



.highlightrow .sapzencrosstab-RowHeaderArea tr:nth-child(1).sapzencrosstab-HeaderRow td:nth-child(1)
{
background-color: #7bc3ef !important;
}






The CSS file should be assigned to the Application Properties as shown below:


ApplicationPropertiesCSS.png

The CSS Class highlightrow should then be assigned to the Crosstab component as shown below:

CrosstabCSS.png

Global Script Variables

I have defined a set of constants as Global Script Variables so they can be maintained centrally, as shown below:

GlobalScriptVariables.png

Global Script Functions

To keep the code modular, several script functions have been created under the HIERARCHY_FILTER global scripts object as shown below:

GlobalScriptFunctions.png

Each of these functions are self-contained in that all necessary external data are passed as input parameters.  A great feature of global script functions is that you can even pass in components and data sources as parameters and then perform actions on these such as setting properties or filtering, so you can apply the same standard process to multiple components / data sources if needed.

I’ll now describe each of the functions in logical order.

initialize:

This function is called from the “On Startup” event script of the application.  It performs the following main tasks:

  • Verifies that the dimension we want to drill on has an active hierarchy assigned to it
  • Initializes the collections for the hierarchy structure and previous node
  • Sets the default text of the filter value
  • Disables navigation, sorting and column resizing on the filter crosstab
  • Disables the back button

It requires the following parameters:

Input Parameter Type Description
pCrosstab Crosstab Crosstab for hierarchy filter
pHierarchyDimension String Dimension for hierarchy filter
pHierarchyRoot String Root of hierarchy
pHierarchyRootDescription String Hierarchy root description
pHierarchyPrefix String Hierarchy prefix
pDataSource DataSourceAlias Data Source for hierarchy filter
pHierarchyCollection org.scn.community.utils.Collection Collection for hierarchy
pVariableCollection org.scn.community.utils.Collection Variable collection for hierarchy
pBackButton Button Back Button
pTitleText Text Selected Item Text
pPreviousNodeID String Previous Node Identifier key used in variable collection lookup

The script is coded as follows:


//
// Initialise Hierarchy Filter
//
// Verify active hierarchies exist
if (!pDataSource.isHierarchyActive(pHierarchyDimension))
{
  APPLICATION.createErrorMessage("No active hierarchy for data source " + pDataSource.getInfo().dataSourceName);
 return false;
}
// Initialize Collections
var hierarchyRoot = pHierarchyPrefix + pHierarchyRoot;
pHierarchyCollection.addItem(hierarchyRoot, '', 0.0, pHierarchyRootDescription); // Initialize first index item with hierarchy root node
pVariableCollection.addItem(pPreviousNodeID, hierarchyRoot, 0.0, pHierarchyRootDescription); // Initialize previously selected node
// Set root node description as default text
pTitleText.setText(pHierarchyCollection.getEntryByKey(hierarchyRoot).prop1);
// Initialise crosstab settings
pCrosstab.setHierarchyNavigationEnabled(false); // Disable navigation
pCrosstab.setSortingEnabled(false); // Disable sorting
pCrosstab.setColumnResizingEnabled(false); // Disable column re-sizing
pBackButton.setEnabled(false); // Disable Back button
return true;






drillDown:

This function is called from the “On Select” event script of the filter crosstab.  It is the engine of overall functionality and performs the following main tasks:


  • Initializes variables
  • Records the drill path
  • Drills down to the next level
  • Applies a filter to the target data source


It requires the following parameters:


Input Parameter Type Description
pCrosstab Crosstab Crosstab for hierarchy filter
pHierarchyDimension String Dimension for hierarchy filter
pDataSource DataSourceAlias Hierarchy data source
pTargetDataSource DataSourceAlias Target data source for filtering
pHierarchyCollection org.scn.community.utils.Collection Collection for hierarchy
pVariableCollection org.scn.community.utils.Collection Variable collection for hierarchy
pBackButton Button Back Button
pHierarchyNodePrefix String Hierarchy node prefix
pPreviousNodeID String Previous node ID for variable collection lookup
pNotApplicable String Not Applicable constant value
pAllMembers String All members filter value

The script is coded as follows:



//
// Drilldown Processing
// Called from OnSelect event of Crosstab
//
//Initialise variables
var parentNodeKey = "";
var selectedFilterKey = pCrosstab.getSelectedMember(pDrillDimension).internalKey;
var selectedFilterText = pCrosstab.getSelectedMember(pDrillDimension).text;
var indexCheck = pHierarchyCollection.getIndexByKey(selectedFilterKey) + '';
var nodePrefix = selectedFilterKey.substring(0, pHierarchyNodePrefix.length);
var previousNodeKey = pVariableCollection.getLabelByKey(pPreviousNodeID);
var rootNode = pHierarchyCollection.getKeyByIndex(0);
// Add drill path to collection only if it has not already been added from a previous drilldown
if (indexCheck == pNotApplicable && selectedFilterKey != pAllMembers && nodePrefix == pHierarchyNodePrefix)
{
  parentNodeKey = previousNodeKey;
  pHierarchyCollection.addItem(selectedFilterKey, parentNodeKey, 0.0,selectedFilterText);
}
// Drilldown to the next level only if the selected node is a hierarchy node
// Update previous node
if (nodePrefix == pHierarchyNodePrefix)
{
  pDataSource.setFilter(pDrillDimension, selectedFilterKey);
  pVariableCollection.removeAllItems();
  pVariableCollection.addItem(pPreviousNodeID, selectedFilterKey, 0.0, selectedFilterText);
}
else
{
  pCrosstab.setCSSClass(""); // Clear first row highlighting if selection is leaf node
}
// If the Back button is not enabled then enable it if conditions are met
if (!pBackButton.isEnabled() && selectedFilterKey != pAllMembers && selectedFilterKey != rootNode)
{
  pBackButton.setEnabled(true);
}
// Apply filter to the target if filter is not "all members"
if (selectedFilterKey != pAllMembers)
{
  HIERARCHY_FILTER.filterTarget(pTargetDataSource, pDrillDimension, selectedFilterKey, selectedFilterText, TEXT_CHART_TITLE);
}






filterTarget:

This function is called from the drillDown, back and home functions.  It performs the following main tasks:


  • Filters the target data source
  • Updates the text field to display the new filter value


It requires the following parameters:

Input Parameter Type Description
pDataSource DataSourceAlias Target datasource for applying filter
pFilterDimension String Dimension to be filtered in target data source
pFilterKey String Dimension member key to be filtered in target data source
pFilterText String Dimension member text
pText Text Text component to display selected dimension member


The script is coded as follows:



//
//  Filter Target
//
pDataSource.setFilter(pFilterDimension, pFilterKey);
pText.setText(pFilterText);  // Update text to display new filter value







back:


This function is called from the “On Click” event script of the Back button.  It performs the following main tasks:


  • Initializes variables
  • Updates the previous hierarchy node
  • Drills back to the previous hierarchy node
  • Applies a filter to the target data source


It requires the following parameters:

Input Parameter Type Description
pDataSource DataSourceAlias Hierarchy data source
pTargetDataSource DataSourceAlias Target data source for filtering
pHierarchyCollection org.scn.community.utils.Collection Collection for hierarchy
pVariableCollection org.scn.community.utils.Collection Variable collection for hierarchy
pBackButton Button Back Button
pText Text Text component to display selected dimension member
pHierarchyDimension String Dimension for Hierarchy Filter
pPreviousNodeID String Previous node ID for variable collection lookup
pCrossTab Crosstab Crosstab for Hierarchy Filter
pRowSelectionClass String CSS Class to highlight first row of crosstab


The script is coded as follows:



//
// Back button (drillup) Processing
//
// Initialise Variables
var previousNode = pVariableCollection.getLabelByKey(pPreviousNodeID);
var parentNode = pHierarchyCollection.getLabelByKey(previousNode);
var parentNodeText = pHierarchyCollection.getEntryByKey(parentNode).prop1;
var rootNode = pHierarchyCollection.getKeyByIndex(0);
previousNode = parentNode;
var previousNodeText = parentNodeText;
// Update Previous Node
pVariableCollection.removeAllItems();
pVariableCollection.addItem(pPreviousNodeID, previousNode, 0.0, previousNodeText);
// Set filter to drill back to previous node (parent node)
pDataSource.setFilter(pHierarchyDimension, previousNode);
// Apply highlight to first row of crosstab
pCrossTab.removeSelection();
pCrossTab.setCSSClass(pRowSelectionClass);
// Disable Back button if top of hierarchy has been reached
if (previousNode == rootNode)
{
  pBackButton.setEnabled(false);
}
// Apply filter to target data source
HIERARCHY_FILTER.filterTarget(pTargetDataSource, pHierarchyDimension, previousNode, previousNodeText, pText);







home:


This function is called from the “On Click” event script of the Home button.  It performs the following main tasks:


  • Gets the root node of the hierarchy
  • Resets the previous hierarchy node
  • Removes the hierarchy filter
  • Filters the target data source based on the root node


It requires the following parameters:

Input Parameter Type Description
pDataSource DataSourceAlias Hierarchy data source
pTargetDataSource DataSourceAlias Target data source for filtering
pHierarchyCollection org.scn.community.utils.Collection Collection for hierarchy
pVariableCollection org.scn.community.utils.Collection Variable collection for hierarchy
pBackButton Button Back Button
pHierarchyDimension String Dimension for Hierarchy Filter
pPreviousNodeID String Previous node ID for variable collection lookup
pCrossTab Crosstab Crosstab for Hierarchy Filter
pRowSelectionClass String CSS Class to highlight first row of crosstab


The script is coded as follows:



//
// Home Button Processing
//
// Get root node information
var rootNodeKey = pHierarchyCollection.getKeyByIndex(0);
var rootNodeText = pHierarchyCollection.getEntryByKey(rootNodeKey).prop1;
// Reset previous node pointer
pVariableCollection.removeAllItems();
pVariableCollection.addItem(pPreviousNodeID, rootNodeKey, 0.0);
// Remove filter to reset hierarchy to top level
pDataSource.clearFilter(pHierarchyDimension);
// Remove previous row selection and highlight first row as default
pCrossTab.removeSelection();
pCrossTab.setCSSClass(pRowSelectionClass);
// Disable Back button
pBackButton.setEnabled(false);
// Filter target data source based on root node
HIERARCHY_FILTER.filterTarget(pTargetDataSource, pHierarchyDimension, rootNodeKey, rootNodeText, TEXT_CHART_TITLE);







Startup Script:


HIERARCHY_FILTER.initialize(CROSSTAB_FILTER, cDrillDimension, cHierarchyRootNode, cHierarchyRootNodeDesc, cHierarchyPrefix, DS_1, HIERARCHY_COLLECTION, VARIABLE_COLLECTION, BACK_BUTTON,TEXT_CHART_TITLE, cPreviousNode);







Component Scripts:


Crosstab:


HIERARCHY_FILTER.drillDown(CROSSTAB_FILTER, cDrillDimension, DS_1, DS_2, HIERARCHY_COLLECTION, VARIABLE_COLLECTION, BACK_BUTTON, cHierarchyNodePrefix,cPreviousNode,cNotApplicable ,cAllMembers);







Home Button:


HIERARCHY_FILTER.back(DS_1, DS_2,HIERARCHY_COLLECTION, VARIABLE_COLLECTION, BACK_BUTTON, TEXT_CHART_TITLE, cDrillDimension, cPre






Back Button:


HIERARCHY_FILTER.back(DS_1, DS_2,HIERARCHY_COLLECTION, VARIABLE_COLLECTION, BACK_BUTTON, TEXT_CHART_TITLE, cDrillDimension, cPrviousNode, CROSSTAB_FILTER, cRowSelectionClass);






Conclusion


I hope the hierarchy filter approach described here is useful for those of you who have such a requirement.  The BIAPP in the animated demo above is attached.

Comments and questions are welcome as always.

Enjoy.

Blog Series Index:  Design Studio Innovation Series – Welcome



To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Werner Hillebrand

    The attached applikation does not work with DesignStudio 1.5.

    During import the following error message appears
         The file SCN_BLOG_HIERARCHY_FILTER_DEMO.zip it not a valid export file.

      (Inclusive misspelling đŸ™‚  it not instead of is not)

    furthermore there are a few errors in the example code

    the variable “pDrillDimension” is used but doesn’t exist.
    Guess “cDrillDimension” would be right.

    In addition the Component Skripts for:

    CROSSTAB und HOME_BUTTON

    are incomplete/wrong.

    (0) 
    1. Mustafa Bensan Post author

      Hi Werner,

      The original ZIP file was for Design Studio 1.4 and therefore not an export file.  I have added a new export file for Design Studio 1.5 which you should be able to import directly with the import function.  Let me know how you go.

      The “pDrillDimension” exists in the global script function definitions.  I accidentally omitted from the above tables.  The CROSSTAB code is complete but the HOME_BUTTON script appears to have been truncated.  In any case, if you import the new archive file for DS 1.5 you will have the complete source code.

      Regards,

      Mustafa.

      (0) 
        1. Mustafa Bensan Post author

          Hi Karthik,

          If you are running DS 1.4 SP2 then you need to download the archive file SCN_BLOG_HIERARCHY_FILTER_DEMO.zip.txt.zip above.  In order to extract it you may need to change the file extension just to “.zip”.  Then you will need to manually install the BI app.

          Regards,

          Mustafa.

          (0) 
      1. Werner Hillebrand

        Hi Mustafa,

        thx for your quick response and your upload. Import doesn’t work because of the following error Message:

        The current platform does not support the upload of MIMEs. See SAP note 2111546.

        Guess I should contact my Administrator đŸ˜‰

        The misstyping in the CROSSTAB code is to find in the following sequence:

        BACK_BUTTierarchyNodePrefix

        Guess

        BACK_BUTTON, cHierarchyNodePrefix

        should be right.

        Greetings

        Werner

        (0) 
        1. Mustafa Bensan Post author

          Hi Werner,

          Yes, based on the note it seems you may need to apply the upgrade to use the import feature.  However, as an alternative approach to get you started in the meantime, you can manually install the BI app on your Design Studio PC by downloading and extracting the BI app from the DS 1.4 file SCN_BLOG_HIERARCHY_FILTER_DEMO.zip.txt.zip after changing the file extension from “.zip.txt.zip” to “.zip”.  Let me know if that works.

          Sorry about the typo for the CROSSTAB.  The script should read as you’ve indicated, as shown below.  I will correct it in the blog post as well.

          HIERARCHY_FILTER.drillDown(CROSSTAB_FILTER, cDrillDimension, DS_1, DS_2, HIERARCHY_COLLECTION, VARIABLE_COLLECTION, BACK_BUTTON, cHierarchyNodePrefix,cPreviousNode,cNotApplicable ,cAllMembers);

          Regards,

          Mustafa.

          (0) 
  2. Kumar Surisetti

    Hi Mustafa,
    i am using BW hierarchy in Design Studio  and implemented below code to select hierarchy and passing parameter.
    var selDim = me.getSelectedMembers(“0COSTCENTER”);
    var DSFilter = “”;
    selDim.forEach(function(element, index) {
    DSFilter = DSFilter + element.externalKey + “;”;
    CC_Sel = element.externalKey;
    CC_Sel1 = element.text;

    });

    DS_1.setFilterExt(“0COSTCENTER”, DSFilter);

    we have new requirement to go up  hierarchy(one step back only for hierarchy).

    i have used below code to get one step back in all over dashboard.

    var ButtonState = State.isBackOneStepAvailable();
    if (ButtonState) { State.backOneStep();
    but we need only for hierarchy.

    any idea how we can implement this?

    Thanks,
    Kumar

    (0) 
    1. Mustafa Bensan Post author

      Hi Kumar,

      Can you clarify your approach further by:

      1) providing screenshots of your application;

      2) confirming what component “me” is referencing in the me.getSelectedMembers(“0COSTCENTRE”) statement?

      Also, you appear to be asking a question that is different to the solution I have provided above.  As such, under the SCN Rules of Engagement, you should post a new question so that the whole community can assist.

      Regards,

      Mustafa.

      (0) 

Leave a Reply