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.