Monday, July 27, 2020

SSRS. Use of Lookup type functions.

There is a lot of blog posts in the Internet about how to use Lookup and other functions of this type in SSRS.
It is very straight forward and easy to use.
However, I've managed to make a mistake and I assume have the same problem if you got to that page.

The Error

At firs, here is the error I've got when I tried to use Lookup function in SSRS:
The expression used for the calculated field 'ProductName' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions.

How did I get there?

At first, I've created two data sets "WorkOrders" and "Products"

Then I wanted to include "Product Name" into my "Work Order Report"
Here is How I've done it:

The WRONG WAY

(that is how it shouldn't been done)
Obviously, if I use a function I thought I have to use it in a calculated field

But That is Wrong

You should do it the right way:

The RIGHT WAY

That is not obvious, but you have to use "Query Field" to use the Lookup function:

Then you just have to specify New Field's name and insert a formula.
In my case I used following:

=Lookup(Fields!ProductID.Value, Fields!ProductID.Value, Fields!Name.Value, "Products")


That worked very well for me and I hope you will struggle less by reading this.
Good Luck


5 comments:

  1. I have tried it and it doesn't work. However doing the exact thing as an expression in the report does work.

    ReplyDelete
    Replies
    1. can you suggest the extra thing what you done in you are expression, since i am also facing same issue.

      Delete
    2. could you please share what you done in the expression ?

      Delete
  2. I also tried the same thing but it does not work

    ReplyDelete
    Replies
    1. Sorry guys. Currently working with other tools and can't get back and review the situation. However, if that worked when I posted it that means something is different. Data types? Data set's types? Changes in SSRS/VS ?

      Delete