SSRS Error - "Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate

Question

I'm new to SSRS and I'm not sure if it will do what I would like to do.

I'm getting the following error in SSRS:

"The Value expression for the text box 'Textbox17' refers to the field 'DayCnt'. Report item expressions can only refer to fields withing the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case."

I have a list that's 3 rows and 3 columns. I need to use multiple data sets across the rows and columns. I would like everything on the report to be grouped on the school ID, which each dataset does have.

I will be using multiple datasets per cell in some cases and I'm using a textbox and then dragging the dataset field into it. I'm not sure if grouping is the problem. I'm not sure how to group the entire list at once, or if it is row based, or how the grouping works with a list with multiple columns.

How can I get everything in the list to be based off of the school ID?

Thank you for your help.

Answer 1

You can reference more than one dataset in the same data region (table, etc.) but only if it makes sense to use aggregate functions on all the datasets except the primary one that your grouping is based on. I'm not sure if this makes sense for your use case or not.

An aggregate function is something like First. If you don't specify the dataset, it defaults to the "current dataset." The current dataset is an invisible default that you can't see or set anywhere in the UI, as far as I can tell.

=First(Fields!MyField.Value)

vs.

=First(Fields!MyField.Value, "MyDataset")

Now, there are a couple of tricky things to know about the Report Builder UI.

• When you drag a field into your report, the expression it creates does not specify the dataset.
• When you drag a field into your report, it changes the current dataset!

This leads to exasperating behavior such as the following:

1. Create a data region.
2. Set its grouping to a field from Dataset1.
3. Drag in a field from Dataset1.
4. Run the report. It works!
5. Drag in a field from Dataset2. RB will automatically use an aggregate function, as you would expect.
6. Run the report again. Now you get an error, not on either of your fields, but on the grouping of your data region.

The problem is that dragging in the field from Dataset2 changed the current dataset to Dataset2 and broke everything that uses Dataset1 without explicitly specifying it. The solution is hacky:

1. Manually change the expressions of all your fields from Dataset2 so they explicitly reference the dataset, or
2. Reset the current dataset by dragging in a field from Dataset1 and deleting it.

Answer 2

You cannot use different datasets on only one SSRS table. One table should only refer to one dataset.

And the solution to your question is: recreate your dataset (query), try to get one dataset by using distributed query if they are on different server instance, or specify database name when they are on the same server.