Calculate rank values of entire data source

[FishTacos]

Hello- I am trying to calculate 3 rank values for my data source.  1- overall rank, 2-gender rank, 3-age rank.  I have a tabular report displaying each individuals record with a time for a run.  Each person needs to have an overall rank, gender rank and age rank.  I can accomplish this with a calculated field using RANK functions by each respective data field but my problem is that if you change search criteria it will change the rank.  I want the ranks to remain constant regardless of the search filters...almost as if those rank values live on the database table.  I have tried to move this from a datapage calculation field to a database trigger to insert the values on the database table but can't seem to get any logic how I want it in the trigger expression builder - is it even capable of doing this?

Example of gender rank would be: DENSE_RANK () OVER (PARTITION BY [@field:cb_vr_results_SEX] ORDER BY [@field:cb_vr_results_TIME]).  Assigns gender place by time ASC for each M and F.

Any help or leads would be helpful.

Thank you!

[Nuke354]

Based from my experience, some SQL functions such as DENSE_RANK () are not available within the Caspio Platform. Have you tried using the same OnLoad and OnExit parameters when searching?

Perhaps you can give us a sample test DataPage or some screenshots on how you configured it so we can check.

[TellMeWhy]

Instead of using {@field:field] have you tried the manual SELECT Statement?

I believe if you used field parameters, it will abide by the rules of the DataPage, so, if it's filtered, it will only check the filtered. If you use SELECT Statement, it will take from the Table itself regardless of the filtered set in the DataPage

[FishTacos]

2 hours ago, TellMeWhy said:

Instead of using {@field:field] have you tried the manual SELECT Statement?

I believe if you used field parameters, it will abide by the rules of the DataPage, so, if it's filtered, it will only check the filtered. If you use SELECT Statement, it will take from the Table itself regardless of the filtered set in the DataPage

It doesn't seem to work.  Keep getting formula errors.  This is a good suggestion and I was hopeful.  Below is what I was using:

 DENSE_RANK () OVER (PARTITION BY (SELECT cb_vr_results_SEX FROM view_cb_vr_results WHERE cb_vr_results_DISTANCE_SUBMITTED=[@DISTANCE_SUBMITTED])
 ORDER BY (SELECT cb_vr_results_TIME FROM view_cb_vr_results WHERE cb_vr_results_DISTANCE_SUBMITTED=[@DISTANCE_SUBMITTED]))

[@DISTANCE_SUBMITTED] is a param passed from Search.  

Am I missing something?

[futurist]

Hi @FishTacos,

this might be an alternative that you might wanna consider: