Show Corteza: Raw SQL Views In Reports

This is definitely “advanced usage only” and neither reports nor modules were intended to be used in this way, but with the new physical table functionality and some minor hackery, you can now use SQL views inside of the reports builder.

Steps

  1. Create a module with the correct fields and module name. These will be the fields for the view. Remove all of the ancellary default and audit fields like recordId and createdAt.
  2. In the DB, drop the table
  3. Create a view with the same field types and field names from step 1. Make sure the view name matches the module handle
  4. Add a single load in the report builder and you’re done :slight_smile:

Proofs

Here are the child records

Here’s the view definition

CREATE VIEW "ChildAggView" AS
SELECT
    SUM("Amount") "TotalAmount", p."Name" "Parent"
FROM "Child"
JOIN "Parent" p on p.id = "Child"."Parent"
GROUP BY p."Name";

And here’s the report with the single load!

5 Likes

Nice this is very interesting and we are really happy with the way you are experimenting with Corteza! Our developers and community will definitely find this useful so thank you.

2 Likes