Lookup in ssrs not displaying the value in tablix.

Question

Working environment is sharepoint2010. we have already connected the sharepoint list datasource to the report project in vs2008. Its possible to display the data in a single dataset using tablix. we have 2 datasets 1)salesMain 2)Customers

In salesMain, we have fields such as SalesID,CustomerID,saleDate,TotalAmount,taxAmt,netAmount

In Customer we have customerID,CustomerName,Address

I need to display the data like SalesID,CustomerName,Address,netAmount,TaxAmt,TotalAmount

We had assigned the SalesMain dataset to a Tablix, which displays the data as it is. The problem is that some of the data displayed is foriegn key values like CustomerID. we need to get the values like "customername" using the foriegn key value(CustomerID). What we done next is instead of the customerID field, using the placeholder, we had set an expression using the Lookup,but this field displays blank. The lookup method is some thing like this lookup. SalesMain dataset is the source(datasetname) of Tablix table1. the look up function we used is =Lookup(Fields!CustomerID.Value, Fields!customerID.Value, Fields!CustomerName.Value, "Customer")

where 1st parameter is the key to lookup(foriegnkey), 2nd parameter is the key to match on(primarykey of Customer),3rd parameter is the value to be retrieved(to be displayed in the tablix cell) and 4th parameter the dataset in the report(Customer)

Could any one suggest why Lookup dosent wrk. In the sharepointlist we did not use any lookup(in the sense that table relationship is not set.)

Answer 1

I had the same problem working with Sharepoint datasets and lookupset function, I noticed that inside Sharepoint source definition the foreign key field was Lookup type, so I fix this casting the field in SSRS as follows:

=Lookup(**CInt(Fields!CustomerID.Value)**, Fields!customerID.Value, Fields!CustomerName.Value, "Customer")

Answer 2

Sorry to disappoint you but one table(tablix) can handle only one dataset.

Answer 3

Jijeshan009, you are right, Lookup should do the trick for you.

Take note:
- When previewing the report in the Business Intelligence Development Studio, open the error list and check the warnings. Lookup errors could be on that list (e.g. conversion failures).
- Sometimes all of the code looks correct but an #error will be displayed in the columns which are usings lookups. Closing BIDS, restarting the machine etc. will not work. What can work though is executing the report with different parameters (if you have any). You'll get fresh data instead of cached data and the lookups will probably work.
- The columns of dataset1 and dataset2 have to be of the same type. You could even mix a dataset from Oracle with a dataset from SQL Server: e.g. a varchar2 column and a varchar column.
- @Gil Peretz, Lookup will work for every row, not only the first. I think the data is cached because I have no performance issues with it (using multiple columns even).

Answer 4

Slightly off topic, but you also get this error when using the Lookup function where the value of the foreign key (the first parameter) is NULL. At least that's what I found when trying to use Lookup to join two datasets, both SharePoint lists.