Filtering Arbitrary Hierarchies

Question & Answer
, ,
1

My dashboard and data are multi-tenant.

Any given tenant defines their unique hierarchy, and the Reader is associated somewhere into that hierarchy where they can see from that node down. A given data row for the visual is at a specific point in the hierarchy and has that node’s GUID. Using row-level security I built a list of GUIDs that user can see.

My last issue is to be able to add a filter. Assume a tenant defined a 10 level hierarchy, and this Reader can see from level 7 down to 10. A user could select any Level in the filter values above 8 and see their permitted rows, or below 7 and see just a subset of their permitted rows. To populate the filter, I have another dataset with row-level security to provide the full tenant unique hierarchy.

I wanted to have a display value and then a bunch of spaces to hide a list of GUIDs that selection allows. However, the filter control inconveniently stripped out all those spaces. Now the visual shows “Level 8 guid1,guid2,…”. I need this list to write a filter that the data row’s GUID is IN the filter value (split by text/metadata).

Anyway to push that text off the screen? Or a better hack given hierarchy filters are not supported?

“Level 8 --guid,guid,guid,guid”

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!