Aggregation of expressions in SSRS

Imagine you have an SSRS report with a tablix, which contains a calculated field. For example, there is a date field (from a data set) and a calculated field showing the difference between the given date and the current date:

TablixWithoutTotal

The Diff field is calculated by the following expression:

=DateDiff("d", Fields!GivenDate.Value, Today)

Now we want to add a field showing the sum of differences. First off all, add a new row below the Details group (i.e. the row with individual dates) using Insert Row > Outside Group – Below.

Tablix

The only question is how to implement the expression of Total diff. You probably know how to summarize values of a dataset field – by =SUM(Fields!SomeField.Value). But we’re not limited to fields, we can use almost any expression there. Therefore we simply put the expression calculating date difference to the SUM function:

=SUM(DateDiff("d", Fields!AccountingDate.Value, Today))

Indeed, the total difference is calculated as expected.

Result

Leave a Reply

Your email address will not be published.