SSRS having multiple dataset Error

0
votes

I have a Dataset1 which has all records.

I have Dataset2 and Dataset3 for Dropdown filteration.

Dataset1 has a query as follows : select * from vw_shoppinghistory where storename =@storename and city = @city

Dataset2 has a query as follows : Select Distinct StoreName from vw_shoppinghistory

Dataset3 has a query as follows : Select distinct city from vw_shoppinghistory where storename = @storename

On selecting store and then the city should pull the records.

I am facing an error as

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

1
reporting-servicesssrs-2008

1 Answers

2
votes

You are referencing a field in a dataset on a control that doesn't count as a data region (data regions being things like charts, tables, lists, etc). Most commonly you'll see this on textboxes.

To fix this, you need to do 2 things:

  1. Use an aggregate expression. You can't reference a field outside of a data region without performing aggregation. You can use functions like SUM, FIRST, MIN, MAX, etc.

  2. Specify a dataset when referencing a field. For example, your code that's causing this error is going to look something like this:

    =Sum(Fields!city.Value)

What you'll need to do is specify the dataset the field comes from, like:

=Sum(Fields!City.Value, "Dataset1")