I have a fairly smple use case that I can never seem to figure out in SSRS. It has caused me great pain so I figured I would see if anyone knows the answer. In SSRS i have a query that returns a set of rows (obviously) one of the columns of the table is a percentage that I use an aggregrate function to do an AVG() on. So in the footer of a group i have an average. This is all easy and it works perfectly.
However here is where i constantly fall down. I have another table. It will be configured like
LookupValue int MinScore float MaxScore float
The table of values is configurable in my application but suffice it to say i want to do a lookup in this other table with the group aggregrate Avg(Fields!somefield.Value, "SomeGroup"). I basically want to do
for all rows in the lookup table if (aggregrate >= row.MinValue && aggregrate < row.MaxValue) return LookupValue end if end for
I have used lookup() many times but I dont think you can use it for a problem like this. I cant put the lookup into SQL, as I cant do the groupby caluse to know how to aggregrate. I have also tried to use report code but I cant for the life of me find an example of this anywhere.
Any help would be greatly apreciated.