none
SSRS Expression adding a total value

    Question

  • In one of the SSRS reports WeeksBookedInPropel is measured with the following expression:

    =iif(isdate(Parameters!DateBookedThru.Value),sum(Fields!PeriodCount.Value),

    "")

    I want to add a total in the bottom and get the sum of the WeeksBookedInPropel in the bottom of the report.

    I tried this but it gives me an error:

    =Sum(iif(isdate(Parameters!DateBookedThru.Value),sum(Fields!PeriodCount.Value),

    ""))

    This is the error that I get:

    [rsAggregateofAggregate] The Value expression for the textrun 'textbox103.Paragraphs[0].TextRuns[0]' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.

    [rsInvalidAggregateScope] The Value expression for the textrun ‘textbox17.Paragraphs[0].TextRuns[0]’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.

    How would I do that?

    Thanks!


    Kajoo
    Monday, June 21, 2010 6:15 PM

Answers

  • Hi Kajoo,

    Thank you for your reply.

    We can place the code in the report code section. Generally speaking, place it in this way:
        1. In design mode, click the menu "Report" >> "Report Properties"
        2. Go to "Code" tab.
        3. Copy and paster the code I have posted before.

    Now, we can use the custom function in expression in this way: =Code.<FunctionName>(parameters)

    Here is a article described more detailed about this steps, you may like to have a look:
    http://support.microsoft.com/kb/920769/en-us

    If you still have more questions, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by Kajoo Thursday, June 24, 2010 4:52 PM
    Thursday, June 24, 2010 6:00 AM
    Moderator

All replies

  • Hi Kajoo,

    In SQL Server Reporting Services 2008 R2, we have a enhacement that we can use nested arrgegate functions. This means the expression you posted works fine in SQL Server Reporting Services 2008 R2.

    In SQL Server Reporting Services 2008 or earlier, we can use custom code the solve the issue.
     1. Define custom code functions in the report.

     Dim total As Integer
     Public function AddTotal(Byval periodCount As Integer)
      total = total + periodCount
      return periodCount
     End Function
     Publich Fucntion GetTotal()
      return total
     End Function


     2. Replace the original expression
           =IIF(IsDate(Parameters!DateBookedThru.Value),SUM(Fields!PeriodCount.Value),"") 
    with
           =IIF(IsDate(Parameters!DateBookedThru.Value),Code.AddTotal(sum(Fields!PeriodCount.Value)),"")
     3. Now, in the report footer, using the following expression to get the total value:
           =Code.GetTotal()

    For more information using custom code, please see:
    http://msdn.microsoft.com/en-us/library/ms157328.aspx

    If you have any more questions, please feel free to ask.

    Thanks,
    Jin Chen
     
     


    Jin Chen - MSFT
    Wednesday, June 23, 2010 12:04 PM
    Moderator
  • Hello Jin,

    Thank you for taking the time to respond to my trace. I am pretty new to SSRS 2008 and I can't figure out how or where to place the script.

    Thanks


    Kajoo
    Wednesday, June 23, 2010 11:53 PM
  • Hi Kajoo,

    Thank you for your reply.

    We can place the code in the report code section. Generally speaking, place it in this way:
        1. In design mode, click the menu "Report" >> "Report Properties"
        2. Go to "Code" tab.
        3. Copy and paster the code I have posted before.

    Now, we can use the custom function in expression in this way: =Code.<FunctionName>(parameters)

    Here is a article described more detailed about this steps, you may like to have a look:
    http://support.microsoft.com/kb/920769/en-us

    If you still have more questions, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by Kajoo Thursday, June 24, 2010 4:52 PM
    Thursday, June 24, 2010 6:00 AM
    Moderator