Compatibility:
SharePoint 2013 Foundation (SPF) and Server (SPS) SharePoint 2010 Foundation (SPF) and Server (SPS) Windows SharePoint Services (WSS) 3.0 / Microsoft Office SharePoint Server (MOSS) 2007
SPF & SPS 2013 • 2010 • 2007
User Voices
(Loading...)
« Page Request (URL Query String) Filters | Business Data Connectivity (BCS/BDC) Filters »
Sunday
Apr262009

SQL Data Filters

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.

Data Source Connection

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:

  • SQL Server — the best option for connecting to any Microsoft SQL Server 2000 or higher database (System.Data.SqlClient)
  • Oracle — the best option for connecting to any Oracle 8.1 or higher database (System.Data.OracleClient)
  • ODBC — the best option for connecting to any database via an Open Database Connectivity driver (System.Data.Odbc)
  • OLE DB — the best option for connecting to a Jet database (Microsoft Access) or any other OLEDB-compatible database (System.Data.OleDb)

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.

SQL Query, Value Column and Display Column

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.