[ideaoverload]

I was working on such system for performance measurements. Basic assumption was that user would never see any mention of tables, joins, column names nor any other SQL concept.

1.Table selection - there is predefined set of joined tables we call views. Views have descriptive names e.g. 'Temperature measurements', no underlying tables are shown.

2.Column selection - user selects columns from views using descriptive names , not actual column names . UI clearly indicates columns that land in 'group by' section by calling them dimensions. There is predefined aggregation strategy for all columns that are not dimensions - e.g. average for temperatures or sum for number of measurements. In some cases more that one aggregate is available e.g. average or maximum temperature. User just selects maximum or average temperature not aggregation operator.

3. Filters: user can select simple filters on columns: =<> and string matching for text. All filters are ANDed

4.Sorting: user can select columns to sort on.

5.Display: results are displayed as tables or charts. More that one section may be placed on single report.

The real system has tons of additional features but basic design as design as described above has worked great for years.