Sorting in Freeform SQL reports

Just as for standard reports, you can sort objects in rows and columns in Freeform SQL reports. As one of the formatting features, sorting is usually done after data is retrieved from the data source and displayed in a grid. This especially applies to Freeform SQL reports, since the foremost goal of such a report is to obtain the correct data from its source.

Sorting is processed by the Analytical Engine. For standard reports, if there is no sort information in the report, the result set is sorted based on the attribute ID (ascending) of the attribute if there is only a single attribute in the report, or the far left attribute on the row or the top attribute in the column if there are multiple attributes in the report.

In Freeform SQL reports, even if you indicate the sorting order in the SQL statement, the Analytical Engine by default displays the result set using the attribute ID in ascending order, as described above.

Here is a simple example. If you use the following SQL statement in the Freeform SQL report:

Select Region_ID, Region_Name
From LU_Region
Order by Region_Name desc

You will get the following result:

1	Northeast
2	Mid-Atlantic
3	Southeast
4	Central

 

As you can see, the "Order by Region_Name desc" clause did not impact the default sorting order by the Analytical Engine.

To get the best of the Freeform SQL feature, it is recommended that you use the SQL statement to get the report data from the data source first. Next, use the sort functionality within the report to sort the data, as needed. In other words, there is no need to include the sort information in the SQL statement, because it is overridden by the Analytical Engine's default behavior, as illustrated in the example above.