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.