SSRS - Expression using different dataset fields without the “first” or “sum”

Question

I have a ssrs report with three datasets. I would like to populate a column that shows =(Fields!Total_Hrs.Value, "Dataset3") without the "SUM(Total_Hrs)" that is showing as an option when I click on Datasets under the Category.

The problem with using =(Fields!Total_Hrs.Value, "Dataset3") is that I'm getting an error stating "Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope."

Would one know what is causing the error? Is there a way to have the expression window show my Fields as DataSet3?

Thank you.

Chris Latta Chris Latta · Accepted Answer · 2012-12-17T22:00:56

The problem is how is SSRS supposed to know what row you want from Dataset3? So you have two choices:

Option 1: If you want the total per person, include the total hours in your main dataset as a nested query calculation, like so:

SELECT Person, Hours, TotalHours
FROM Time
INNER JOIN (
    SELECT Person, SUM(Hours) AS TotalHours 
    FROM Time 
    GROUP BY Person
) TotalTime ON Time.Person = TotalTime.Person

Option 2: If it is a genuine grand total, just have Dataset3 return one row and use an aggregate on this:

SELECT SUM(Hours) AS TotalHours
FROM Time

and use this in your expression with an aggregate:

=SUM(Fields!TotalHours, "Dataset3")

SSRS - Expression using different dataset fields without the “first” or “sum”

1 Answers