DevExpress Gems – The VCL Filter Control

Recently I was tasked with creating a visual query builder for the reporting. The existing query builder was pretty simplistic, and did not allow for grouping conditions. After talking to a few colleagues and looking into some recommended solutions, a consultant and coworker (and close friend) of mine suggested something that is now obvious, but at the time caught me off guard: why not try utilizing the same filter control shown when you customize the filter of the DevExpress VCL Quantum Grid?

Using the filter control from the Quantum Grid component would immediately carry several benefits:

  • We wouldn’t have to purchase an additional control
  • Our query builder for reports would be the same control customers already use to customize our grids
  • Our query builder for reports would visually match the rest of our application

I was optimistic about being able to reuse the filter control, but honestly thought I’d be digging into the source for the existing Quantum Grid filter customization dialog, hoping for some source I could use. Once I sat back down to my development environment, I was delighted (to say the least) to find both TcxFilterControl and TcxDBFilterControl in my tool palette. This was not the first time (nor, I suspect, the last) that I was completely surprised to find some controls in my DevExpress arsenal of which I was previously unaware.

Did these controls allow us to provide a flexible query builder capable of creating complex SQL? You betcha.

Out of the box, TcxFilterControl is meant to be wired up to something like a TcxGrid table view, while the TcxDBFilterControl is meant to be wired up to a TDataSet descendant. While I normally shy away from database-aware controls in the VCL, the TcxDBFilterControl seemed to be more suited for our needs.

To get started using a TcxDBFilterControl, you can simply drop the control on a form and hook it up to a TDataSet descendant. Immediately, the control is capable of creating complex filters, with the field selections in the filter control populated with the field definitions from the TDataSet descendant.

If you are working with a standards compliant SQL engine, using the TcxDBFilterControl to query data requires only a handful of code. Given a simple VCL project with an ADO connection to the Northwind database, a TcxDBFilterControl hooked up to the EmployeeDefsQuery component, and a grid control hooked up to the EmployeeResultsQuery component, the following code is all you need:

procedure TDemoForm.FormCreate(Sender: TObject);
begin
  EmployeeDefsQuery.SQL.Text := 'Select * From Employees Where 0 = 1';
  EmployeeDefsQuery.Open;
end;

procedure TDemoForm.FetchButtonClick(Sender: TObject);
var
  SqlStatement, WhereClause: string;
begin
  EmployeeResultsQuery.Close;
  SqlStatement := 'Select * From Employees';
  WhereClause := EmployeeFilterControl.FilterText;
  if WhereClause <> '' then
    SqlStatement := SqlStatement + ' Where ' + EmployeeFilterControl.FilterText;
  EmployeeResultsQuery.SQL.Text := SqlStatement;
  EmployeeResultsQuery.Open;
  if EmployeesTableView.ItemCount = 0 then
    EmployeesTableView.DataController.CreateAllItems;
end;

Our database engine is not fully SQL compliant, plus there were some niceties that our previous report query builder supported that had to be preserved (such as not requiring the end-user to use the % mask character with LIKE conditions). While crafting custom SQL from the filter control is not trivial, it is entirely possible and we were able to meet all of our requirements with this very useful control.

To create your own SQL from the supplied filter control, you’ll need to parse the TcxFilterControl.Criteria.Root property. The items within the criteria list can either be criteria items or additional criteria lists (TcxFilterCriteriaItem and TcxFilterCriteriaItemList). The item lists have a BoolOperatorKind property, while the criteria items have an Operator property, along with additional properties, that allow you to fully inspect the filter specified in the filter control and create your own SQL statement. While this is outside the scope of this post, feel free to contact me for more details on how to accomplish this.

I love being surprised by existing controls, new to me, that DevExpress has provided in their suites, allowing me to get my job done better and faster without additional investments in time and money. To be honest, when I thought visual query builder, I just did not think DevExpress. To me this is a showcase control (as seen from other vendors), but DevExpress’s implementation provided everything we needed to get our new report query builder going in no time.

Advertisements

3 thoughts on “DevExpress Gems – The VCL Filter Control

  1. Claudio Piffer

    Hi Nathanial,

    great info. It would be very interesting connect an TcxDBFilterControl component to an application DataAbstract exploiting DynamicWhere. Do you think it is something feasible?

    Reply
    1. nwoolls Post author

      Claudio,

      Something like that should absolutely be possible. You would simply need to interpret the filter in the TcxFilterControl as indicated in the second-to-last paragraph, parsing the Criteria.Root property. You can drop me an email and I can send you some sample source.

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s