on 02-09-2015 11:12 PM
I am trying to create a report where sum of amount would be displayed in 3 different columns based on what is in EDLCode field.
So,
If EDLCode IN (501,502,503) Then Sum(Amount) in Column1
If EDLCode IN (505,506) Then Sum(Amount) in Column2
If EDLCode IN (1500) Then Sum(Amount) in Column3
Let me know if this makes sense. I am getting the report to work when I put fields in detail section but I need only one line per employee, so I am using Group Header and it shows 0 in column 2 and 3.
Final data should look like:
EmployeeName Column1 Column2 Column3
My ultimate aim is to concatenate all this fields in 1 field and be able to send as fixed width file.
Hi Parag,
Here's what you need to do:
1) Create a formula (@Column1) with this code:
If {EDLCode} IN [501,502,503] Then {Amount}
2) Go to Insert Summary > choose the formula field from the list as the Field to Summarize > Choose 'Sum' as the summary operation > Under Summary Location choose the correct Group and click OK
3) Repeat steps 1 and 2 for the other two columns however, make sure you change the EDL codes for each formula.
You can also do this via the Running Total Option however you won't be able to place the sum on the Group Header, it works only on the Group Footer.
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Abhilash,
Thank you for the reply,
Your solution is giving me the total of all amount that have those EDLCode, I need it grouped by employees and also I need to store sum of column1,2 and 3 in a field so I can use that to create a fixed width file.
EmployeeName&''&(Sum of Column1)&''&(Sum of Column2)&''&(Sum of Column3)
One record per employee and sum grouped by employee.
Hope that makes sense.
Abhilash,
I think I got it resolved, your solution helped in decoding it.
I created the summary field as you suggested and then created another formula field and used that summary field in it, but it was giving me total sum, instead of per employee sum.
So I grouped it on Employee and got it to working in the footer, so the final formula looked like.
TotalAmount = (Sum ({@Column1},{PREH.Employee})
where @Column1 = IF {EDLCode} IN [501,502,503} THEN {Amount} and then created a summary field for @Column1
User | Count |
---|---|
75 | |
9 | |
8 | |
7 | |
6 | |
5 | |
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.