applying-hidden-filter-using-inner-query

default

markdown

#How can I apply a hidden filter using an inner query?

[[_TOC_]]

**Note:** Use the below method only when you have a large number of values.  Normaly you don't have to add filters via inner query. Please use AdHocSettings.HiddenFilters instead.

##Using a Hidden Filter

For this example, we will use the Northwind database as our datasource.
Here is our sample report in the report designer:

![](/FAQ/applying-hidden-filter-using-inner-query/hidden_filters_1.png)

Below is a code sample showing how to add a hidden filter to a report. We will be using this query with our sample datasource.

###C♯

```csharp
public override void PreExecuteReportSet(ReportSet reportSet)
{
    base.PreExecuteReportSet(reportSet);
    Filter filter = new Filter();
    //These three parameters are what are normally shown in the Filters tab of the report designer
    filter.Column = "ShipCity"; //Filter Field
    filter.Operator = OperatorTypes.In; //Operator
    filter.Values = new string[] {"Boise"}; //Value(s)
    reportSet.Filters.AddHidden(filter);
}
```

###VB.NET

```visualbasic
Public Overrides Sub PreExecuteReportSet(ByVal reportSet As Izenda.AdHoc.ReportSet) 
    MyBasease.PreExecuteReportSet(reportSet);
    Dim filter As New Filter();
    'These three parameters are what are normally shown in the Filters tab of the report designer
    filter.Column = "ShipCity" 'Filter Field
    filter.Operator = OperatorTypes.In 'Operator
    filter.Values = New String() {"Boise"} 'Value(s)
    reportSet.Filters.AddHidden(filter)
End Sub
```

**Note:** The **Values** property should always be used, even for one item.

After booting up our application and running our report, here is what we get:

![](/FAQ/applying-hidden-filter-using-inner-query/hidden_filters_0.png)

##Hidden Filter with SQL Override

The SqlOverride property can be used to more precisely handle inner queries for the filter. You simply need to define the Column and the SqlOverride properties to use it. See the query below for a demonstration.

###C♯

```csharp
public override void PreExecuteReportSet(ReportSet reportSet) 
{  
    base.PreExecuteReportSet(reportSet);   
    if(reportSet.Source.Equals("[dbo].[Orders]"))
    {    
        Filter filter = new Izenda.AdHoc.Filter();    
        filter.Column = "ShipCity";    
        filter.SqlOverride = "ShipCity IN (SELECT ShipCity FROM [dbo].[Orders] WHERE ShipCountry = 'USA')"; //There is no need to use the Operator or Values properties with SqlOverride
        reportSet.Filters.AddHidden(filter);    
    }
}
```

###VB.NET

```visualbasic
Public Overrides Sub PreExecuteReportSet(ByVal reportSet As Izenda.AdHoc.ReportSet) 
    MyBase.PreExecuteReportSet(reportSet)
    If reportSet.Source.Equals("[dbo].[Orders]") Then
        Dim filter As New Izenda.AdHoc.Filter()   
        filter.Column = "ShipCity"  
        filter.SqlOverride = "ShipCity IN (SELECT ShipCity FROM [dbo].[Orders] WHERE ShipCountry = 'USA')" 'There is no need to use the Operator or Values properties with SqlOverride
        reportSet.Filters.AddHidden(filter)   
    End If
End Sub
```

![](/FAQ/applying-hidden-filter-using-inner-query/hidden_filters_2.png)

##Multi-tenant hidden filters

For a global method of using hidden filters, see the article on the [[HiddenFilters|/API/CodeSamples/HiddenFilters]] collection in [[AdHocSettings|/API/AdHocSettings]].

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 applying-hidden-filter-using-inner-query

Header

Sidebar