locked
SUM ReportItems! in Matrix With Row Grouping RRS feed

  • Question

  • Hello All -

       I've been struggling with this problem for a bit now, and have looked over some possible solutions, but none seem to address my particular issue.  I have 4 columns in my matrix table that I need to subtotal by the row grouping.  The columns are using calculated expressions to derive their values, and as such when I try to sum these, I get the "Aggregate functions can be used only on report items contained in page headers and footers." error.  Having the sum in the footer doesn't really help me as I need the totals by row grouping.  

    For instance, a textbox that I have named LabelPayout uses this expression: 

      = ReportItems!UnitCost.Value * (Sum(Fields!Redeemed.Value))

    Using SUM(ReportItems!LabelPayout.Value) gives me the aggregate error.  

    I've seen solutions that call VB code, but those solutions still seem to require that the total live in the footer of the report. Is there a way that I can have each total appear under each row grouping?

    Thanks very much.

    Travis

    Thursday, February 4, 2016 3:51 PM

Answers

All replies

  • Hi Travis,

    As the error message stated, aggregate functions can be used only on report items contained in page headers and footers. So it’s expected that the error will throws out when you use the expression =SUM (ReportItems!LabelPayout.Value) on report body section.

    In your scenario, which expression you have specified for the textbox UnitCost? If the textbox only contains field values, or you could modify the query to return the textbox UnitCost values in one column field. Then you can get subtotal using the expression like below:

    =SUM(Fields!UnitCost.Value* (Sum(Fields!Redeemed.Value)))

    If the textbox UnitCost contains a value expression instead of field values, please try to specify its value expression as an calculated field on dataset level. Then get subtotal based on the expression below:

    =SUM(Fields!CalculatedField.Value* (Sum(Fields!Redeemed.Value)))

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu


    Qiuyun Yu
    TechNet Community Support


    Friday, February 5, 2016 6:48 AM
  • Thank you Quiyun. In the end, I changed my dataset queries so that I wouldn't have to reference report item fields.

    Tuesday, February 9, 2016 9:59 PM