Dynamically Modifying Hierarchies

Introduction

I was asked to create a hierarchy that can be modified dynamically from within a text area with a multiple select list box.

I failed to find anything on the web that can solve my problem, so I create the attached ironPython script/component.

The instruction for exploiting the script are the following

1. Create a Document Property with "String List" as data type that will hold the default order of the targeted hierarchy. But in the current version of Spotfire, the option of creating a "String List" Document Property does not exist yet. So, to create a predefined default column list for the targeted hierarchy, follow the following steps:
   1. In a Text Area, create a multiple select list box with fixed values.
   2. Fill the list with the Display Names/Values.
   3. Save and close the text area.
   4. Select everything from the multiple select list box.
   5. Delete the multiple select list box from the text area and save.
   6. Verify in the Document Properties that the property is created.
2. Create the targeted hierarchy.
3. In the text area that will hold the dynamic hierarchy modification feature, add a multiple select list box and fill it with the values of the previous list box. That list box will act as an action listener to the user inputs. Save the text area before passing the next step.
4. Attach an ironPython script to the multiple select list box. By doing so the script will act as an action event. In other words, each time items are selected in the list box, the script is fired up.
5. Insert the following code into the script:

   from Spotfire.Dxp.Data import *
   from System.Collections.Generic import List
   from System import Array
   
   #######################################
   # Parameters:
   #   targetDataTable: The data table containing the hierarchy to be modified (General Hierarchy)
   #   columnHierarchy: The selected column from the hierarchy
   #   defaultColumnHierarchy: The default column order in the hierarchy
   #   generalHierarchy: The hierarchy name in the targeted data table to be modified
   #######################################
   
   docPropertyValues  = Document.Data.Properties.GetProperty(DataPropertyClass.Document,columnHierarchy).Value
   mainHierarchyOrder = Document.Data.Properties.GetProperty(DataPropertyClass.Document,defaultColumnHierarchy).Value
   newHierarchyCol = []
   
   if targetDataTable.Columns.TryGetValue(targetedHierarchy):
       targetDataTable.Columns.Remove(targetedHierarchy)
       for col in mainHierarchyOrder:
           if col in docPropertyValues:
               newHierarchyCol.append(col)
   else:
       newHierarchyCol = mainHierarchyOrder
   
   definition=HierarchyDefinition(HierarchyNestingMode.Nested,newHierarchyCol)
   targetDataTable.Columns.AddHierarchyColumn(targetedHierarchy,definition)

    
6. Add the following parameters:
   1. targetDataTable
      • data type: data table
      • description: The data table containing the hierarchy to be modified (Targeted Hierarchy)
   2. columnHierarchy
      • data type: string
      • description: The selected column from the hierarchy
   3. defaultColumnHierarchy
      • data type: string
      • description: The default column order in the hierarchy
   4. targetedHierarchy
      • data type: string
      • description: The hierarchy name in the targeted data table to be modified
7. Save everything and it should work.

Where did I use that script:

I was asked to add a dynamic hierarchy to the vertical axe of a cross table. But the usage of this combo is not limited to a cross table, it can be exploited elsewhere.