I recently ran into an unexpected behavior of SSRS while writing a complex report. The report required various calculations that would refer to specific previous groups and details, and I had decided to solve the problem by writing a custom report function that would evaluate on each detail row, saving the information I would need later in a dictionary. My solution worked fine, as long as my custom calculation in a group footer didn't depend on the contents of that group's details. Upon further investigation, I learned something very interesting about the order in which SSRS evaluates group headers, footers, and details...

Here is how to observe the behavior for yourself: create a stored procedure like this one, that generates some simple test data:

CREATE PROCEDURE dbo.HundredRows
AS
SET NOCOUNT ON

create table #result (
id int,
grp int
)

declare @id int
set @id = 1

while @id <= 100
begin
insert #result
values ( @id, ( @id - 1 ) / 5 )

set @id = @id + 1
end

select *
from #result

This will return a result set whose id column increments in steps of one from 1 to 100, and whose grp column increments in steps of one for every five rows. Next, create a simple report layout with a table, showing the values of each column and grouping by the grp column. In the group header and footer, include the same column references. Next, add this custom code:

Dim eval_order As Integer = 0

Public Function show_eval_order() As String
  eval_order = eval_order + 1
  Return eval_order.ToString()
End Function

Display the result of this function in the group header, footer, and detail rows. Your layout should look like this:

Once you have the report set up, click on the preview tab to see the surprising results:

I've only shown the first two groups here, but notice what has happened! The order column clearly indicates that the group header and footer are both evaluated before the details in between! By looking at the value of the id column in the footer, we can see that the the group's header and footer rows are printed while the "current" row in the dataset is still positioned on the first row in the group.

While this is interesting, it isn't very important as long as you stick to using SSRS's built-in aggregation functions. It is only when you attempt to "roll your own" by saving the dataset in your own storage for later reference that you might run into trouble. In my case, I used the following workarounds:

1. Move calculations that rely on the details having been traversed from the group footer to the following group's header. It's clunky and less intuitive, but that's what SSRS has forced upon us.
2. For the final group, move the calculation out of the table entirely, and put it into textboxes just below the table. While there is no way to tell SSRS to evaluate the textboxes after it evaluates the table, it does so at least for the 2005 version.

In summary, it sure would have been nice if SSRS would have evaluated group headers and footers after it evaluated the details that each group contains, but since we have clearly demonstrated that it doesn't, we now know what we will have to do in order to make custom report code produce the output we want: by putting those custom calculations into the following group's header, and by putting the final footer calculation outside the table entirely.