ReportBuilder 11 and later include what is known as Group Search support or Having clause support in DADE. In order to understand how this feature can be used, we must first discuss what exactly the Having clause is.
The Having clause is very similar to the Where clause in a SQL statement in that it is used to select and reject data from a query. The difference is that while the Where clause is used to filter individual rows, the Having clause is used to filter row groups.
An example of using the Having clause would be the following:
– Find the customers who have spent a total of $50,000 or more with our company.
SELECT CustNo, SUM(AmountPaid) FROM orders GROUP BY CustNo HAVING SUM(AmountPaid) >= 50000
As you can see in the above statement, we are filtering on a calculated field that is grouped. Note that it is not necessary to select the calculated field that is used in the Having clause.
– Find the total amount spent with our company for customers who average over $1000 per order.
SELECT CustNo, SUM(AmountPaid) FROM orders GROUP BY CustNo HAVING AVG(AmountPaid) > 1000
Now that we are familiar with the Having clause, we can begin using it with DADE in ReportBuilder. The Query Designer now contains the Group Search tab which allows you to visually control the Having clause of your query. Below we will walk through creating the above queries in DADE.
Start by creating a new report and configuring the datasettings to connect to the DBDEMOS database.
– Find the customers who have spent a total of $50,000 or more with our company.
1. Create a new query using the Query Designer. Select the orders table.
2. From the Fields tab, select the CustNo field from the available fields list.
3. From the Calc tab, select the AmountPaid field from the available fields list. Be sure it is set to calculate the SUM. A new group should automatically be added for the CustNo field.
4. From the Group Search Tab, select the SUM_orders_AmountPaid field from the available fields list. Select the “>=” operator and set the Value to 50000.
5. From the SQL tab you should see a SQL statement similar to the first statement above.
– Find the total amount spent with our company for customers who average over $1000 per order.
1. Create a new query using the Query Designer. Select the orders table.
2. From the Fields tab, select the CustNo field from the available fields list.
3. From the Calc tab, select the AmountPaid field from the available fields list. Be sure it is set to calculate the SUM. A new group should automatically be added for the CustNo field.
4. From the Group Search Tab, select the AmountPaid field from the available fields list. Pull down the Function combo box and select the AVG(orders.AmountPaid) option. Select the “>” operator and set the Value to 1000.
5. From the SQL tab you should see a SQL statement similar to the second statement above.
It is also possible to select the search values using the AutoSearch feature in ReportBuilder. This works the same way ordinary search criteria (Where clause) does.
Group Search has also been added to the TdaSQLBuilder object. See the snip of code below for an example of how it is to be used.
Download: HavingExample.zip
Sample Delphi code:
uses
daSQLBuilder;
---
var
lSQLBuilder: TdaSQLBuilder;
begin
lSQLBuilder := TdaSQLBuilder(ppReport1);
lSQLBuilder.GroupSearchCriteria.Add('orders', 'SUM_orders_AmountPaid', '>=', '50000');
lSQLBuilder.ApplyUpdates;
lSQLBuilder.Free;
end;