How to display ragged hiearchy

Question & Answer
, ,
1

I need to show hierarchy info based on who is viewing the dashboard. The hierarchy data is ragged/variable depth, and can be anywhere from 1 to 10 layers deep. The dataset being used has flattened hierarchy data meaning it has nulls in any layer that isn’t used. I have a pivot table setup using the hierarchy view that shows the appropriate records, but it is showing nulls all the way down to the 10th layer for every result

so today it looks something like

image
image494×604 4.16 KB

but i’d want it to look like:

image
image423×349 2.19 KB

and if user 2 was logged in, they’d see the layers below them like:

image
image313×294 2.07 KB

any ideas on how to achieve this?

2

Hi!

In order to avoid showing null values and show hierarchy dynamically, my first thought goes towards using calculated fields! You can create a calculated field to show only the hierarchy that is relevant to the current logged in user (eg. WHEN userID = 'user1' and so on), but there are a few solutions that you can use depending on your use case:

  • Hide null values with an empty string and use a filter to exclude rows where hierarchy columns are empty
  • Applying row level security (map users to relevant hierarchy) then use parameters based on the users role/id
  • You can also pre-process the data before Quicksight and collapse any unused levels (combine it with the higher one)

You can also combine these solutions to satisfy your use case or special requirements! Feel free to ask any further follow up questions.

More on Conditional Formatting.
More on Row Level Security (RLS)

Thanks,

Arnav

2 Likes
3

Thanks for the ideas! i’ll give this a try and see if i can get something to work

4

Hey, so still not sure how to get rid of the empty layers of hierarchy. Maybe i didn’t fully explain this, but the hierarchy is shown with 10 columns (the columns are always there for any record). In almost all cases the 10th level is blank/null. If i add a filter to remove records where the 10th row is null it removes almost all the records.

Is there a setting in the hierarchy pivot table view that just removes empty cells or something? or possibly a way to collapse the rows above the blank values?

1 Like
5

Hi @binkyben,
It’s been awhile since last communication on this thread. Were you able to find a work around for your case or are you still encountering the same issues?
From my understanding, I’m not aware of a work around that’s currently available to only remove empty cells or collapse the rows above blank values.

Let us know if you have any additional questions, if we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

1 Like
6

Hi @binkyben,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!