Skip to Content

Sum for group in memory

Hello everybody:

I need some help, I have a tabular datawindow and it´s a data entry, it has a money field and I have to group for 3 fields and sum the money field,

the problem is the data is not in database for doing a sum group by, by the way if the sum is less than 0 for any grouping I can´t save the data. Can I sum in memory or something like that.

I will appreciate any help

Regards

SandraE

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Apr 05, 2016 at 09:35 PM

    If all your required data is in the datawindow the simplest is create a computed field in a group header or trailer. You should do a sort() and GroupCalc() before saving to ensure the sum is calculated correctly as part of your validation prior to Update(). Note that the sort() will cause the datawindow to scroll to first row.

    Add comment
    10|10000 characters needed characters exceeded

    • Sandra,

      Be sure to include "for group 1" inside the sum function of the calculated field in the dw:

      Field: c_money_sum

      Expression: sum(money_field for group 1)

      You can get the sum value with the GetItemNumber function:

      decimal ldec_sum

      ldec_sum = dw_1.GetItemNumber(ll_i, 'c_money_sum')


      The thing is the GetItemNumber will give you the group sum associated with each particular row. So it will repeat itself until a row from a new group is reached. AFAIK you cannot get the group value for a specific group.


      Let's say your data is grouped in three rows each. You'll get the same value for the first three rows and then a new value for the next three rows and so on. So you need to know when a row changes group to get the new group value and validate it. In other words, you need to use a loop and read the group value in each row to know when a group has changed.


      Regards,

      Ricardo

  • avatar image
    Former Member
    Apr 05, 2016 at 11:06 PM

    Hi Sandra;

    The DataWindow has a Group feature of course and a Sum () method support. This would create a computed column. I would put the summed column (field) in the DW's summary band. You can make it non-visible if you like. To access computed columns in the header, footer or summary band, just use the standard GetItemXxxx() method - always specifying Row #1.

    Using the DW's internal Sum() method is much faster than performing a Sum within a loop plus, the pain of coding the loop in the first place.

    Tip: combine an IF() method within a Sum() to conditionally count/Sum anything. That way, you could have 3 group calculations for example using three computed Sum(If)) columns. ;-)

    HTH

    Regards ... Chris

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 07, 2016 at 03:11 PM

    If you want to do a group sum and validations based on the sum, here's some guides for you:

    First step is you create the group and specify the sort order accordingly. The group can be based on one column or on multiple columns as one group.

    Second is you create two sum compute fields. Both have the same expressions/formula ( e.g. sum(col1 for group 1) ). Name one field as group_sum1 and the other one detail_sum1. Place the group_sum1 in the Trailer of the Group band and the detail_sum1(you can hide this field) in the Detail band.

    Third, you do a Find (using the DW Find method) for detail_sum1 which is less than 0 or whatever validations you want to make. You will need to access the detail_sum1 (NOT the group_sum1) field to validate the group sum.

    Note: There is also a FindGroupChange DW method that you may find useful.

    HTH,

    Neil

    Add comment
    10|10000 characters needed characters exceeded