Jump to content
  • 0

Limit on Aggrigates - need more help



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.



Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

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. image.png.3f6e696217d99521d8a47a448db943c4.png
  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. image.png.14c3fa890bfd6ce467a36bd825c55adc.png
  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. image.png.2e188c899ac74fd32cb16edc567f6583.png




I hope this helps.


Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...