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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


migration rejected from Nov 12 '13 at 22:12

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Max Vernon, Mark Storey-Smith, Michael - sqlbot, Paul White, dezso Nov 12 '13 at 22:12

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the 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
Take a look at the Lookup function: 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