🧲 The QUERY Column: One Column to Rule Them All!

How about having a single table, but grouped columns?

How many columns do you usually have per report?
I’ve never actually resorted to using Glide for full fledged reporting. Only basic roll-ups and reports within detail screens.

Although, if it’s intuitive from a backend perspective, I would definitely go for it since the UX is awesome for the end user + allows for live editing of the data.

It depends, I use both. For example, here is what the column setup looks like for one of those reporting tables:

I have logical groupings of columns for each of:

  • Data Export
  • Accident Free Days
  • Injury Free Days
  • This Year
  • Last Year
  • This YTD
  • Last YTD
  • Table
  • Chart

If it makes sense to generate two or more reports from a single table, then sometimes I will do that. But as a general rule I keep it to one report category per table.

Again, it depends on the complexity of the report. It could be anywhere between a handful and 100 or more.

I actually have one whole App that’s purely dedicated to reporting. It shares the same data source with 4 or 5 other transactional Apps. My CEO loves it :slight_smile:

3 Likes

Interesting!
That many columns intimidate me! Especially if they’re functionally the same across all reports simply for filtering purposes.

In such instances I would spend days to figure out how to re-use the filter columns for roll-ups across all reports by loading them as sort-of user specific columns.

I would then import the data for those reports according to the report the user selected.

I’m definitely complicating it, but if it’s figured out, it would result in a very simple and dynamic reporting structure-
And if you upgrade a single report, you upgrade all of them.

Damn!
Can we have a peek? :eyes:

Wait, what if you have the date filtering criteria as rows instead of columns?

  • ColA = startDate,
  • ColB = endDate,
    And the value columns could be populated based on a singleValue column to fill in all rows based on the report selection. And then you use the roll-ups based on the dates etc.

Essentially, each report would be a group of columns consisting of;

  • Single value column,
  • Roll-Up per metric

Visibility of Metrics per report which will be triggered via user selection could be achieved via

  • Separate Page per report. OR
  • Dynamic Visibility Rules for the Report components.
    Could potentially even re-use report components just fill the Report Columns Dynamically based on selection (IF, THEN, ELSE)

In that particular example, there is no Date filtering.
There are 12 rows in the table, one for each month of the year.
And the output is a static chart and table that shows month on month and YTD totals for the current and previous year.

For reports/charts that allow date filtering, I have separate driver table with one row per report. Date filters are set in that table and then applied in the Data tables via Single Value columns.

1 Like

I see, would be interesting to find a simple Architecture to use consistently across the board for many use-cases in reporting.

In the query column documentation it mentions the following in an FAQ

For “and” conditions, the order doesn’t matter since all of the conditions will need to be true. For “or” conditions as well, the order doesn’t matter since results satisfying any of the conditions will be returned.

In an “OR” condition, would it perform better if your first condition is the one that is going to be true most frequently? In that case, would it be checking less conditions because it keeps finding the first condition true and isn’t needing to go to the second or third condition for each row?

That’s a very interesting question. I would think it’s a yes since it doesn’t make sense to continue checking in an OR after a condition is true.

2 Likes

Yes, I’d agree with that.
I don’t know what impact that would have on performance, though. I guess it depends on how many conditions there are and how much data is being processed.

2 Likes

Similarly an AND condition would flag false as soon as the first condition is false right?

You would expect so, yes.

1 Like