Izenda Logo

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:

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♯

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

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:

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♯

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

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

Multi-tenant hidden filters

For a global method of using hidden filters, see the article on the HiddenFilters collection in AdHocSettings.