Net Total
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22605
Printed Date: 30 Apr 2024 at 4:51am
Topic: Net Total
Posted By: dax007
Subject: Net Total
Date 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.
|
Replies:
Posted By: DBlank
Date 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
|
Posted By: dax007
Date 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..
|
Posted By: DBlank
Date 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.
|
Posted By: dax007
Date 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,
|
Posted By: DBlank
Date 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
|
Posted By: dax007
Date 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..
|
Posted By: DBlank
Date 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?
|
Posted By: DBlank
Date Posted: 22 May 2018 at 3:17am
|
If you prefer I can give you directions for a Running Total.
|
Posted By: dax007
Date 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...
|
Posted By: DBlank
Date 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}
|
Posted By: dax007
Date 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
|
Posted By: DBlank
Date 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
|
Posted By: dax007
Date 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.
|
Posted By: DBlank
Date 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.
|
Posted By: dax007
Date 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..
|
Posted By: DBlank
Date 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
|
Posted By: dax007
Date 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,
|
Posted By: DBlank
Date Posted: 22 May 2018 at 9:29am
|
check the evaluation formula
|
Posted By: dax007
Date 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.
|
Posted By: DBlank
Date Posted: 22 May 2018 at 9:38am
I usually use IN rather than = when dealing with multiple values but I think that is moot.
to further test you can make another formula that is testing that out
//test1
({Command.DepDescr})=["AAA","BBB"]
Place it on the detail section and you should see a TRUE or FALSE per row. You want to see TRUE for any row that is AAA or BBB.
I would also place the Command.DepDescr field next to it to verify that is the value for the row.
|
Posted By: dax007
Date Posted: 22 May 2018 at 9:42am
Hello,
Not sure what went wrong but I delete both RT and formula and retype it. Then put it on my report and guess what it worked as expected.. So not sure white space was causing the issue? But I have seen such type of behavior in the past for oracle database..
Now it is working..
Again THANK YOU for your time.. You will see more questions from me on this forum as just picked up to work on big reporting project.
Thanks again,
|
|