GetPossibleValuesAsTree

default

markdown

#GetPossibleValuesAsTree

[[_TOC_]]

##About

The GetPossibleValuesAsTree method is an overridable method in the [[AdHocConfig|/API/AdHocConfig]] class that can change the behavior of the Equals (Tree) filter operator. This option is normally hidden from appearing on the list until it is overridden in your [[CustomAdHocConfig|http://wiki.izenda.com/Integration/Tutorials/Customizing-Izenda-Settings]]. The [[Equals (Tree)|http://wiki.izenda.com/Guides/ReportDesign/5.0-Filters-tab#5.3-Operator-Categories]] operator will allow you to select elements based on a hierarchy of values starting with "All", which is the root option. Values can be expanded out from the root option and any number may be selected.

##Use

The filter should be constructed to address a hierarchy - in this case, ShipCountry -> ShipRegion -> ShipCity. When applied to the lowest level of the hierarchy, you will be able to select indvidual values from a given level of the hierarchy (for example, one city from a region or a country) instead of selecting all values using a single filter, or by using multiple layered filters to isolate multiple combinations of city, region, and country.

In the example photo, notice how we have selected two cities in Oregon, one city in the UK, and all cities in the country of Spain with a single filter.

![Using an Equals(Tree) filter to select multiple values at different levels of a hierarchy.](/FAQ/GetPossibleValuesAsTree/EqualsTreeHowTo.png)

##C♯ Method

In the following example, we will be using the Northwind example database to construct the Equals (Tree) list for the columns ShipCity and ProductName. You may use this snippet to construct an Equals (Tree) list that works for your particular organization.

```csharp
public override System.Collections.Generic.IList<ValueTreeNode> GetPossibleValuesAsTree(string fullColumnName,
                                                                                        int maxRowCount, ReportSet rs) {
      if (string.IsNullOrEmpty(fullColumnName) || fullColumnName == "...")
        return new System.Collections.Generic.List<ValueTreeNode>();

if (fullColumnName == "[dbo].[Orders].[ShipCity]" || fullColumnName == "[dbo].[Invoices].[ShipCity]") {
        System.Collections.Generic.IList<ValueTreeNode> result = new System.Collections.Generic.List<ValueTreeNode>();
        ValueTreeNode all = new ValueTreeNode("All");
        ValueTreeNode none = new ValueTreeNode("(none)");
        result.Add(all);
        result.Add(none);
        all.Nodes = new System.Collections.Generic.List<ValueTreeNode>();

string tableName = "[dbo].[Orders]";
        if (fullColumnName == "[dbo].[Invoices].[ShipCity]")
          tableName = "[dbo].[Invoices]";
        System.Data.DataSet ds =
        AdHocContext.Driver.ExecuteDataSet(
        "SELECT DISTINCT [ShipCountry], [ShipRegion], [ShipCity] from " + tableName +
        " order by [ShipCountry], [ShipRegion], [ShipCity]", false);
        System.Data.DataTable table = ds.Tables[0];

ValueTreeNode shipCountry = null;
        ValueTreeNode shipRegion = null;
        foreach (System.Data.DataRow row in table.Rows) {
          string country = row[0].ToString();
          if (shipCountry == null || shipCountry.Text != country) {
            shipCountry = new ValueTreeNode(country);
            all.Nodes.Add(shipCountry);
            shipCountry.Nodes = new System.Collections.Generic.List<ValueTreeNode>();
            shipRegion = null;
          }

string region = row[1].ToString();
          if (string.IsNullOrEmpty(region)) {
            shipRegion = shipCountry;
          }
          else {
            if (shipRegion == null || shipRegion.Text != region) {
              shipRegion = new ValueTreeNode(region);
              shipCountry.Nodes.Add(shipRegion);
              shipRegion.Nodes = new System.Collections.Generic.List<ValueTreeNode>();
            }
          }
          shipRegion.Nodes.Add(new ValueTreeNode(row[2].ToString()));
        }
        return result;
      }
      else if (fullColumnName == "[dbo].[Products].[ProductName]") {
        System.Collections.Generic.IList<ValueTreeNode> result = new System.Collections.Generic.List<ValueTreeNode>();
        ValueTreeNode all = new ValueTreeNode("All");
        ValueTreeNode none = new ValueTreeNode("(none)");
        result.Add(all);
        result.Add(none);
        all.Nodes = new System.Collections.Generic.List<ValueTreeNode>();

System.Data.DataSet ds =
        AdHocContext.Driver.ExecuteDataSet(
        "SELECT DISTINCT [CategoryName], [ProductName] from [dbo].[Categories] left join [dbo].[Products] on [dbo].[Products].[CategoryID] = [dbo].[Categories].[CategoryID] order by [CategoryName], [ProductName]",
        false);
        System.Data.DataTable table = ds.Tables[0];

ValueTreeNode categoryNode = null;

foreach (System.Data.DataRow row in table.Rows) {
          string category = row[0].ToString();
          if (categoryNode == null || categoryNode.Text != category) {
            categoryNode = new ValueTreeNode(category);
            all.Nodes.Add(categoryNode);
            categoryNode.Nodes = new System.Collections.Generic.List<ValueTreeNode>();
          }
          categoryNode.Nodes.Add(new ValueTreeNode(row[1].ToString()));

}
        return result;
      }
      return base.GetPossibleValuesAsTree(fullColumnName, maxRowCount, rs);
    }
```

Uploading file...

Header

Sidebar

![Izenda Logo](http://izenda.com/wp-content/uploads/2014/12/IzendaNewLogoBlueTR.png)

---

* [[Izenda 7 Series Documentation|https://www.izenda.com/docs/]]
* [[Release Notes]]
* [[Upgrade Best Practices|FAQ/Izenda-Update-Best-Practices]]
* [[FAQ]]
* [[Tutorials]]
* [[The Izenda API|/API/AdHocConfig]]
* [[The AdHocSettings class|/API/AdHocSettings]]
* [Developer Links and Guides](/Guides/Developer-Links-and-Guides)
* [[Known Issues]]

---

* <a href="http://www.izenda.com" rel="nofollow" target="_blank">Izenda Website</a>
* [Product Video](https://www.youtube.com/watch?v=X3-yWFq0w5A)
* <a href="http://www.izenda.com/blog" rel="nofollow" target="_blank">Izenda Blog</a>
* <a href="http://www.izenda.com/company/" rel="nofollow" target="_blank">About Us</a>
* <a href="http://www.izenda.com/contact-us/" rel="nofollow" target="_blank">Contact Us</a>

---
* [Guide To Report Design](/Guides/ReportDesign)
* [Making Custom Forms Video](http://www.youtube.com/watch?v=5b2axJlgdFs)

---

* [[Acknowledgements]]

Edit message:

Cancel

Editing GetPossibleValuesAsTree

Header

Sidebar