How to use two datasets in an ssrs report and referencing two fields appropriately?

0
votes

I am making an ssrs report in vs2008,sql2008 and everytime I try to preview I get the following error.

"The value expression for the texbox 'Textbox37' refers directly to the field 'AvgRate' without specifying a dataset aggregate.

When the report contains multiple datasets, field references outside of a data region must be contained outside of a data region must be contained within aggregate functions which specify a dataset scope."

I think problem arises as a result of having two datasets in the report the code which is giving the error is here below:

=iif(Sum(Fields!CostData.Value)-Sum(Fields!Data.Value)>0,0,
(Sum(Fields!Data.Value)-Sum(Fields!CostData.Value))*Fields!AvgRate.Value)
1
sql-serverreporting-servicesssrs-2008

1 Answers

1
votes

If your Textbox37 not in tablix you should set a dataset scope from wich field is selected. So, your expression should look something like this:

=iif(Sum(Fields!CostData.Value, "DataSet1") - Sum(Fields!Data.Value, "DataSet2") > 0, 
    0, 
    (Sum(Fields!Data.Value, "DataSet2") - Sum(Fields!CostData.Value, "DataSet1")) * First(Fields!AvgRate.Value, "DataSet1"))