| Author |
Message |
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
   Posted: 22 May 2018 at 7:55am |
|
make a formula field
//A_pos_B_neg_Only
if {Command.DepDescr} IN ["AAA"] Then {Command.CurrVal} Else
if {Command.DepDescr} IN ["BBB"] Then {Command.CurrVal}*(-1) Else 0
Now make the exact same RT you just did but for
Field to Summarize = {CurMon}
do Field to Summarize = {@A_pos_B_neg_Only}
|
 IP Logged |
|
dax007
Newbie
Joined: 17 May 2018
Online Status: Offline
Posts: 25
|
Posted: 22 May 2018 at 8:43am |
|
Hello,
Thanks for your reply..so I created a formula as you suggested and then created a RT
where Field to Summarize = {@A_pos_B_neg_Only}
Type of summary = "SUM"
Evaluate - Use following formula.
({Command.DepDescr})=["AAA","BBB"]
Now when I put it on report and run the report it takes the total value of BBB and make it negative, so it displays
NET COUNT -10
"Net count" is just a label, <10> is the total of BBB
So is anything am I missing or doing wrong?
Thanks
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 May 2018 at 8:51am |
|
It is basically correct except that for some reason it is not including the positive values for AAA
Is AAA above BBB in the report display?
RTs execute in order so if AAA is not 'before' BBB then there is nothing to sum (yet) except negative values from BBB.
What exactly (copy and paste) is your formula for @A_pos_B_neg_Only
You can also place the formula field on your canvas and you should see something like
Department CurrMon Formula
AAA
A111 2.0 2.0
A112 5.0 5.0
A113 5.0 5.0
TOTAL 12.0 12.0
BBB
B111 3.0 -3.0
B112 2.0 -2.0
B113 5.0 -5.0
Total 10.0 2.0
|
|
IP Logged |
|
dax007
Newbie
Joined: 17 May 2018
Online Status: Offline
Posts: 25
|
Posted: 22 May 2018 at 9:04am |
|
Thanks for your quick reply.. Yes AAA is before the BBB.
So here is what I have in my formula
If {Command.DepDescr} in ["AAA"] Then ({Command.CurMon}/1000) Else
If {Command.DepDescr} in ["BBB"] Then ({Command.CurMon}/1000) *(-1) Else 0
Also tried put the formula on report canvas and it does display as the logic says.
So how do I achieve this?
Thank again.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 May 2018 at 9:07am |
|
is your running total set to reset on group change?
it should be set to reset = never
Place your RT next tot he formula field an you can see it 'working' row by row. It is a good way to 'debug' (see where you might have set it up wrong) the RT and its properties.
|
|
IP Logged |
|
dax007
Newbie
Joined: 17 May 2018
Online Status: Offline
Posts: 25
|
Posted: 22 May 2018 at 9:12am |
|
Hello,
Thanks for your reply.Yes it is set to "NEVER" same as previous report setup.
Trying to see where it goes wrong.. Please let me know if you think anything else.
Thanks again..
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 May 2018 at 9:16am |
|
place the RT into the canvas on the detail section and see what it is doing for each row...where does it deviate from what you expected/wanted to see?
It should look like this
Department CurrMon #RT
AAA
A111 2.0 2.0
A112 5.0 7.0
A113 5.0 12.0
TOTAL 12.0
BBB
B111 3.0 9.0
B112 2.0 7.0
B113 5.0 2.0
Total 10.0
|
|
IP Logged |
|
dax007
Newbie
Joined: 17 May 2018
Online Status: Offline
Posts: 25
|
Posted: 22 May 2018 at 9:27am |
|
Thanks for your reply.. So I put the RT on my report,here is what it shows
Department CurrMon #RT
AAA
A111 2.0 0.0
A112 5.0 0.0
A113 5.0 0.0
TOTAL 12.0
BBB
B111 3.0 -3.0
B112 2.0 -2.0
B113 5.0 -5.0
Total 10.0
So as you can see that for group AAA it all shows 0.0, so it seems it's not picking the currmon values for group AAA (A111,A112,A113)
so why it's like this? Is something is not correctly picked up/setup in formula? or RT?
Thanks,
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 May 2018 at 9:29am |
|
check the evaluation formula
|
|
IP Logged |
|
dax007
Newbie
Joined: 17 May 2018
Online Status: Offline
Posts: 25
|
Posted: 22 May 2018 at 9:32am |
|
Thanks for your reply.. It is very straightforward
({Command.DepDescr})=["AAA","BBB"]
Do you see anything wrong here?
Thanks.
|
|
IP Logged |
|
|
|
