Skip to Content
avatar image
Former Member

SAP Crystal Total rowrow calculation based on specific Group Name

Hello All,

Consider below case.

I want to insert total rows (yellow) between Group1 Footer and Header. And its calculation based on calculation of specific group name. For example I want to add group 1 totals of only group name = 'Sales' & Cost and put them in new row named as 'TOTAL (COST+SALES)'.

Similarly 'TOTAL(EXPENCE-SALES)'.

Position of these rows is not priority now (we can put them at the bottom when all values of group 1 are ending).

However could you please guide me on how to calculate these kind of totals.

Thanks in Advance.

Kind Regards,

Naoman D

total-issue.png (24.8 kB)
total-issue.png (24.8 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Mar 16, 2017 at 10:55 AM

    Hi Naoman,

    Here's what you need to do:

    1. Insert a Group Footer 1b and Group Footer 1c section.

    2. Create a formula called @Sales_Balance1 with this code:

    If {Group Field} = 'Sales' then {Balance 1 Field}

    3. Create a formula called @Expenses_Balance1 with this code:

    If {Group Field} = 'Expenses' then {Balance 1 Field}

    4. Create a formula called @Cost_Balance1 with this code:

    If {Group Field} = 'Cost' then {Balance 1 Field}

    5. Create a formula called @Cost_Sales_Balance1 with this code and place it on Group Footer 1a:

    Sum({@Sales_Balance1}) + Sum({@Cost_Balance1})

    6. Create a formula called @Expenses_Sales_Balance1 with this code and place it on Group Footer 1b:

    Sum({@Expenses_Balance1}) + Sum({@Sales_Balance1})

    7. Go to the Section Expert > Highlight Group Footer1a > Click the formula button beside 'Suppress' and use this code:

    {Group Field} <> 'Cost'

    8. Highlight Group Footer1b > Click the formula button beside 'Suppress' and use this code:

    {Group Field} <> 'Expenses'

    Repeat Steps 1 through 6 for 'Balance 2' field, by creating separate formulas with the same logic.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Abhilash,

      I tried above steps but id step 5 & 6 I'm getting error like 'only recurring fields can be sum' aslo when I tried putting reference object like account number to sum up it s showing sum only if I put it below given group foorter.

      If I want to put all the calculations rows like 'Cost + Sales' & 'Expense + Sales' at the report footer what would be formula ( i mean no section expert hiding, put all such calculations at the bottom). Thanks in advance.

  • avatar image
    Former Member
    Mar 16, 2017 at 09:57 AM

    Kindly check

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 16, 2017 at 10:32 AM

    You need to use a series of variables

    @Reset -- this is optional but is good practice, but is also required if you have a higher group level and should be placed in Higher grouplevel header or in your example report header, and can be suppressed

    Whileprintingrecords;

    Global numbervar Sales:=0;

    Global numbervar Cost:=0;

    Global numbervar Expenses:=0;

    Split Groupfooter 1 section into two sections in section expert of new blank section go to formula for suppress section and enter

    not(Group1Field in ['COST', 'EXPENSES')

    @Eval -- place this in top group footer and suppress

    Whileprintingrecords;

    Global numbervar Sales;

    Global numbervar Cost;

    Global numbervar Expenses;

    If Group1Field = 'SALES' then Sales:=sum(valuefield, Group1Field) //repeat for Cost and expenses

    @Display Vars -- Place this in bottom section

    Whileprintingrecords;

    Global numbervar Sales;

    Global numbervar Cost;

    Global numbervar Expenses;

    If Group1Field = 'COST' then Sales+Cost;

    If Group1Field = 'EXPENSES' then Expenses-Sales

    Place two text labels in the blank footer with conditional suppression

    eg TOTAL SALES + COST with suppression Group1Field <> 'COST'

    Good luck

    Ian

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 23, 2017 at 07:56 AM

    Could you post the formula you used for '@Sales_Balance1'?

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Abhilash,

      Balance Formula is if posting date = parameter then value in local currency (direct measure). I mean the value of the measure is restricted by date entered by user in selection.