locked
Sum reportitems RRS feed

  • Question

  • I want to do is display the sum of  textboxes in a group so I can have a subtotal for every group.

    I tried these things:

     

    Sum(ReportItems!txtbudget.Value).

    I can't use Fields collection because the textbox value is from custom code so there are no Fields.

     

    I searched for options on this and one option was supposed to be adding a field to the dataset. I tried this and I get "An error occured on the report server" when building the report before I even reference the field in the report.

     

    I even wrote code to do the sum and passed in the ReportItem. I then get the error about the grouping scope.

     

    I want the payroll sum to be the subtotal of each column. The budget values along with some other columns are from custom code that makes database calls (which was a workaround for another problem).

     

    Object Object Title Budget Current Spent YTD Spent
             
    4100 EMPLOYEES $10,000.00 $11,307.45 $40,000
    4105 EMPLOYEES - TEMPORARY $2,000.00 $506.00 $7,000
    4200 EMPLOYEE BENEFITS $13,000.00 $2,354.74 $30,000
      Payroll      

    Tuesday, June 12, 2007 9:55 PM

Answers

  • >>

    I can't use Fields collection because the textbox value is from custom code so there are no Fields.

    <<

     

    Are you absolutely sure you can't do this without referencing the reportitem/textbox value?

     

    Here's what I just did to check

     

    • I created a textbox in the detail band with this expression:


      =Code.MyUDF(Fields!User.Value)

    • I created the following custom code as a stand-in for yours. MyUDF is pretty simple, I recognize that your code will be more complicated than this, but it should serve for the purpose of illustration.

        Public Function MyUDF(tsString As String) AS Integer
           Return LEN(tsString)
         End Function

    • In my group footer, I *repeated* the call to MyUDF but summed it with the appropriate scope, like this -- IOW, there is no relationship between this textbox and the one in the detail band, they just both call the same code to do their work except that the group level has the aggregate function added "embracing" that code:

        =Sum(Code.MyUDF(Fields!User.Value),"table1_Group2")

     

    ... and this appeared to work fine...

     

    BTW I can repro your analysis of the order in which things are triggered with the custom code as you are trying to do it (manually accumulating the total), so that does look like it might be a dead end... I'm just not sure you need it..

     

    >L<


     

    Wednesday, June 20, 2007 8:34 PM

