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.
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.
Question
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!
Link to comment
Share on other sites
6 answers to this question
Recommended Posts
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.