Group By,sum
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 11 of 11

Thread: Group By,sum

  1. #1
    Join Date
    Mar 2013
    Posts
    10

    Group By,sum

    Goodmorning
    I want little help again.
    I have a field (1,2,3 values) that i have done it group by so i take something like that

    1.Income
    1 Thomas test 40
    2 thomas test2 30
    sum:70

    2.Spends
    1.thomas test 23
    2.thomas test 35
    sum:58


    Total sum: income:70- spend:58

    *Now i have done all the above but i cant do the last row. I dont know what field to put and how i can sum my groups above


    I create a formula field and i have done something like that.

    shared numbervar rtTotal;

    if({Emp016.PelProm}="1") Then
    rtTotal:=rtTotal+Sum ({Emp016.SynTeliko})

    where the field pelprom is the field i group by and take values (1,2,3) and the field synteliko is the field with ammounds.

    Now i want to get that at the end of report and not at the end of every group

    GrandTotals
    ---------------------
    Incomes: (sum of group with value 1)
    Spends: (Sum of group with value 2)
    Storesum of group with value 3)
    Total: Incomes-Spends-Store

    So at the end i want 4 new fields but i dont know how to do it...Any help?

  2. #2
    Join Date
    Jul 2005
    Posts
    1,076

    Re: Group By,sum

    Create 3 new formula fields
    name it as you like
    insert them in Detail Section & suppress them

    #1
    Code:
    WhilePrintingRecords;
    numberVar totIncomes;
    If {Emp016.PelProm} = "1" Then 
      totIncomes:= totIncomes + {Emp016.SynTeliko}
    #2
    Code:
    WhilePrintingRecords;
    numberVar totSpends;
    If {Emp016.PelProm} = "2" Then 
      totSpends:= totSpends + {Emp016.SynTeliko}
    #3
    Code:
    WhilePrintingRecords;
    numberVar totStore;
    If {Emp016.PelProm} = "3" Then 
      totStore:= totStore + {Emp016.SynTeliko}
    Now in Report Footer Section you can use the formulaes
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  3. #3
    Join Date
    Mar 2013
    Posts
    10

    Re: Group By,sum

    Hi
    thanks for the reply. For a strange reason it works but only for this

    WhilePrintingRecords;
    numberVar totStore=0;
    If {Emp016.PelProm} = "3" Then
    totStore:= totStore + {Emp016.SynTeliko}

    Everything seem normal, but to other 2 it brings me as a result =0;

  4. #4
    Join Date
    Mar 2013
    Posts
    10

    Re: Group By,sum

    Hi again. Your code works but for a strange reason works only if there are records in one of tree parts. For example if there are records at group 1 it works but if there are records on 3 groups it works only for the last one

  5. #5
    Join Date
    Mar 2013
    Posts
    10

    Re: Group By,sum

    Hi again. Your code works but for a strange reason works only if there are records in one of tree parts. For example if there are records at group 1 it works but if there are records on 3 groups it works only for the last one... I didnt understand that

    insert them in Detail Section & suppress them

  6. #6
    Join Date
    Jul 2005
    Posts
    1,076

    Re: Group By,sum

    "insert them in Detail Section & suppress them "
    It means that you must insert the 3 formula fields in the details section of the report
    and mark them as "suppress"
    Right click on each formula, select format field and mark the "Suppress" property
    It means that we don't want to see them in the report, we only want to execute
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  7. #7
    Join Date
    Mar 2013
    Posts
    10

    Re: Group By,sum

    Ok. I got that. But how after that i will print them on report footer where i want to see them? I did what you told me and i put the 3 formulas on details and suppress them. Everything there seem ok. Now to print them at the end of the report i create 3 new formulas and i put inside that:
    {@SumInocome} so the new field to see the other field we put the code you said. But it brings me 0 and i dont know what i have done wrong. Is any other way to print the result of the 3 fields of the details??? Sorry for my questions but i have start work with crystal last month
    Last edited by Melina291; March 16th, 2013 at 03:23 AM.

  8. #8
    Join Date
    Jul 2005
    Posts
    1,076

    Re: Group By,sum

    (Sorry, I gave you only the half of the solution)
    Yes
    You can insert each variable at report Footer Section with another formula field as :
    #4
    Code:
    WhilePrintingRecords;
    numberVar totIncomes;
    totIncomes;
    Last edited by jggtz; March 16th, 2013 at 03:37 AM.
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  9. #9
    Join Date
    Jul 2005
    Posts
    1,076

    Re: Group By,sum

    Also, you can use only 1 formula field instead of 3, as :
    #1
    Code:
    WhilePrintingRecords;
    numberVar totIncomes;
    numberVar totSpends;
    numberVar totStore;
    
    If {Emp016.PelProm} = "1" Then 
      totIncomes:= totIncomes + {Emp016.SynTeliko}#2
    ElseIf {Emp016.PelProm} = "2" Then 
      totSpends:= totSpends + {Emp016.SynTeliko}#3
    ElseIf {Emp016.PelProm} = "3" Then 
      totStore:= totStore + {Emp016.SynTeliko}
    And use the variables at Report Footer as I posted in post #8
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  10. #10
    Join Date
    Mar 2013
    Posts
    10

    Re: Group By,sum

    Ok it works finally. But i want to ask something more...
    When i print th 3 variables on the report footer like: Incomes
    Spend
    Store
    -------------

    I want something more like total at the end. What variable i should put for this. When i put numchar
    total=Incomes-Spend-Store and the result was - it brought me false. What variable i have to use;

    i put that but its wrong
    WhilePrintingRecords;
    numberVar totBuys;
    numberVar totIncomes;
    numberVar totStore;
    numberVar Teliko;

    Teliko=totIncomes-totStore-totBuys;
    Last edited by Melina291; March 16th, 2013 at 06:42 AM.

  11. #11
    Join Date
    Jul 2005
    Posts
    1,076

    Re: Group By,sum

    Insert a new formula field, name it MyTotal or whatever you like
    Code:
    WhilePrintingRecords;
    numberVar totIncomes;
    numberVar totSpends;
    numberVar totStore;
    numberVar tottot;
    totot:= totIncomes - totSpends - totStore
    ... and place the formula field at report footer

    The problem with your code is that the character : is missing in the operation
    Teliko:=totIncomes-totStore-totBuys;
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center