Lookup within the same Dataset in SSRS 2008

1
votes

I'm trying to do a lookup in SSRS 2008 that is within the same dataset.

Here's the scenario:

ID        Postcode  Name
123456    A12 345   Customer1
234567    B12 456   Customer2
345678    C12 789   Customer3
D12 345   D12 345   City1
A12 345   A12 345   City2

Here's what I'm trying to achieve (add an extra location column):

ID        Postcode  Name       Location
123456    A12 345   Customer1  City2
234567    B12 456   Customer2  City3
345678    D12 345   Customer3  City1
D12 345   D12 345   City1      City1
A12 345   A12 345   City2      City2

I've tried the following expression:

=Lookup(Fields!Postcode.Value,Fields!ID.Value,Fields!Name.Value,"Location")

And here's the error message:
The value expression for the text box 'TextBox45' has a scope parameter that is not valid for a lookup function. The scope parameter must be set to a string constant that is the name of a dataset.
I am aware that the dataset is incorrect in the first place and that it is a terrible practice to structure data like that. However, these are the 'cards' I've been given and I can't change that.

Also, I am aware that I could create a separate query to extract postcodes from the ID field and use that for a lookup but I was just wandering if it's possible to get the same result without the extra dataset?

Sorry if this is a flawed question in the first place.

Any input is much appreciated.

2
reporting-servicesdatasetlookup
It is possible to get the same result without the extra dataset, but you have to change your query/stored procedure to join the table containing location column. If you can't change the dataset you have to use another dataset and use lookup(), share a sample of your tables in order to help you. Another approach could be hardcoding the location values in a SWITCH expression what could result unmaintainable if your data changes continuously. – alejandro zuleta
The syntax of your function looks correct. Please confirm that "Location" is the name of your Dataset. – StevenWhite

2 Answers

1
votes

Thank you @StevenWhite, your hint was an answer.
The issue was not in the logic of the question but in the expression.
Due to my lack of experience in SSRS-2008 I wasn't sure what the last bit of the expression was for.

Here's the fix: 

=Lookup(Fields!Postcode.Value,Fields!ID.Value,Fields!Name.Value,<b>"DataSet1"</b>)
0
votes

This may not help anybody, but it sure helped me. I was trying to find the next value in a row in SSRS, similar to the Previous() SSRS function, but in the other direction.

There is no Next() SSRS function, but I found something about using the Lookup() SSRS function, along with using the ROW_NUMBER() SQL function. ROW_NUMBER() didn't work for me, but DENSE_RANK() did.

The suggested Lookup() function was something similar to: =Lookup(Fields!RowNumber.Value+1,Fields!RowNumber.Value,Fields!PFCode.Value,"Main") Except that it didn't quite work. It should in theory, but didn't.

So then I thought, what if I just added a second, duplicate dataset of my original "Main", call it "Main2", and use the Lookup() function against that. And it worked! Maybe it's not "efficient" use of resources, but after spending the better part of two days on it, I didn't care. :-)

Hope this helps somebody out there.