Jump to content
  • 0

Calculate rank values of entire data source


FishTacos
 Share

Question

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!

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

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.

Link to comment
Share on other sites

  • 0

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

Link to comment
Share on other sites

  • 0
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?

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.

Guest
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.

Loading...
 Share

×
×
  • Create New...