Recently had a question from a developer on how to create pivot queries in the Platform.
The attached module shows how to do this kind of query using an Advanced Query in combination with Expand inline parameters (check the Preparation of screen "Pivot" under the MainFlow web flow)

Any other tips on making this kind of queries & reports on the UI?

I have (mis)used the "for xml" a lot ;)

There are also options available to create PivotTables on the browser: I haven't had the change to use them [yet] but I guess they depend on having all needed data available client-side (a "no go" for lots of use cases).