| Author |
Message |
dax007
Newbie
Joined: 17 May 2018
Online Status: Offline
Posts: 25
|
   Topic: Net Total
Posted: 21 May 2018 at 2:46am |
|
I'm using crystal report XI. I'm trying to calculate the value for some groups only, so for example, I have group lay down as follow
CurMon
AAA
A111 432.2
A112 12.1
Total AAA 444.3
BBB
B111 50.5
B112 31.3
Total BBB 81.8
CCC
C111 60.2
C112 21.1
Total CCC 81.3
I have more groups and subgroups like this. Now I want a "Net Total" that will add up "Total AAA + Total BBB" So it should display as follow,right below the "Total BBB"
Net Total : 526.1
I tried with formula like
If Group1 in ['AAA','BBB']
Then
Sum{CurMon}
But it sum for all values.
Can somebody help me as how do I complete this?
Thanks in advance.
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 May 2018 at 6:13am |
|
3 ways
1) create a formula that converts your data to 0 when a row is not in AAA or BBBB
if table.field in ["AAA","BBB"] then table.number else 0
Sum that formula and place in report header or footer
2) use a running total with a conditional evaluation formula
3) use shared variable formulas, same idea as #2 but with shared variables instead of the built in Running Total UI
|
|
IP Logged |
|
dax007
Newbie
Joined: 17 May 2018
Online Status: Offline
Posts: 25
|
Posted: 21 May 2018 at 7:55am |
|
Thanks for your quick reply. I tried option 1, but I want to sum of only group "AAA" and "BBB" total values, so
Department CurrMon
AAA
A111 2.0
A112 4.0
A113 5.0
TOTAL 11.0
BBB
B111 3.0
B112 7.0
B113 5.0
Total 15.0
Total Dep 26.0 --- This is total of (AAA+BBB)
CCC
C111 2.0
C112 1.0
Total 3.0
As you can see from above example, I have defined Group 1 (AAA,BBB,CCC) group2 (Annn,Bnnn,Cnnn)
Now if I create a formula let's say formula name "Deptotal"
if group1 ("AAA",BBB")
then
Sum {CurMonth}
This formula gives me a total of 29 (26 + 3 (CCC total - That I do not want)
So what should i modify in my formula to get a result of Deptotal = 26?
then how do I insert this formula in my report, so that total display only after the "BBB" ends, but not in other?
Thanks again..
Edited by dax007 - 21 May 2018 at 9:16am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 May 2018 at 9:45am |
|
You cannot conditionally sum groups that way
Create a formula field to converts the data at the row level into something that sums the way you want to see it. Basically if row level field that you are goupining on is in AAA or BBB then use your curmonth value else use 0
example--
//A_B_only
if table.department in ['AAA','BBB'] then table.CurrMon else 0
create another formula field to sum that
sum({@A_B_only})
Place this in the group footer and you should see your number.It will be in each group though. To make it appear only in BBB you have to suppress it with a condition.
|
|
IP Logged |
|
dax007
Newbie
Joined: 17 May 2018
Online Status: Offline
Posts: 25
|
Posted: 21 May 2018 at 10:34am |
|
Thanks for your reply.So I created a formula
//A_B_Only
if GroupName ({Command.DepDescr})=["AAA","BBB"]
Then
Sum ({Command.CurrVal},{Command.DepDescr})/1000
Else 0
So this formula compiled fine without any error.
Then Created another formula
//Realtotal
Sum ({@A_B_Only})
When I tried to check it I got a message that
"This Field cannot be summarized"
Any help Please.
Thanks again,
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 May 2018 at 2:28am |
|
Again, you cannot conditionally sum groups, you have to alter the row level data that is being summed.
Your group condition is the same as a row condition that just is not yet applied to a group.
//A_B_Only
if {Command.DepDescr} IN ["AAA","BBB"] Then {Command.CurrVal} Else 0
Now sum that formula...because every row that is not an AAA or BBB is set to 0 it is the same thing as if you were only summing AAA and BBB.
it looks like you want your sum to be divided by 1000 so do this here
//Realtotal
Sum ({@A_B_Only})/1000
|
|
IP Logged |
|
dax007
Newbie
Joined: 17 May 2018
Online Status: Offline
Posts: 25
|
Posted: 22 May 2018 at 2:40am |
|
Thanks for your reply.. I'm sorry but I'm not sure how to do this "you have to alter the row level data that is being summed"
Can you please advice me on that?
I can try the other formula as you mentioned and let you know the results.
Thanks again..
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 May 2018 at 3:16am |
|
technically you are not altering the row level data, you are just creating a formula to "append" to that data set. At its core you are wanting crystal to only show the sum of a value where the related data field for that value is either AAA or BBB. All we are doing is "creating" (via a formula field) the data that supports that need.
Does that help?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 May 2018 at 3:17am |
|
If you prefer I can give you directions for a Running Total.
|
|
IP Logged |
|
dax007
Newbie
Joined: 17 May 2018
Online Status: Offline
Posts: 25
|
Posted: 22 May 2018 at 7:47am |
|
Hello,
Thanks for your help. I think i got what I was looking for..So what i did is, created running total, with following values
Field to Summarize = {CurMon}
Evaluate = Use Formula.
Formula = {Group_Field} in ['AAA', 'BBB']
Reset = Never
Then Created a second group footer section and put the new running total in it.
Set the Suppress formula for this new section to (do NOT check the Suppress checkbox!):
{Group_Field} <> 'BBB'
Same way I did it for other group as well..
Now on similar report I wanted to Subtract the values. For example,
Department CurrMon
AAA
A111 2.0
A112 5.0
A113 5.0
TOTAL 12.0
BBB
B111 3.0
B112 2.0
B113 5.0
Total 10.0
Net Count 2.0 --- This count is of (AAA-BBB)
I tried to use the field summary but there is no any subtraction. So any Idea how do I do this?
Thanks again...
|
|
IP Logged |
|
|
|
