Creating total fields RRS feed

  • Question

  • Hi everyone,


    I have a query that has, among other fields, the following:

    Costs  (expenditures)

    Cost_Quarter   (indicates which quarter cost was incurred)


    I’m attempting to create fields for my report which are:

    Q1_Costs   (total costs incurred in the first quarter)

    Q2_Costs   (total costs incurred in the second quarter)

    Q3_Costs   (total costs incurred in the third quarter)

    Q4_Costs   (total costs incurred in the fourth quarter)


    BUT, when I try to create the Qx_Cost fields with the statement

    =IIF( (Fields!Cost_Quarter.Value) = 2, SUM(Fields!COSTS.Value), 0)

    I get the error:

    The expression used for the calculated field ‘=IIF( (Fields!Cost_Quarter.Value) = 2, SUM(Fields!COSTS.Value), 0)’ includes an aggregate function.  Aggregate fundtions cannot be used in calculated field expressions.


    I tried building variables:

    =IIF( First(Fields!Cost_Quarter.Value, "Report_Info") = 1, SUM(Fields!COSTS.Value, "Report_Info"), 0)

    But then I get the error:

    The Variable(Q1_Costs) value expression for the report ‘body’ uses aggregate function First, Last or Previous.  These aggregate functions cannot be used in valiable value expressions


    If I can't use an aggregate, what do I use?  Can anyone set me onto the correct path to create the variable/expression/whatever I need to display on my report?



    Thanks in advance,


    Monday, November 8, 2010 7:22 PM

All replies

  • I ran into this before. Prertty sure I ended up adding a textbox (hidden) to the report. Used the textbox to do my SUM function, the used that info in my IIF statement..
    Tuesday, November 9, 2010 11:01 AM
  • Steven,

    Thanks for the reply.  I've added some textboxes (to be hidden later) and I can get the sum-total to show there but I can't get that total into my regular display.  I know I've got some little thing wrong and I will eventually get it - it may just take a while.  I'm lucky in that I'm not working on a dedline and I have time to spend on this without someone really getting after me.

    Your suggestion has probably put me on the right track, although it seems stupid/dumb/____ that you can't do an aggregate function on a calculated field.  Is this something that may be added in a future version or is there some logical reason that this works this way?

    Thanks again,



    Tuesday, November 9, 2010 3:28 PM