Summary Table

[NickO]

I have a Datapage which is sifting through data in a fairly large view (4mil rows and growing). The simplified version is that the view allows me to search for "content" that meets a combination of about 15 different criteria, so no real way to shrink it. The Datapage in question though, is concerned only with summary level data. I.E. how many of each criteria are met by the content.

For example:

I have a single content, Content A. That content is linked to 3 items in criteria1, 2 items in criteria2 and 0 items in criteria3

So my view looks like:

But my datapage looks like:

 

With 4 million rows, 500 Content and 15 different criteria, the datapage itself obviously takes a while to chew through the data and load, filter, sort, etc. Is there any way to create a table or view that will have the summary level data for this datapage so I don't have to calculate it all on the fly every time a user interacts?

[Aether]

Hi @NickO,

For this process, I suggest that you use the Pivot DataPage to only get the summarize/group report and use the Aggregate function for the calculation and the output will depend on your configuration. You can check this documentation for more information:  

https://howto.caspio.com/datapages/reports/pivot-table/

I hope this helps

~WatashiwaJin~

[NickO]

Thanks for the suggestion Watashiwa. That is the first path I tried. Unfortunately, performance is atrocious on a pivot with that many records and criteria. 

[DefinitelyNot31337]

Not sure if I understood your question correctly.

 

If you have a Contents table, you may use that as a DataSource to a Tabular Report DataPage, then create 15 Calculated Fields with this syntax:

SELECT COUNT(Criteria1)
FROM _v_ViewName
WHERE ContentID = target.[@field:ContentID]

 

Repeat as needed for Criteria2 through 15

[NickO]

Thanks DefinitelyNot, you are understanding. You describe what I currently do to get those values. The problem is two-fold. 1) There are 15 of those queries for each of the 400 rows of the contents table and 2) Those queries are happening against about 4mil records currently in the view. This leads to a significant performance drag. It is light years better than the pivot table performance to be sure, but it is a turn off for users.

My real problem is that the data they are looking for really could be any combination of those 15 different criteria so I can't step them through it in any way to reduce the # of queries in any given datapage or the number of records in the view.

[DefinitelyNot31337]

On 8/16/2019 at 6:43 AM, NickO said:

It is light years better than the pivot table performance to be sure, but it is a turn off for users.

Hi @NickO, I agree that doing 15 Calculated Fields is better in performance the more complex Pivot Table. To my knowledge, I believe this is the most optimized solution we can implement. You just have a big amount of data.

 

===

You may want to consider "caching" your results by creating 15 integer fields (corresponding to each of your criteria) on your table, then populate it either by Tasks/Triggered Actions.

 

Sorry for the late revert. Do feel free to tag/mention me with the "@" sign so I get notified when you respond.

[NickO]

@DefinitelyNot31337, thanks for circling back. While it would be great to be able to cache results, they are calculated on the fly based on user provided criteria. They are also queries against a View, so I am not sure that a calculated field in that view would be allowed even if I could leverage that method. 

[ianGPT]

Hello,

Just to update on this you may use a view in calculated view by adding a prefix  _v_ + table name. For reference you may check: https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/#:~:text=data from a-,View,-%2C add prefix _v_ 

[Kronos]

Just adding this here in case someone also stumbled ending up here and needs help in configuring Pivot tables:

Creating a Pivot Table Report Using Caspio

Caspio Live: Summarize Large Data Sets With Pivot Tables

Hope this helps!