This documentation is for the legacy Izenda 6 product. Documentation for the new Izenda 7 product can be found at https://www.izenda.com/docs/
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.
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.
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); }
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:
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.
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); } }
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
For a global method of using hidden filters, see the article on the HiddenFilters collection in AdHocSettings.
Last edited by Joseph Adams, 2014-04-30 10:09:36