hi guys
my report is a Customer Ageing Report. It shows for a customer what he owed and paid us over the past 5 weeks. So the 5 weeks represent 5 Columns in my report. First week balance goes in Column 1, similarly 2nd week balance goes in column 2 and so on.
So now what the users want is that for any one customer
For example
:-For a customer if all of the 5 columns(or weeks) I am getting a negative value than the result should sum up all the columns and only show the result in the first column rest of the columns should remain blank. -ve values mean advance payment by customer. like for C1 in example below.
:-If there are no u2013ve values than the result should remain as is for that row. like for C2 in example below.
:-And if the row has +ve and u2013ve values for the 5 columns than I need to sum up all the values and display result on the first column where the u2013ve value was generated for that row. like for C3 in example below.
Customer Week1 Week 2 Week 3 Week 4 Week 5
C1 -100 -33 -5 -5 -3
C2 100 400 300 0 0
C3 20 -40 100 0 0
this should show up to user like below.
Customer Week1 Week 2 Week 3 Week 4 Week 5
C1 -143 0 0 0 0
C2 100 400 300 0 0
C3 0 -80 0 0 0
Each column is created using Restricted keyfigs restricted by certain date ranges.
How can this be done ?