Help with Summary Report, totals by column (Pivot Table Style)

[ZOCO_Unlimited]

Hello, I am need of assistance with getting a summary export of my template. I need to total all records by the Location with Amount Sums . I have the totals that total the entire table as well as by location with the rows but I am stuck at what should be the simplest (I think my brain is just fried at this point)..  I keep ending up with individual rows instead of summaries and a menagerie of other things. 

Parent Table: Invoice, Child Table: Work Orders

Here is a link to my template:  https://docs.google.com/document/d/124RJRjcpxo3OCwLEcNxDNDUcoo4E6DS1rO9ABr1ACw8/edit?usp=sharing 

What I would like:   

Location	Amount Hauled	Time	Cost
pond 1	725	37.5	$1,200
pond 2	575	88.5	$1,500
pond 3	448	77	$1,600
Blank	0	22	$800

Thank you in advance!

[Steve · Accepted solution]

<<Start: ORDERBY(
  FILTER(
    "Work Orders",
    AND(
      ([_THISROW].[Invoice ID] = [Invoice ID]),
      (
        [_ROWNUMBER]
        = MIN(
          SELECT(
            Work Orders[_ROWNUMBER],
            AND(
              ([_THISROW].[Invoice ID] = [Invoice ID]),
              (("x" & [_THISROW-1].[Location]) = ("x" & [Location]))
            )
          )
        )
      )
    )
  ),
  ISBLANK([_Location]),
    FALSE,
  [Loction],
    FALSE
)>>

<<[Location]>>

<<SUM(
  SELECT(
    Work Orders[Amount Hauled],
    AND(
      ([_THISROW].[Invoice ID] = [Invoice ID]),
      (("x" & [_THISROW-1].[Location]) = ("x" & [Location]))
    )
  )
)>>

<<SUM(
  SELECT(
    Work Orders[Time],
    AND(
      ([_THISROW].[Invoice ID] = [Invoice ID]),
      (("x" & [_THISROW-1].[Location]) = ("x" & [Location]))
    )
  )
)>>

<<SUM(
  SELECT(
    Work Orders[Cost],
    AND(
      ([_THISROW].[Invoice ID] = [Invoice ID]),
      (("x" & [_THISROW-1].[Location]) = ("x" & [Location]))
    )
  )
)>>

<<End>>

The above is untested and will require adaptation to the specifics of your app.

Newlines and indentation are for clarity only and must be removed for use.

In ([_THISROW].[Invoice ID] = [Invoice ID]), replace the first occurrence of Invoice ID with the name of the key column of the Invoices table, and the second occurrence of Invoice ID with the name of the Ref column of the Work Orders table that refers to the work order's invoice.

[SkrOYC]

I remember when I discovered this from you, the dynamic "Group-by like" thing, it is one of the biggest game changers for me

[Steve]

A post well worth citing: https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/PAGE-BREAKS-IN-PDFS/m-p/460458/highlight/true#M...

[ZOCO_Unlimited]

Keeping this in my wheelhouse for sure! Many Many thanks

[ZOCO_Unlimited]

@Steve This worked perfectly! I cannot thank you enough again. Before I click Accept as Solution and close this, I can't figure out, what is the purpose of the "x" in the expression you wrote for me?

[Steve]

---

@ZOCO_Unlimited wrote:

I can't figure out, what is the purpose of the "x" in the expression you wrote for me?

---

("x" & [column]) produces a value of type Text consisting of x followed by the textual representation of whatever the column column contains. If [column] is a blank value, the result will be x rather than a blank value.

If your app is configured for legacy data processing, a blank value used in a comparison expression may have some magic behavior. Preventing a blank value by adding a leading x prevents the magic behavior.