none
Totaling an expression field

    Question

  • I have the following expression in a text box in a detail row.  I want to sum all the values in this particular expression for each customer.  How do I do this?  I tried using sum(ReportItems!TextBoxName) but I receive an error stating that aggregates for report items can only be used in report page headers or footers.  I also tried putting the expression into an aggregate function, sum(blahblah) but the report takes for ever to return data, and I know my client will not like waiting around for a week and a half for the report to run.

    Any suggestions would be appreciated.

    Thanks

    Ralph

    =iif(month(Fields!GLPOSTDT.Value) = Month(Parameters!Month.Value+"/"+"01"+"/"+Parameters!Year.Value) and year(Fields!GLPOSTDT.Value) = Parameters!Year.Value, Fields!RealDeferredAmount.Value,0)


    Thursday, December 27, 2012 8:58 PM

Answers

All replies

  • Can you please post the screen shot of report design? Where do you want this aggregation to be shown.

    Also take a look at this:

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/a3517a3b-16d6-4b1d-9201-afcdd949dfdc

    Regards,Eshwar.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, December 28, 2012 4:08 AM
  • Hi Ralph,

    You can try the folloiwng expression:

    =iif(month(Fields!GLPOSTDT.Value) = Month(Parameters!Month.Value+"/"+"01"+"/"+Parameters!Year.Value) and year(Fields!GLPOSTDT.Value) = Parameters!Year.Value, Sum(Fields!RealDeferredAmount.Value),0)

    Another possible approach is to do the aggregate in the query statements, and create another dataset to result the aggregate value, and then use Lookup() function to retrieve the aggregate value for each customer. For more information about the Lookup function, please see:
    Lookup Function   

    If it doens't work, please post a screenshot to show the details of the issue for further analysis.

    Regards,


    Mike Yin
    TechNet Community Support

    Sunday, December 30, 2012 5:20 PM
    Moderator
  • An easy way would be to use custom code like shown below

    http://www.sqljason.com/2010/07/aggregate-of-aggregate-function-in-ssrs.html


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Sunday, December 30, 2012 8:03 PM
    Moderator