Report Expressions for Grouping and Sorting Data

Hi!

I am trying to create a report which sorts the data by certain columns, I am happy to share my app with anyone who is willing to help but I have been working on this for days and am going a bit :peanuts:

It is an app to be used to collect information from real estate agents about their upcoming, current and future properties. I have set the app up so it is all in one table, the agent makes a new entry for each property and the type selected jumps them to the relevant questions. Would this be better set up in a different way?

With the report I want to group and sort the data first by agent, then property type (e.g. upcoming, current, sold, future).

I have been experimenting with the start and select expressions but just can’t figure it out!

Looking forward to getting some answers!

I’d posted a reply yesterday (since deleted) saying:

As I understand it (I don’t work with reports much), there’s no automatic way to group data in reports: you’d have to do it manually.

But as I though about it, I couldn’t get passed the feeling there is a way to do it. After some tinkering, I found it! It’s not trivial–in fact, I’d call it expert-level stuff.

This is the <<Start>> tag to group by agent:

<<Start: ORDERBY(FILTER("All Data", ([Row Key] = MAXROW("All Data", "_ROWNUMBER", ([_THISROW-1].[Agent] = [Agent])))), [Agent])>>

This is the <<Start>> tag to group by property type:

<<Start: ORDERBY(FILTER("All Data", AND(([_THISROW-1].[Agent] = [Agent]), ([Row Key] = MAXROW("All Data", "_ROWNUMBER", AND(([_THISROW-1].[Property Type] = [Property Type]), ([_THISROW-1].[Agent] = [Agent])))))), [Property Type])>>

In each, replace All Data with the name of your table, Row Key with the name of the table’s key column, and Agent and Property Type with the column names you use for them.

Very roughly, your template will look something like this:

<<Start: ...by agent...>>
<<Start: ...by property type...>>
...property...
<<End>>
<<End>>
5 Likes

Steve, you are a legend! Thank you!
I also feel better that this is expert level stuff :wink:

I have entered the code in and this is the result, on the right track but not 100% there as yet:

As you can see some entries are doubling up and some are not being shown at all. I have tried fiddling with the above expressions, once again to no avail!

Thank you again for your help and I look forward to hearing your thoughts!

1 Like

Here is a screenshot of the information within the app :slight_smile:

1 Like

Please post a screenshot of your template showing your version of the expression I gave you.

Excuse the formatting mess :wink:

This is super awesome trick and finding Steve!

3 Likes

Your second <<Start>> tag doesn’t appear to match the format I gave. Please review what I offered and make sure you use something similar.