4

I have an SSRS 2008 R2 report that uses this expression in a table:

=Lookup(Fields!DataSet1Date.Value, Fields!DataSet2Date.Value, Fields!DataSet2Price.Value, "DataSet2")

I have 2 data sets and am using the Lookup function to get data from one dataset based on the date in another dataset.

My problem is that this works on machines that I have tried it on, but others are getting errors like this:

Error   1       [rsFieldReference] The Value expression for the text box ‘Col_D2Price’ refers to the field ‘DataSet2Date’.  Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope.
Error   2       [rsFieldReference] The Value expression for the text box ‘Col_D2Price’ refers to the field ‘DataSet2Price’.  Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope.

What other things can we do to troubleshoot this issue? We are all using the same 2008R2 version.

dtc
  • 10,136
  • 16
  • 78
  • 104
  • You'll need to tell us more about the differences between these machines. Do they refer to the same database? Do they get the exact same data? Are the parameters exactly the same? Is one perhaps a dev pc, and the other a real server? In addition, how complex is the report that's giving this issue? Have you tried making a very small repro? Perhaps something in the report's not playing nice. – Jeroen Sep 11 '12 at 14:23
  • have you solved this issue? – Alex Gordon Feb 01 '13 at 05:04

1 Answers1

6

I oftern get this "phantom" error when using the LookUp function. I call it phantom as no where can I find a reason, but there you have the error pop up.

The only way to get around it in my cases is to use the secondary function LookUpSet.

Hope I've helped.

Edit:

Furthermore you've intrigue me so I've done some research:

  1. The lookup function is only for 1-to-1 relationship.
  2. The loopupset funcrion is for 1-to-many relationship.
  3. The multilookup function is for many 1-to-1 relationships, i.e. an array of single values where there is only 1 value in the second dataset. Not relevant but quite interesting.

Also I came across a potential fix. This being on the new machines try and open the datasets in the report and refresh all fields in the dialog box. For some reason this may relink the fields to this expression. Go figure...Blockquote

glh
  • 4,900
  • 3
  • 23
  • 40
  • thanks so much! now perhaps you can help withthis? http://stackoverflow.com/questions/14669689/building-and-deploying-works-but-not-running – Alex Gordon Feb 04 '13 at 17:39