on 03-16-2017 9:48 AM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
Could you post the formula you used for '@Sales_Balance1'?
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Kindly check
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.