How to reference calculated fields in a report using SQL Server Reporting Service

Question

I'm writing a report using SQL Server2005 Reporting Service. I have a stored procedure which provides a dataset.

The data is split into Groups in the report. Each group footer has a function (expression) which is an average calculated from the fields in the table (lets call this value X). At the end of the data, I want the report to have a footer which includes the SUM of all the X values.

Note that I dont want a sum of all the fields in the column, but I want a sum of all the calculated values in the group footers. This would be really easy to do in Excel

This problem would seem to be straightforward, but I'm battling to find an answer. I would appreciate any help, thanks.

Answer 1

After searching through the MS Forums, and much trial and error, my findings are that this is a limitation in this version of BIDS. The following are workarounds:

1) Upgrade to a later version of BIDS. I didn't investigate whether my problem would be addressed in a later version because upgrading is not an option at this time.

2) Add a new Calculated Field to the dataset. This didn't work because, for my application, the Calculated Field requires an Aggregate function, which is not supported.

3) The best solution was to add a VB code function to the Report Properties as follows:

Dim Shared totalBalance
Dim Shared Cnt AS Integer

Public  Function AddTotal(ByVal balance ) 
                totalBalance = totalBalance + balance
                Cnt=Cnt+1
                return balance

End Function

Public Function GetTotal() 
                return totalBalance
End Function   

And then reference the code from the group footer as: =Code.AddTotal(Avg(Fields!Amount.Value))

And from the Report Footer as:

=Code.GetTotal()

This solution worked well in the Group Footer, but in the Report Footer at the end of the report, the variables Cnt and totalBalance were reset to 0.