CODEWITHSUNDEEP
reporting-servicesssrs-2012ssrs-tablixreportingservices-2005
Lalalala9999
Lalalala9999

Reputation: 149

Calculate time difference between 2 columns in SSRS

I've got two fields that are calculated as per the below:

=Format(DateAdd("s", Sum(Fields!TalkTime.Value) / Sum(Fields!EmployeesWorked.Value), "00:00:00"), "HH:mm:ss")

and

=PREVIOUS(Format(DateAdd("s", Sum(Fields!TalkTime.Value) / Sum(Fields!EmployeesWorked.Value), "00:00:00"), "HH:mm:ss"), "Quarter1")

I've got a separate column to calculate the difference between the 2 columns:

=ReportItems!Textbox2625.Value - ReportItems!Textbox29.Value

However, when I run the report, the column that I used to calculate the difference between the 2 fields shows '#Error'. I'm assuming there's some kind of formatting problem, but I'm not sure how to fix it?

Upvotes: 0

Views: 595

Answers (1)

Chris Latta
Chris Latta

Reputation: 20560

Format returns a string, so you aren't trying to subtract two dates, you are trying to subtract two strings, which is throwing an error.

Firstly, use a date calculation for the Value expression:

=DateAdd("s", Sum(Fields!TalkTime.Value) / Sum(Fields!EmployeesWorked.Value), "00:00:00")

This calulates the Sum of the TalkTime over the Sum of the EmployeesWorked time for the scope of the textbox.

Put the formating into the Format property: HH:mm:ss

This separates the value of the textbox (the time) from its presentation. Now you might be able to do your calculation:

=ReportItems!Textbox2625.Value - ReportItems!Textbox29.Value

Again, put the formating into the Format property: HH:mm:ss so it displays as a time.

If this doesn't work, put the calculation in full (excluding the Format functions, but see note below on why you might not be using the Previous function correctly):

=DateAdd("s", Sum(Fields!TalkTime.Value) / Sum(Fields!EmployeesWorked.Value), "00:00:00") 
- PREVIOUS(DateAdd("s", Sum(Fields!TalkTime.Value) / Sum(Fields!EmployeesWorked.Value), "00:00:00"), "Quarter1")

Note that aggregating within the Previous function and specifying the scope (in this instance "Quarter1") requires the specified scope ("Quarter1") to be a containing scope for the current scope of that ReportItem.

For example, you might have a table with three nested levels of grouping: by Year, then by Month, then by Day. The expression =Previous(Sum(Fields!Sales.Value, "Day"), "Year") in a row associated with the group Day will return the sales value for the same day and month for the scope of previous year.

If expression contains an aggregate function that specifies a scope other than the default, the scope parameter for the Previous function must be a containing scope for the scope specified in the aggregate function call - in other words, the textboxes must be in a child scope of the "Quarter1" scope. While I don't know the scoping you are using, from your wording it looks like you are trying to get the calculation from a dataset which has the value from the first quarter, not a containing group scope? If so, you don't need the Previous function, you can just calculate the aggregate over the entire dataset "Quarter1" in the Sum function like so:

=DateAdd("s", Sum(Fields!TalkTime.Value, "Quarter1") / Sum(Fields!EmployeesWorked.Value, "Quarter1"), "00:00:00"))

Upvotes: 0

Related Questions