Skip to Content

### 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

### 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
• 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.

• 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
• Thanks Ian. Let me try this.

• 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
• 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.