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 IsPrimaryContact(Bit Column) ContactNumber City
1 1 124 City1
1 0 345 City2
1 0 678 City3
1 0 745 City1
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
We added the tables as DataSet in the Report data of the SSRS. DataSet1- for tblEmp and DataSet2 for tblEmpContacts
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
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.
LookupSet is evaluated after all filter expressions are applied.
Only one level of lookup is supported. A source, destination, or result expression cannot include a reference to a lookup function.
Source and destination expressions must evaluate to the same data type.
Source, destination, and result expressions cannot include references to report or group variables.
LookupSet cannot be used as an expression for the following report items:
Dynamic connection strings for a data source.
Calculated fields in a dataset.
Query parameters in a dataset.
Filters in a dataset.
Report parameters.
The Report.Language property.
BalasubramanianKM
The value expressio for the text box 'textbox67' refers to the field 'IsOwner'.
What is the expression for the 'textbox67' ? Do you have a field called IsOwner? I think this is a case of not valid expression for the textbox67. Open Document Outline in Report and check your expression.
With Regards,
Krunal Parekh
Krunal
MSDN Community Support
Please remember to "Mark as Answer" the responses that resolved your issue.
None
0 Points
10 Posts
how to use column from different datset in lookupset against a constant in ASP.NET Report
Sep 02, 2015 11:21 PM|BalasubramanianKM|LINK
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
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
All-Star
15252 Points
2074 Posts
Re: how to use column from different datset in lookupset against a constant in ASP.NET Report
Sep 04, 2015 03:50 AM|Krunal Parekh|LINK
Hello,
Are PrimaryContract and Seconday Contract Tablix or textbox? For LookupSet there are some limitations that you should look into.
Please see: LookupSet Function (Report Builder and SSRS)
The following restrictions apply:
LookupSet is evaluated after all filter expressions are applied.
Only one level of lookup is supported. A source, destination, or result expression cannot include a reference to a lookup function.
Source and destination expressions must evaluate to the same data type.
Source, destination, and result expressions cannot include references to report or group variables.
LookupSet cannot be used as an expression for the following report items:
Dynamic connection strings for a data source.
Calculated fields in a dataset.
Query parameters in a dataset.
Filters in a dataset.
Report parameters.
The Report.Language property.
What is the expression for the 'textbox67' ? Do you have a field called IsOwner? I think this is a case of not valid expression for the textbox67. Open Document Outline in Report and check your expression.
With Regards,
Krunal Parekh
MSDN Community Support
Please remember to "Mark as Answer" the responses that resolved your issue.