The handler that was supposed to be returning the report as an attachment in the request was displaying a 'Page cannot be displayed' and the System event log had warnings logged whenever this happened:-
A process serving application pool 'DefaultAppPool' suffered a fatal communication error with the World Wide Web Publishing Service. The process id was '6244'. The data field contains the error number.We couldn't recreate the problem locally in Visual Studio web development server, it only seemed to be for this one report when running in Windows 2003 and IIS 6.0.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
The Xslt engine being used was the standard XslCompiledTransform class in the .NET framework, the previous implementation of Xslt transforms XslTransform had been marked as obsolete - so all during development the team was working with XslCompiledTransform. The only difference we could see with the broken report and the working ones (after a while of checking and double checking other probable causes) was that the Xsl file for that report was larger (~480Kb).
The migration guide in MSDN makes no mention of there being a file size limitation when using XslCompileTransform, but we were able to find this forum post that explains the issue we were having. The problem is due to the JIT compiler for Xslt transformation running out space for declaring locals during IL compilation. The suggestion is to split the XSL into smaller transforms, but we took an alternate route and switched back to using XslTransform in the code. The older class handles the large report transformation - it might obsolete, but it works!
Building reports like this way isn't the most elegant solution, but we hit this problem late in the development cycle that it was far too late to consider switching to a different method. It's a little disappointing that MS aren't more explicit about that limitation in the migration guide.