PDF Report | Dynamic Grouping Times Out

[ZOCO_Unlimited]

I can get this report to generate sporadically, the majority of the time it times out after 124ish seconds when monitoring it. I have removed the top section (pasted below) and it runs fine. I'm open to add columns in the invoice table that calculate this but I get stuck writing it 

Equipment Summary <<Start: ORDERBY( FILTER( "Ticket Details",    AND( ([_THISROW].[Invoice #] = [Foreman Ticket ID].[Invoice]), ( [_ROWNUMBER] = MIN(  SELECT(  Ticket Details[_ROWNUMBER], AND( ([_THISROW].[Invoice #] = [Foreman Ticket ID].[Invoice]), (("x" & [_THISROW-1].[Item].[Item Name]) = ("x" & [Item].[Item Name]))  )  )  )  )  )  ),  ISBLANK([Item].[Item Name]), FALSE,  [Item].[Item Name], FALSE)>> <<[Item].[Item Name]>> : <<SUM( SELECT( Ticket Details[Total], AND( ([_THISROW].[Invoice #] = [Foreman Ticket ID].[Invoice]), (("x" & [_THISROW-1].[Item].[Item Name]) = ("x" & [Item].[Item Name])) ) ))>>  <<End>>

By Unit Measures <<Start: ORDERBY( FILTER( "Sub Works",    AND( ([_THISROW].[Invoice #] = [Foreman Ticket ID].[Invoice]), ( [_ROWNUMBER] = MIN(  SELECT(  Sub Works[_ROWNUMBER], AND( ([_THISROW].[Invoice #] = [Foreman Ticket ID].[Invoice]), (("x" & [_THISROW-1].[Material_UOM]) = ("x" & [Material_UOM]))  )  )  )  )  )  ),  ISBLANK([Material_UOM]), FALSE,  [Material_UOM], FALSE)>> <<[Material_UOM]>> : <<SUM( SELECT( Sub Works[volume], AND( ([_THISROW].[Invoice #] = [Foreman Ticket ID].[Invoice]), (("x" & [_THISROW-1].[Material_UOM]) = ("x" & [Material_UOM])) ) ))>>  <<End>>

Other Billable Expenses <<Start: ORDERBY( FILTER( "Receipts",    AND( [Billable]=”Yes”,([_THISROW].[Invoice #] = [Foreman Ticket ID].[Invoice]), ( [_ROWNUMBER] = MIN(  SELECT(  Receipts[_ROWNUMBER], AND( ([_THISROW].[Invoice #] = [Foreman Ticket ID].[Invoice]), (("x" & [_THISROW-1].[Cost Code]) = ("x" & [Cost Code]))  )  )  )  )  )  ),  ISBLANK([Cost Code]), FALSE,  [Cost Code], FALSE)>> <<[Cost Code]>> : <<SUM( SELECT( Receipts[Amount], AND( ([_THISROW].[Invoice #] = [Foreman Ticket ID].[Invoice]), (("x" & [_THISROW-1].[Cost Code]) = ("x" & [Cost Code])) ) ))>>  <<End>>

Link to doc: https://docs.google.com/document/d/1lOjcpltj9Rs7jK2Yp3RstpLVTyTzkALuMSJIy3uOWUI/edit?usp=sharing 

@Steve  if you're available, Prior Post where the setup formula was created; https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Help-with-Summary-Report-totals-by-column-Pivot... 

[WillowMobileSys]

---

@ZOCO_Unlimited wrote:

Link to doc: https://docs.google.com/document/d/1lOjcpltj9Rs7jK2Yp3RstpLVTyTzkALuMSJIy3uOWUI/edit?usp=sharing 

---

I don't get the correlation between the ORDERBY expressions you have shown and the the doc linked in the quote above.

We don't know your data structure or app process flow. so I'll have make some assumptions.

In your ORDERBY expressions, it appears you are using some Detail table (depending on expression) to get a list of "things" and then show SUM for that thing.

This is an inefficient way to do it BECAUSE as the Detail tables grow in size, there will be more and more rows to be parsed, taking longer, to basically get the SAME LIST of "things".  

The SUM is a problem for two reasons:

1)  You are recalculating the SUM's based on ALL rows in the tables.  This forces you to load ALL rows, at least for the template but anywhere else you might be calculating SUM's.  This means you would NOT be able to help improve app performance by filtering out rows that might no longer be needed for daily use.

2)  The time re-calculate the SUM will increase as the table size increases.  Every row needs to be inspected to determine if its included in the SUM or not

HOW TO FIX....

For the list of "things", you want to use tables that contain those list of things.  It appears you have an "Item" table. Use it to get your list of items in the template.  For Material UOM and Cost Code, create tables for them and reference those tables throughout the app.

For the SUM's, I would recommend adding a SUM column to the "things" tables described above.  It can be a Virtual Column but that may significantly slow Sync performance.  So I suggest adding them as "normal" columns and then adding in actions that recalcs the SUMs whenever any Detail rows change that will affect the SUM.

Eventually, I suggest converting the SUMs to rolling values - meaning you adjust the SUM, add or subtract, based on the edits made to your Detail rows rather than a SUM that recalcs based on ALL rows.  This will allow you to eventually apply Security Filters to filter out rows that are no longer needed for day to day use AND will drastically improve the Sync performance!!

[ZOCO_Unlimited]

Thank you @WillowMobileSys I will try what you are suggesting as you just confirmed my thought process, I have questions though and have created/attached examples of what I'm after to help.. I understand what you are saying but the execution is where I'm struggling.   

Here is a link to the App Structure:
 https://docs.google.com/spreadsheets/d/1ZSKq6zAivBDeAKm6pzqBR_RmVC8s4Aj4l5cDnlx851s/edit?usp=sharing 

Sample PDF Export: https://drive.google.com/file/d/1X4u2Y6E4BwSJOH9rmAVTZbXx-tsyPdUI/view?usp=sharing  (when it worked..)

Basically I am trying to get totals to the related tickets within the invoice to show on a report. Invoices consist of foreman tickets which consist of itemized Ticket Details, some are billable some are not.  The goal is similar to a pivot table except with multiple layers of related tables.  Sync Time is currently horrendous and often times out due to virtual columns that I realize I need to convert to standard columns.  

I would like to correct this to your last statement converting the SUMs to rolling values, I get stumped on how to do this? 

Currently each column in my referenced section populates a list with totals to each item in the category and the hours for each item. Appsheet only allows lists in virtual columns, how do you do this with a standard column without creating a column for each Item?

[Suvrutt_Gurjar]

In addition to @WillowMobileSys 's  excellent guidance, if I may add, you may want to consider the following.

Possible Issue 1: In the expression below, there are two multirow expressions-  FILTER() and within FILTER() there is a SELECT() with MIN(). These two multirow functions are running on the same table of "Ticket Details". So if there are 10 rows in the "Ticket Details" table, the expression will iterate 100 times because of a SELECT() in another SELECT() ( FILTER() is a specific implementation of SELECT()) . If there are even 100 rows, the expression will iterate 10000 times, over the table rows. This can cause timeout.

<<Start: ORDERBY(

                                     FILTER( "Ticket Details",   

                                                  AND(

                                                                  ([_THISROW].[Invoice #] = [Foreman Ticket ID].[Invoice]),

                                                                    ( [_ROWNUMBER] =

                                                                                                        MIN(  SELECT(  Ticket Details[_ROWNUMBER],

                                                                                                                  AND( ([_THISROW].[Invoice #] = [Foreman Ticket ID].[Invoice]), 

                                                                                                                             (("x" & [_THISROW-1].[Item].[Item Name]) = ("x" & [Item].[Item Name])) 

                                                                                                                              )       

                                                                                                                                         ) 

                                                                                                                                 ) 

                                                                              ) 

                                                           ) 

                                                    ),  ISBLANK([Item].[Item Name]), FALSE,  [Item].[Item Name], FALSE

                                           )>>

Suggestion 1: To overcome this SELECT() in SELECT(), you may want to create a slice called say "MinRow_Ticket_Details" with a slice filter expression something like 

[Ticket Details Key Column]= MINROW("Ticket Details", "_ROWNUMBER" , AND( ([_THISROW].[Invoice #] = [Foreman Ticket ID].[Invoice]), 

                                                                                                                             (("x" & [_THISROW-1].[Item].[Item Name]) = ("x" & [Item].[Item Name])) 

                                                                                                                              )       )

Then in the main expression you could have 

<<Start: ORDERBY(

                                     FILTER( "Ticket Details",   

                                                  AND(

                                                                  ([_THISROW].[Invoice #] = [Foreman Ticket ID].[Invoice]),

                                                                    IN( [_ROWNUMBER] , MinRow_Ticket_Details[_ROWNUMBER])

                                                                              ) 

                                                           ) 

                                                    ),  ISBLANK([Item].[Item Name]), FALSE,  [Item].[Item Name], FALSE

                                           )>>

I believe , the above will shift some burden of heavy calculation in run time in the bot to the slice. So overall app sync time may not reduce but the calculation burden in bot may.

Possible Issue 2 : As for virtual columns with large multirow calculations, you may want to use an on/off toggle approach to enable/disable  heavy calculation. I believe this approach of toggling calculations in VC is recommended by AppSheet guru @MultiTech 

Suggestion 2: So if you have a VC with a extended multirow expression you could have an expression like 

IF( ANY(Current_User[Enable_SUM]) ,

                                                                       SUM( SELECT( Ticket Details[Total], AND( ([_THISROW].[Invoice #] = [Foreman Ticket ID].[Invoice]),

                                                                        (("x" & [_THISROW-1].[Item].[Item Name]) = ("x" & [Item].[Item Name])) ) )) ,

                                                                          LIST("")

           )

Where [Enable_SUM] is a Y/N type column in the Users table. If the user selects No in [Enable_SUM] column, the VC does not calculate the sync expensive calculation but simply returns an empty list that does not require calculation. So user could selectively enable the calculation only when she/he wants to see the sums etc or run the bot.

I tested this approach in a 37 k row table and the VC calculation time drastically reduces.

The image below shows the VC [FilterTest] with calculations disabled takes 0.38 seconds to compute. The other column [SelectTest] has calculations enabled.

The image below shows the VC [FilterTest] with calculations enabled takes 2.53  seconds ( much on higher side)  to compute. The other column [SelectTest] has calculations enabled.