Contents x
    Create hierarchical Tree filters
      • Print
      • Dark
        Light
      • PDF
      Contents

      Create hierarchical Tree filters

        • Print
        • Dark
          Light
        • PDF
        Article summary

        Intro to Hierarchical Tree Filter

        SQLSpreadsPremiumLogo.png
        Hierarchical Tree Filters are available in SQL Spreads Premium and above

        SQL Spreads can create a multi-level (hierarchical) Tree Filter.

        The user always selects the item at the lowest level, like the City in the example below:

        NestedImportFilters.png

        Unlimited filter levels

        From SQL Spreads 7.5 you can add an unlimited number of Groups (In earlier versions you can add up to five levels).

        Create a Hierarchical Tree Filter

        To add a new level in a Tree Filter, add a new column in the filter SQL query and name it with AS Group1_Text as in the example below:

        SELECT
   city.[CityName] AS [Text],
   city.[Id] AS Code,
   country.[Name] AS Group1_Text,  -- Show country
  'true' Group1Expanded            -- Always expand the countries
FROM
  [dbo].[Cities] AS city
INNER JOIN
  [dbo].[Countries] as country ON city.CountryCode = country.CountryCode

        You can define an unlimited number of levels by naming columns to Group1_Text, Group2_Text, and so on:
        Group1_Text
              Group2_Text
                    ...
                          Text, Code

        Note 1

        Please note that you must name the columns using AS, eg “ AS Group1_Text” or “ AS Group1Expanded”, otherwise SQL Spreads will not detect the filter group.

        Note 2

        In versions before SQL Spreads 7.5, the groups where named Group1, Group2, etc. In SQL Spreads 7.5 and later, you can use either Group1 or Group1_Text

        Filter the data based on the bottom level in a hierarchical Tree Filter

        To filter the loaded data based on the bottom node in a hierarchical Tree filter, create a Custom SQL Filter in the same way as for a normal Tree filter:

        [Cities].[Id] = @City_Code

        Filtering the data based on the Groups in a hierarchical Tree Filter

        In some cases, you would like to filter the loaded rows using also the Groups in a hierarchical Tree Filter.

        In the example below, each state appears under each Forecast period, so the state key is not enough, we need to also apply a filter on the forecast period.
        image.png

        To do that, we can add a Code value also for Group1 which holds the Forecast PeriodKey. We will name the column with the PeriodKey Group1_Code:

        SELECT 
	[PeriodKey] AS Group1_Code
	,[ForecastPeriodName] AS Group1_Text
	,region.RegionKey AS Code
	,region.RegionName AS Text
FROM 
	SQLSpreadsDemo.dbo.meta_ForecastPeriod as foreccast
CROSS JOIN 
	[SQLSpreadsDemo].[dbo].[dim_Region] as region

        In the Designer filter where we filter which rows that should be loaded into the spreadsheet, we will now be able to use the parameter @Forecast_Group1_Code which will hold the PeriodKey for the selected Forecast:
        image.png

        [source_ForecastTransactions].[PeriodKey] = @Forecast_Group1_Code
AND 
[source_ForecastTransactions].[RegionKey] = @Forecast_Code

        Collapse and expand by default

        You can control if the group should be collapsed or expanded by default by adding a column named “AS GroupXExpanded” to the result set with a value of either 'true' or 'false'.

        The column should be named “AS GroupXExpanded” where X is the number of the group to expand/collapse eg: 'true' AS Group1Expanded to expand the level Group1.

        Was this article helpful?
        What's Next

        SQL Spreads