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
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
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.
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
Thanks Ian. Let me try this.
Could you post the formula you used for '@Sales_Balance1'?
-Abhilash
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.