cascading hierarchy filter issue

#1

Hi,

I’m trying to use a cascading hierarchy filter, and I have some issues.

I have a hierarchy built upon this query:
SELECT [dbo].[ContractLocations].[Id]
,[Location]
,[CountryZoneId]
,[dbo].[ContractCountryZones].[Zone]
,[dbo].[ContractCountryZones].[CountryId]
,[dbo].[Countries].[Name] AS Country
,[dbo].[ContractLocations].[Deprecated]
FROM [dbo].[ContractLocations] INNER JOIN [dbo].[ContractCountryZones] ON [dbo].[ContractCountryZones].[Id] = [dbo].[ContractLocations].[CountryZoneId] INNER JOIN [dbo].[Countries] ON [dbo].[Countries].[Id] = [dbo].[ContractCountryZones].[CountryId] WHERE [dbo].[ContractLocations].[Deprecated] = 0 AND [dbo].[ContractCountryZones].[Deprecated] = 0

in order to have a filter for setting the location of a meeting. In the Dashboard I have set a cascading hierarchy filter with no value selected by default.
cascading%20hierarchy%20filter1
When I do the first selection, everything is ok:
cascading%20hierarchy%20filter2

The behaviour I was expecting was that the location is set to “(none)” if the “CountryZone” is changed (up to here, it works fine), and both “Country Zone” and "Location set to “(none)” if the value of the top reference (“Country”) is changed. BUT, if I change the “Country” value then I got this:
cascading%20hierarchy%20filter3

Why isn’t the value of the Location set to “(none)”, as the value shown isn’t valid anymore? What have I missed?
Thanks in advance.

#2

Hi Olivier,

I’m not certain but perhaps you would like Dependent Filters, where one filter determines what values show in a different filter.

https://www.dundas.com/support/learning/documentation/create-view-content/filters/adding-filters

Thank you

#3

Hi Derek,
First of all, thank you.
The solution you’re proposing implies that I use several filters, when the “cascading hierarchy filter” is a unique filter associated with a hierarchy having several levels (in my case level A, B and C). I’ll give a try to separate them in 3 different filters with dependencies, but it means also that I need to have 3 different parameters declared (one per level), and that the query needs to have 3 separated fields (one per level), I already used that kind of solution, and it’s a bit heavy.
I was hoping that the “cascading hierarchy filter” could have managed more than 1 level, which would have simplified a lot the query (as only the lowest level would have needed to be included), the design and the settings.
And if the functionment of the cascading hierarchy filter is to don’t reset the visualization when the upper level is modified (because the value itself is not valid anymore, even if it’s shown), then I don’t see the point of having it. That’s why I was asking if there was a way to have a consistent display. From the end-user point of view it’s really confusing, the end-user sees a value that has been removed from the parameterValue object, but is still shown as if it was the one selected (even if its color is lighter, that’s a bit too “subtle”).

#4

Hi,

This is the designed behavior of cascading hierarchy filters.

If you would like the child node to get automatically set to the selection made in the parent hierarchies, please enable the option ‘Set Value on Any Filter Change’ option in the properties panel of the cascading filter. This way even if the middle hierarchy is ‘none’, the child node will show all the values in the category selected in the parent node. Please see below screenshots:

image
image.png1718×685 45.2 KB

image

#5

Hi Abigail,

Thank you for your answer. I already tried this option before creating this topic, adding this option is even worse for the filter’s behaviour I was expecting to have, because, not only a value is still shown, but it’s a value that corresponds to a upper level. And the aim is to have at the end a single value selected at level C, not a list of values.

My aim is to have a functionment like this, for the visualization:

  • if only a level A value is set, level B and level C parts of the filter should show “(none)”
  • if only a level A and level B values are set, level C part of the filter should show “(none)”
  • if the level A value is modified, level B and level C parts of the filter should be reset to “(none)”
  • if the level B values is modified, level C part of the filter should be reset to “(none)”

in order to force the end-user to select first a level A value, then a level B value, and finally a level C value. But here, with this filter behaviour, I cannot have a consistent visualization, even if the values behind the scene are consistent, and the end-user can trust only what he/she sees.

Olivier

#6

Hi Olivier,

Unfortunately, the way cascading filters have been designed is such that the visual will update only when the last child node is selected, unless you enable the option ‘ Set value On Any Filter Change’ as discussed earlier.

You might want to consider using Dependent Filters, as an alternative, if you can use the dimensions of the levels of your multi-level hierarchy separately, without having them promoted with a Custom Multi-Level Hierarchy.

Thanks!