how to use column from different datset in lookupset against a constant in ASP.NET Report

Question

User-1644108997 posted

Hi Team,

We have something like below scenarioin ASP.NET report..there a stored procedure which returns result like this..2 result set  with only EmpId =1 values

Empid      EmpName  HasPrimaryContact        HasSecondaryContacts

1               xyz             1                                     1

EmpId     IsPrimaryContact(Bit Column)      ContactNumber        City

1              1                                                     124                         City1    

1              0                                                     345                         City2

1              0                                                      678                         City3

1              0                                                      745                        City1

1. We created a DataSet1.xsd in the Visual studio project and created a Table structure for the above each result set . Lets say tblEmp and tblEmpContacts
2. We added the tables as DataSet in the Report data of the SSRS. DataSet1- for tblEmp and DataSet2 for tblEmpContacts
3. We created a table in the SSRS and assigned the DataSet1 (ie.tblEmp)

We created a report like below

EmpId  :  1

EmpName : XYZ

PrimaryContact : City1(124)

Secondary Contacts : City2(345)

                                   City3(678)

                                   City1(745)

I created a expression for the PrimaryContact  and secondary contact like below

1)  For Primary contact

=Join(LookupSet(True,Fields!IsPrimaryContact.value,(Fields!City.Value+"("+Fields!ContactNumber.value),"DataSet2"),vbCrLF)

her I put True as first field bcoz it needs to fetch only rows where  IsPrimaryContact is True  from the DataSet2 (tblEmpContacts)

2) For Secondary Contact

=Join(LookupSet(False,Fields!IsPrimaryContact.value,(Fields!City.Value+"("+Fields!ContactNumber.value),"DataSet2"),vbCrLF)

her I put True as first field bcoz it needs to fetch only rows where  IsPrimaryContact is False from the DataSet2 (tblEmp)

The above expression's worked only once after I rebot my machine its not working instead it shows  some error

Error:

The value expressio for the text box 'textbox67' refers to the field 'IsOwner'.
Report item expression can only refer to fields within the current dataset scope or,
If inside an aggregate,the specified dataset scope.

Please help how to resolve this issue