Search code examples
sql-serverreporting-servicesreportingservices-2005

Sum the value of aggregate function in SSRS report

I have a column that gets its value from Commission %(percentage is based on a customer # so it can change) multiplied by Profit column by using the following expression:

=Fields!Profit.Value * ReportItems!Commission%.Value

I need to get subtotal for the Commission Total column. When I try to total the commission I get the error:

"The Value expression for the textrun 'Textbox91.Paragraphs[0].TextRuns[0]' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers."

I then tried to SUM the value of the textbox by using =SUM(ReportItems!Textbox65.Value) but it would only grab the last result and not sum all of them.enter image description here

The subtotal of the Commission Total column should be 14.64 not 2.13045. I am trying to total the Commission Total column.

Solution

  • Well, the problem is that aggregate functions like Sum don't like to operate on ReportItems. I think that the value in the Commission% column comes from an expression that uses elements (or even just one element) of the Fields collection, so you just have to use that same expression when calculating the total commission, for example

    =Sum(Fields!Profit.Value*Fields!Commission%.Value)