SQL Report Builder: Use aggregate function on ReportItem

Question

I've entered the following expression for a given cell, which is essentially a dollar value divided by a quantity to get a cents per gallon value, labeled as Textbox41:

=ReportItems!Total_Gross_Profit2.Value / ReportItems!Gallon_Qty3.Value

What I was trying to do is use this expression for an AVG aggregation in another cell =avg(ReportItems!Textbox41.Value), but I'm getting an error:

The Value expression for the textrun 'Textbox79.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.

Is there some limitation that does not allow aggregations on ReportItems? I've also tried the following, which also did not work:

=AVG(ReportItems!Total_Gross_Profit2.Value / ReportItems!Gallon_Qty3.Value)

Where am I going wrong here?

pwilcox pwilcox · Accepted Answer · 2019-09-30T17:36:21

Regarding your question:

Is there some limitation that does not allow aggregations on ReportItems?

You have your answer in the error message you provided.

As for the resolution, it's hard to give precise guidance with the information you provided, but in general, start thinking in terms of dataset fields instead of report objects. If you're operating from inside a matrix or table, and if the values for 'Total_Gross_Profit' and 'Gallon_Qty_3' look something analogous to this:

= ReportItems!ProfitsFromX.Value + ReportItems!ProfitsFromY.Value
= ReportItems!GallonQtyA.Value + ReportItems!GallonQtyB.Value

Point to the fields directly instead:

= Fields!ProfitsFromX.Value + Fields!ProfitsFromY.Value
= Fields!GallonQtyA.Value + Fields!GallonQtyB.Value

That way, when it comes to aggregation, it's more clear what to do:

= avg(
      (Fields!ProfitsFromX.Value + Fields!ProfitsFromY.Value)
    / (Fields!GallonQtyA.Value + Fields!GallonQtyB.Value)
)

And if you find that cumbersome, you can create calculated fields on the dataset object, and reference those instead where appropriate.

SQL Report Builder: Use aggregate function on ReportItem

1 Answers