Summary Report vs Detail Reports

Often, we hear about clients trying to figure out why Foxfire! creates an SQL statement with GROUP statements and why it doesn’t. The distinction can be subtle when looking at the interface so let’s explain why.

Foxfire! does two things with a request:

It creates the SQL statement that is used to gather the data.
It creates a formatted report file to display the information, using the layout you have specified. If you are using a Custom Layout, then Foxfire! won’t create the formatted layout, it will use the one you created.

Let’s start by looking at two types of Foxfire! requests. When you first create a request, you choose between five different types:

Detail
Summary
Cross-Tab
Label
Form Filler

Select a Request Type

A Detail report is similar to a List – it is simply going to return all of the information requested without performing any aggregation (like a Total, Average or Minimum and Maximum values).

Default detail report layout showing Cars Make, Color, Sale Date and Price.

A Summary report summarizes information in the query. In SQL, when summarizing information, every field that is NOT being summarized must be grouped so the aggregation is performed properly.

Default report output for a summary report showing Make, Color, Date and Sum of Sales Price.

Note in the above two reports, showing the same data, the first report shows each car in stock and its sale price and date. In the second report, we only see the color and the total (or Sum of) sale price by brand and date. Notice the result from December 16th 1999, in the Summary report , there is only one row and shows a total of $12,810. In the Detail report, there are multiple rows, one for each car sold on December 16th, 1999.

Internally, the SQL generated by Foxfire! is also different.

In the Detail request, the SQL is

SELECT ALL;
CARS.MAKE,;
CARS.COLOR,;
CARS.SALEDT,;
CARS.SALEPR;
FROM “CARS” CARS;
INTO CURSOR CARSDET;
WHERE (UPPER(Cars.Make) IN (“PONTIAC”,”FORD”);
AND Cars.Saledt<>{};
AND UPPER(Cars.Color) IN (“BLACK”,”BLUE”))

In the Summary request, the SQL is:

SELECT ALL;
CARS.MAKE,;
CARS.COLOR,;
CARS.SALEDT,;
Sum(CARS.SALEPR) AS SM_SALEPR;
FROM “CARS” CARS;
INTO CURSOR CARSDUM;
WHERE (UPPER(Cars.Make) IN (“PONTIAC”,”FORD”);
AND Cars.Saledt<>{};
AND UPPER(Cars.Color) IN (“BLACK”,”BLUE”));
GROUP BY 1, 2, 3;
ORDER BY 1, 2, 3

See the difference? By default, the Summary report adds a GROUP BY statement for the first three columns: MAKE, COLOR and SALEDT.

The most common mistake made with a summary report is to forget about the aggregation function. If instead of choosing to SUM the Sale price, only the data item is selected (see below).

The Select Data Items tab in a summary report includes aggregation functions at the bottom.

Even if there are no aggregation functions in a summary report, the SQL that is generated is still the same.

SELECT ALL;
CARS.MAKE,;
CARS.COLOR,;
CARS.SALEDT,;
CARS.SALEPR;
FROM “CARS” CARS;
INTO CURSOR CARSDUM;
WHERE (UPPER(Cars.Make) IN (“PONTIAC”,”FORD”);
AND Cars.Saledt<>{};
AND UPPER(Cars.Color) IN (“BLACK”,”BLUE”));
GROUP BY 1, 2, 3, 4;
ORDER BY 1, 2, 3, 4

In this scenario, the two reports will look identical but the GROUP BY and ORDER BY statement is added automatically to the second report.

It’s important to note that the Sort/Group tab don’t affect the GROUP BY statement that much. Instead, the Grouped option in the Sort/Group tab creates a different type of layout.

Choosing Group By in the Sort/Group tab affects the layout generated by Foxfire!.

The resulting Summary report has the same results but hides the individual columns for Make and Color, creating groups instead.

This report is grouping the totals by Make and Color

The Detail report with similar grouping shows the details but is not totalling the results. It is simply showing the Price received for each item.

A grouped report automatically includes totals but is still not a summary report.

Ideally, the Summary Report wouldn’t include the Sale Date, instead giving a better summarized view of the data.

A Summary report with Make, Color and Total Price Received.

In this case, the SQL is much clearer:

SELECT ALL;
CARS.MAKE,;
CARS.COLOR,;
Sum(CARS.SALEPR) AS SM_SALEPR;
FROM “CARS” CARS;
INTO CURSOR CARSDUM;
WHERE (UPPER(Cars.Make) IN (“PONTIAC”,”FORD”);
AND Cars.Saledt<>{};
AND UPPER(Cars.Color) IN (“BLACK”,”BLUE”));
GROUP BY 1, 2;
ORDER BY 1, 2

When building a report, it helps to think about the information you want to see in the end. If it involves summarizing data, choose a Summary Report. If you simply need a list, then choose a Detail report.

In Foxfire! 2016 (coming this fall), Foxfire! will warn you if you are creating a Summary report that isn’t using a summary function.