Filtering Arbitrary Hierarchies

Question & Answer
, ,
2

Hi @sirwin007, managing complex, multi-tenant hierarchies with row-level security can be challenging, particularly when it comes to filtering based on hierarchy levels. Here is a good reference article.

Here’s a way to address your scenario and ensure that you can apply filters effectively:

Step-by-Step Solution:

  1. Create Hierarchy Level Columns:
  • Ensure that your dataset includes hierarchy level columns for each tenant. For example, have columns like Level1, Level2, …, Level10, each containing the GUIDs or identifiers for the nodes at that level.
  1. Row-Level Security Setup:
  • Ensure your row-level security dataset correctly defines which GUIDs a user can access.
  • This is already set up with a list of GUIDs that each user can see.
  1. Custom Calculation for Display and Filtering:
  • Create a calculated field that combines the level display with the GUIDs, ensuring the GUIDs are hidden or moved off-screen.
  • Use the calculated field to push the GUIDs off-screen in a way that they don’t clutter the visual.
  1. Filter Setup:
  • Set up filters in QuickSight that allow users to select the hierarchy levels.
  • Use a custom control or an advanced parameter to manage the filtering logic based on the user’s selection.

Detailed Implementation:

  1. Create Calculated Fields for Levels:
  • In QuickSight, go to your dataset and create calculated fields for each hierarchy level, for example:
Level7_Display = concat('Level 7 ', repeat(' ', 50), guid)
Level8_Display = concat('Level 8 ', repeat(' ', 50), guid)
...
  1. Using Parameters and Controls:
  • Create parameters for each hierarchy level, e.g., selected_level.
  • Create a control (dropdown) linked to this parameter so users can select the level they want to filter by.
  1. Implementing the Filter Logic:
  • Use a calculated field to determine if a data row should be visible based on the selected hierarchy level and the user’s permitted GUIDs.
  • For example:
Can_View_Row = ifelse(
  contains(${permitted_guids}, ${data_row_guid}),
  1,
  0
)
  1. Applying the Filter:
  • Apply the Can_View_Row calculated field as a filter in your analysis:
    • Add a filter with the condition Can_View_Row = 1.

Example:

Assume we have the following hierarchy and user permissions:

  • User can see from Level 7 down to Level 10.
  • User has permitted GUIDs: ['guid1', 'guid2', 'guid3']

Calculated Fields:

For Level 7:

Level7_Display = concat('Level 7 ', repeat(' ', 50), 'guid1,guid2,guid3')

For Level 8:

Level8_Display = concat('Level 8 ', repeat(' ', 50), 'guid1,guid2,guid3')

Filter Logic:

Can_View_Row = ifelse(
  contains(['guid1', 'guid2', 'guid3'], ${data_row_guid}),
  1,
  0
)

In Analysis:

  • Add a parameter selected_level and link it to a control (e.g., a dropdown with values Level7_Display, Level8_Display, …).
  • Apply a filter where Can_View_Row = 1.

By using this approach, you ensure that the user interface remains clean while still allowing the complex filtering logic needed for your multi-tenant hierarchy structure. The display value and GUIDs are managed in such a way that they do not interfere with the visual representation but still support the required filtering functionality.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!