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?

Shopwindow

Answer 1

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.

Ricebird

Thank you for your reply @Long Could you also use multiple tables utilizing multiple data sets? – Shopwindow

@JM1, you could, and you can show different datasets in one report using different tables or matrixes. – Ricebird

The goal is to have all data from one school per page, so if I use multiple tables, I would just group each table based on the schoolID? Thank you. – Shopwindow

That is not a good design, and if you group by each table, you cannot control to let SSRS show one school per page, the way you can achieve your goal is like what I posted, try to build one query that returns you all school info. – Ricebird

Answer 2

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.

Influence

Answer 3

You might have done some modification on the Dataset and you did not refreshed the fields.

Transpose

Answer 4

You might get this error, when a report is using some field in a dataset, to populate data. But due to some reason that dataset is not getting refreshed properly in Report Builder. Since the dataset is not getting refreshed properly, either you might not be able to see all fields or some fields under your dataset in Report Builder (when you expand a dataset you will see all the fields in the dataset). Therefore, the report is unable to access the particular field in your dataset. So, after you have done any modification & when you try to save the report you might get this error.

One reason for this might be that you have changed the Stored Procedure of a dataset & now the dataset is not getting refreshed properly, when you try to refresh the fields (using the 'Refresh Fields' button in Report Builder). It might be due to an incorrect or out of range value that you have entered to one of the SP parameter values in the pop up, that appears when you click on the Refresh Feilds button.

Cookgeneral