none
Percent Total in a Textbox

    Question

  • Hello!

    I have a column that calculate the percentages of a row base on expression. The expression is:

    =Fields!LineNo.Value/Sum(Fields!LineNo.Value, "dsKPIs")*100

    which actually returns the right value. And now When I added a total row from the Row Group pane through the Detail level as there are no group involve in the report , no total was added. I have tried using ReportItems, RunningValue but unable to add a total to the report without the error "...aggregate function can only be used on report items contained in a page header and footer." My expression was:

    =SUM(ReportItems!txtPctTotal.Value)
    How do I add up the column containing the first expression above?

    Please help if you know. Thank you


    Zionlite

    Monday, November 18, 2013 5:27 PM

All replies


  • Hi Zionlite,

    Just as you said, Aggregate functions (including sum function) can only be used on report items contained in page headers and footers in Reporting Services. So we can’t sum on report items on report body. While, we can use Custom Code to write a function to achieve the same goal. So in order to meet you requirement, you can try to use the custom code below to sum the calculated column values: 

    1. Copy the custom code below and paste it to your report. (Right-click report>Report Properties>Code)
      Public Shared Value as double=0
        Public Shared Function GetValue(Item as double) as double
           value= value + Item
           return Item
        End Function
        Public Shared Function GetTotal()
           return value
        End Function
    2. Replace the original expression
            =Fields!LineNo.Value/Sum(Fields!LineNo.Value, "dsKPIs")*100
      with
            =Code.GetValue(Fields!LineNo.Value/Sum(Fields!LineNo.Value, "dsKPIs")*100)
    3. Use the following expression to get the sum value:
      =Code.GetTotal()

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

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Tuesday, November 19, 2013 8:25 AM
  • Thanks Katherine. I got the error that the "Sum" is not declared and it may be inaccessible due to its protection level. Error [BC30451]

    What could be wrong with the function?


    Zionlite

    Wednesday, November 20, 2013 10:36 AM