Solved: Help with Summary Report, totals by column (Pivot ... - Page 2

ZOCO_Unlimited · 02-22-2023 04:27 AM

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

<<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.

View solution in original post

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