passing-alternate-filter-values-with-a-table-or-view

default

markdown

#Substituting filter values for other values when using a table or view

[[_TOC_]]

##Question

When I filter a report built on a table or view, I want to pass a different value than what is displayed. How can I accomplish this in Izenda?

##Answer

Please refer to the included sample code. In this code, we override PostProcessEqualsSelectList in order to populate a filter with a set of paired values. Values from the CompanyName column will be placed in the filter dropdown which users can select, but the corresponding value from CompanyID will be placed in the where clause of the query.

**Note:** If you would like to substitute the value of a stored procedure param, click [[here|/FAQ/Questions/using-alternate-filter-values-with-stored-procedures]].

```csharp
public override void PostProcessEqualsSelectList(Column column, List<string> labels, List<string> values)
{
    if (column.Name == "CustomerID")
    {
        var sql = string.Format("SELECT [CustomerID] AS \"Value\", [CompanyName] AS \"Label\" FROM [Customers] WHERE [CustomerID] IN ({0}) ORDER BY [CompanyName]", string.Join(",", values.Where(v => v != "...").Select(v => "'" + v + "'")));
        var ds = AdHocContext.Driver.ExecuteDataSet(sql);
        if (ds != null && ds.Tables.Count > 0)
        {
            var table = ds.Tables[0];
            values.Clear();
            labels.Clear();
            values.Add("...");
            labels.Add("...");
            foreach (DataRow row in table.Rows)
            {
                values.Add(row["Value"].ToString());
                labels.Add(row["Label"].ToString());
            }
        }
    }
}
```

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 passing-alternate-filter-values-with-a-table-or-view

Header

Sidebar