Limit on Aggrigates - need more help

[Lynda]

I want thank those that have helped me so far, but I have run into another roadblock.  On my reports page I have nine columns. The first aggrigate sums each of the columns. This works as designed. The second aggrigate should apply a calculation to each of the columns (CalorieGoal - SUM, ProteinGoal - SUM, CarbGoal - SUM, etc.) across the nine columns. The last time I asked for help, the solution was to apply the calculation in a new aggrigate and the in the footer move the aggrigate to the second aggrigate and hide the 3rd, 4th aggrigate in the header. I applied this code and it worked, as you can see in the screen shot below.

Then I ran into the BIG PROBLEM. Caspio only supports five aggrigates. 

Does anyone know of a solution or a work around? I really need one.

 

[Aether]

Hi @Lynda,

I also hit the aggregation limit on my workflow, what I can suggest is just to create/duplicate another report to use different aggregation and hide the column/row on the 2nd report to only display the aggregation you needed. Refer here for the similar workflow:

 

[Lynda]

I was able to break free from the limitation (with help from Cooper Backpack (https://forums.caspio.com/profile/26776-coopperbackpack/))

Caspio allows up to 5 Aggregate rows on each results page.  This is fine if the same calculation is applied to all columns for each level of aggregation.

But my solution required a different value to be applied against each column of data. Both as the 2nd aggregate as a base, and then in the 3rd aggregate calculation as a result.

This solution will allow you to aggregate across as many columns as neccessary. My example shows 9 columns and uses 3 aggregates.

It is a three-step process if you are using multiple tables and relies on consistent naming and positioning.

1. Align your table column names and positions. (FoodLog.Calories, FoodLog.Carbs, FoodLog.Protein... and MyProfile.Calories,MyProfile.Carbs, MyProfile.Protein...)
   1. The columns within each of the tables you are trying to aggregate MUST be in the same order as they appear  on your report data page. If you are using more than one table, then all tables MUST have the same column names in the same order as your report data page.
2. Define your first aggregate ( Total). This aggregate uses a Function: Sum) Data comes from FoodLog and all columns needed have been selected
3. 
4. Define your second aggregate ( Daily Nutritional Goal). The Selected Fields are the same (same order) from Aggregate 1. Data comes from MyProfile. This aggregate uses a Formula: (SELECT SUM FROM MyProfile WHERE ProfileID =[@ProfileID])  SQL assumes the default names and positions.
5. 
6. Define your third aggregate ( Remaining Goal for the Day). The Selected Fields are the same (same order) from Aggregate 1 & 3. Data comes from MyProfile & Aggregate 1. This aggregate uses a Formula: (SELECT SUM FROM MyProfile WHERE ProfileID =[@ProfileID]) - ISNULL((SELECT SUM),0)   SQL assumes the default names and positions.
7. 

Results:

 

I hope this helps.

Lynda