Create a SQL data filter to send filters based on any data stored in ADO.NET-accessible data sources such as relational databases. Whether you let users pick a record set to use in interactive mode, or specify the data row to use directly, the filter value being sent is always the current, live value from the specified data source.
To connect to your data source, you select the data source provider to use and specify a connection string. If unsure, your database administrator can help you with this. The following ADO.NET data source providers can be used out of the box:
Data source providers can be added and removed on the Configuration
page in the FilterZen Studio at
http://farm_url/_layouts/roxority_FilterZen/default.aspx?cfg=cfg.
For the SQL data filter to obtain filter values from your data source, you need to specify, in SQL (or any other query language dialect supported by your data source provider), the query sent to the data source to fetch a result set of filter values. In order for this filter to work as expected, it is mandatory that the result set of filter values is guaranteed by your query to always be returned in a consistent sort order, so in SQL you should add an ORDER BY clause. Per the SQL standard, unless your query specifies the sort order explicitly, the default sort order of data rows is undefined and thus may change randomly at any time.
Your query will need to return a stream of record sets (which your query may also pre-filter and sort as preferred) with at least one data column. The name of the column containing the filter value may be identical to the database column or changed to another name by your query — however, you need to specify the result set column name to be used as the value column, i.e. the name of the column providing the filter value. In interactive mode, if you want to display to the user another column value than the one sent as the filter value, specify the name of the display column.