Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
What version of SSRS are you using? There are some lookup functions you can use in the report, but they are available only in SSRS 2008 R2 and later. – Stacia Jun 14 '12 at 14:33
I am using 2008 R2 – Kevin Jun 14 '12 at 14:40
1  
Take a look at the Lookup function: msdn.microsoft.com/en-us/library/ee210575(SQL.105).aspx There are related functions MultiLookup and LookupSet also. The challenge here is in dealing with a range. You can use complex expressions rather than field references and might need to restructure data in the lookup table to get this to work, but hopefully this points you in the right direction. – Stacia Jun 14 '12 at 14:50

migrated from stackoverflow.com Jun 14 '12 at 16:54

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.