How to Rank by total result/score/value
Environment
Product | Progress® Telerik® Reporting |
Description
Sometimes it is necessary to determine the rank of a category value (e.g. competitor ID) based on some aggregate value (e.g. total score from several competitions).
Solution
It is necessary to create a Custom Aggregate that achieves such requirement. Here is a sample code :
using System.Collections.Generic;
using System.Linq;
using Telerik.Reporting.Expressions;
namespace CustomAggregates
{
[AggregateFunction(Name = "RankByCategory",
Description = "Defines an aggregate function to get the rank of a field based on total value of another field.")]
public class RankByCategoryAggregate : IAggregateFunction
{
private Dictionary<string, double> sums;
private Ranks result;
/// <summary>
/// Initializes the current aggregate function to its initial
/// state ready to accumulate and merge values.
/// </summary>
/// <remarks>
/// This method is called every time the accumulation of values
/// must start over for a new subset of records from the data source.
/// </remarks>
public void Init()
{
this.sums = new Dictionary<string, double>();
}
/// <summary>
/// Accumulates new argument values to the current aggregate function.
/// </summary>
/// <remarks>
/// This aggregate function accepts one argument:
/// number - a numeric value to accumulate to the aggregate function;
/// </remarks>
public void Accumulate(object[] values)
{
this.AccumulateCore((string)values[0], (double)values[1]);
}
void AccumulateCore(string key, double value)
{
double currentValue;
if (this.sums.TryGetValue(key, out currentValue))
{
this.sums[key] = currentValue + value;
}
else
{
this.sums[key] = value;
}
this.result = null;
}
/// <summary>
/// Merges the specified aggregate function to the current one.
/// </summary>
/// <param name="Aggregate">
/// Specifies an aggregate function to be merged to the current one.
/// </param>
/// <remarks>
/// This method allows the reporting engine to merge two accumulated
/// subsets of the same aggregate function into a single result.
/// </remarks>
public void Merge(IAggregateFunction aggregate)
{
// Accumulate the values of the specified aggregate function.
foreach (KeyValuePair<string, double> pair in ((RankByCategoryAggregate)aggregate).sums)
{
this.AccumulateCore(pair.Key, pair.Value);
}
}
/// <summary>
/// Returns the currently accumulated value of the aggregate function.
/// </summary>
/// <returns>
/// The currently accumulated numeric value of the aggregate function.
/// </returns>
public object GetValue()
{
if (this.result == null)
{
this.result = Ranks.FromValues(this.sums);
}
return this.result;
}
}
class Ranks
{
private Dictionary<string, int> ranks;
public Ranks(Dictionary<string, int> newRanks)
{
this.ranks = newRanks;
}
public static Ranks FromValues(Dictionary<string, double> values)
{
List<KeyValuePair<string, double>> myList = values.ToList();
myList.Sort(
delegate (KeyValuePair<string, double> firstPair,
KeyValuePair<string, double> nextPair)
{
// Sort descending
return nextPair.Value.CompareTo(firstPair.Value);
});
int currentRank = 1;
double currentValue = double.MaxValue;
Dictionary<string, int> newRanks = new Dictionary<string, int>();
for (int i = 0; i < myList.Count; i++)
{
// Same values will result in the same rank. Next ranks will be skipped (i.e. Values [2.2, 2.2, 0] -> Ranks [1, 1, 3])
if (currentValue != myList[i].Value)
{
currentRank = i + 1;
currentValue = myList[i].Value;
}
newRanks.Add(myList[i].Key, currentRank);
}
return new Ranks(newRanks);
}
public int GetRank(string key)
{
return this.ranks[key];
}
}
}
Note that with the above implementation the equal values will be ranked equally and the next ranks will be skipped, i.e.
- Values [2.2, 2.2, 0] -> Ranks [1, 1, 3]
- Values [2, 2.2, 0] -> Ranks [2, 1, 3]
The aggregate function can be used in an Expression like :
= Exec('crosstab1', RankByCategory(Fields.AssociateName, CDbl(Fields.CalculatedScore))).GetRank(Fields.AssociateName)
The above expression will rank Fields.AssociateName based on the sum of Fields.CalculatedScore for the row groups of crosstab1 grouped by Fields.AssociateName.