All replies

  • You cannot directly sum the report items since they are not known at design time. Here are a few options:

     

    1. Add calculated fields to the report dataset that reference custom code.

    2. Assuming SQL Server 2005, use a CLR stored procedure to prep the dataset.

    3. Implement a function that sums the values similar to the one mentioned here.

    Wednesday, June 13, 2007 1:52 AM
  • Thanks for your answer.

    1. I tried to use a calculated field in my dataset and get 'An error occured on the report server' because, I assume, I am referencing a reportitem.

    2. I will investigate this but I am using RS2005 with a RS2000 datasource so I'm not sure if this is an option.

    3. I tried to use a function but it gives an error about the group scope since I used the function in a field within a table group with a reportitem as a parameter. Maybe I did this wrong.

    Here is what I did to use the function:

    A. Create a function to sum values

     

     Function DoSum(Byval Value as Decimal) As Decimal

    Dim MyNumber As Decimal
        MyNumber = MyNumber + Value
        Return MyNumber
        End Function

     

    B. Insert a table group so I can group on the object field

    C. Use the function in a field in the group like this =Code.DoSum(ReportItems!txtbudget.Value)

     

    Error- Report item expressions can only refer to other report items within the same grouping scope

     

    Wednesday, June 13, 2007 1:40 PM
  • 1. You can reference a report item because a calculated field is computed before the report is generated and the ReportItems collection is available. You can pass the value of another dataset field however.

    2. What's RS2000 data source? CLR are only available in SQL Server 2005.

    3. The idea was each textbox in the details to pass its value to the function which will accumulate the values. Then, the footer will call another function to get the accumulated value and reset it to 0.

     

    Wednesday, June 13, 2007 4:39 PM
  • 1. That is what I figured

    2. I meant to say SQL2000 datasource. I guess I can't use a CLR proc right now until we move our data to SQL 2005

    3. I relooked at the link you gave me and I might be able to use 2 functions to do this. I will give it a try.

    Wednesday, June 13, 2007 5:52 PM
  • 4105 EMPLOYEES $10,000.00 $11,000.00 $40,000.00
    4115 EMPLOYEES - TEMPORARY $2,000.00 $600.00 $7,000.00
    4201 EMPLOYEE BENEFITS $13,000.00 $2,000.00 $30,000.00
      Payroll $0.00    
    5001 COMMUNICATION SERVICES $6,000.00 $0.00 $13,000.00
    5051 EMPLOYEE DEVELOPMENT SERVICES $7,000.00 $0.00 $300.00
    5101 OTHER SERVICES $20,000.00 $0.00 $400.00
      Operating $25,000.00    

     

     

    I can't seem to get this to work. I am trying to get the subtotal for each group. As you can see, the subtotals are for the previous group and not the current group.

     

    The function in the amount fields are from custom code and not database fields so I can't use the sum function.

    Here is the expression I used in the amount fields:

    Code Snippet

    =FormatCurrency(Code.SetTotal(Code.GetBudget(Fields!object.Value,Parameters!pca.Value,Parameters!BudgetYear.Value)))

     

    Here is the SetTotal Function:

    Code Snippet

    public decimal _total;

    public decimal SetTotal (decimal total)
            {
                _total = _total + total;
                return total;
            }

     

     

     

    Here is the expression I used to get the subtotal:

    Code Snippet
    =FormatCurrency(Code.GetTotal)

     

     Here is the GetTotal Function:

    Code Snippet
    public decimal GetTotal()
            {
               return _total;
            }

     

     It seems the problem is that the group fires first which contains the GetTotal Function. The GetTotal function to returns a 0. The SetTotal function fires next for the next detail row. I need to have the SetTotal function fire in the detail row before the group fires the SetTotal Function.

    Any ideas?

    Wednesday, June 20, 2007 6:09 PM
  • >>

    I can't use Fields collection because the textbox value is from custom code so there are no Fields.

    <<

     

    Are you absolutely sure you can't do this without referencing the reportitem/textbox value?

     

    Here's what I just did to check

     

    • I created a textbox in the detail band with this expression:


      =Code.MyUDF(Fields!User.Value)

    • I created the following custom code as a stand-in for yours. MyUDF is pretty simple, I recognize that your code will be more complicated than this, but it should serve for the purpose of illustration.

        Public Function MyUDF(tsString As String) AS Integer
           Return LEN(tsString)
         End Function

    • In my group footer, I *repeated* the call to MyUDF but summed it with the appropriate scope, like this -- IOW, there is no relationship between this textbox and the one in the detail band, they just both call the same code to do their work except that the group level has the aggregate function added "embracing" that code:

        =Sum(Code.MyUDF(Fields!User.Value),"table1_Group2")

     

    ... and this appeared to work fine...

     

    BTW I can repro your analysis of the order in which things are triggered with the custom code as you are trying to do it (manually accumulating the total), so that does look like it might be a dead end... I'm just not sure you need it..

     

    >L<


     

    Wednesday, June 20, 2007 8:34 PM
  • Wow, that was simple. I guess I had given up on aggegates because I was trying to use them with ReportItems and it would fail because of scope issues.

    Thanks for your help! I didn't even put the function in the details row.

    I guess the downside of doing it this way is that since the GetBudget function makes a database call, I am calling it another time for the sum instead of working with a variable in the code and summing it manually.

     

    Here is the code if anyone can use it

     

    Group Footer

    Code Snippet
    =FormatCurrency(Sum(Code.CustomFunction.MyTotal(Code.CustomFunction.GetBudget(Fields!object.Value,Parameters!pca.Value,Parameters!BudgetYear.Value)),"table1_Group2"))

     

    Code Snippet

    public decimal MyTotal (decimal total)
            {
                return total;
            }

     

     

    Wednesday, June 20, 2007 9:52 PM
  • >> guess the downside of doing it this way is that since the GetBudget function makes a database call, I am calling it another time for the sum instead of working with a variable in the code and summing it manually.

     

    In that case, although I'm glad you confirm that my suggestion works for you, shouldn't you be doing the work in the view or sproc or whatever is SELECTing the data in the first place? 

     

    There has got to be a way.  It will be a summed column that actually has the same value for each row in the group, of course, and you'll only be using it once -- but still... that's the same as any other group value in the "flattened" datasets typically used in reporting...

     

    Performance, coding style, and bytes-over-the-wire wise, you pays your money and you takes your choice....

     

    >L<

    Thursday, June 21, 2007 2:38 AM
  • Yes you are right. I would like to do the logic in this report using a query. I am trying to duplicate a Java website where all the logic is in the jsp pages and not in sql so I just used the existing queries and tried to put the logic into SSRS to make the report . The data is in a SQL 2000 database so I can't use CTEs yet so I will need to use a cursor, table variable etc to do some recursive work.
    Thursday, June 21, 2007 12:45 PM
  • >> The data is in a SQL 2000 database so I can't use CTEs yet so I will need to use a cursor, table variable etc to do some recursive work.

     

    Not necessarily.  You can set up a SQL 2005 instance with that one as a linked server and do the work on the 2005 side...

     

    I'm just sayin' <g>.

     

    >L< 

    Thursday, June 21, 2007 1:33 PM
  • Another good idea...

    Thanks!

    Thursday, June 21, 2007 5:02 PM