Avatar of cinibia_nidhin
cinibia_nidhin
Flag for Kuwait asked on

Finding sum in Crystal Report based upon some condition

Hi,
In Crystal report I need to display the sum of a particular column based upon some condition. I'm using SQL database.
I have a Table named A and have in it I have 2 columns say A1, A2.. Can anyone let me know how to get the sum of those entries in A1 whose corresponding A2 is equal to, say, '1' in Crystal Report 10.0.

Thanks
Crystal ReportsProgrammingSQL

Avatar of undefined
Last Comment
cinibia_nidhin

8/22/2022 - Mon
SOLUTION
peter57r

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
cinibia_nidhin

ASKER
Thanks a lot for your time and consideration.  I could display those totals based upon the value of A2 using running total.
A2 actually holds two values 0 and 1. What i needed was a seperate total depending on its value. Thanks for your help.
 But now I've another problem depending on the same. If the total i got is greater than 12 i need to display only 12 and if its less than 12 i need to display the actual value.
Where and how should i give that condition as i have displayed the total using running total??
SOLUTION
peter57r

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
cinibia_nidhin

ASKER
THANKS.. I've done it. Can u please let me know how to check whether a field value is NULL or not.. I mean, say, i need to check if A2 is null or not. And if its NULL i need to consider it as 0. I used NVL functions but didnt get it working.
Mark Wills

create a formula field... A2_Calc
if {A2} = 1 then {A2} else 0

Another formula field A2_Calc_Total
create a summary field in the report group and point it to A2_Calc - Want to Hide this field so it doesnt actually print.
Then create a new formula - then show the report fields and double click the new formula summary of A2_Calc, then change it into the appropriate if

if( Sum ({@A2_Calc}, {@Group}) ) > 12 then Sum ({@A2_Calc}, {@Group}) else 0  

Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Mark Wills

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
peter57r

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
cinibia_nidhin

ASKER
select ({A.a2})
case 1 :
"TYPE1"
default:
"TYPE2"

Actually what i want is to display a column depending on A2's value. But it displays correctly for all rows which has 0 or 1 value. It isnt considering the null valued rows. I need to display as "TYPE2" if its 0 or null. Can you correct me the above code.
ASKER CERTIFIED SOLUTION
peter57r

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
cinibia_nidhin

ASKER
thank you peter i have done it..
Again, when i take the total of a column using the running total i have added a condition (use a formula in running total)  like A2=1.. But if the column doesnt contain any row with A2=1 its displaying the result empty. Instead of displaying it empy i need the total to be displayed as 0.0..

So please lemme know what should i do for displaying likewise...
SOLUTION
Mark Wills

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mark Wills

Sorry, that should be

if (isnull({A2}) or {A2} = 0) then "TYPE 2" else "TYPE 1"
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
cinibia_nidhin

ASKER
Mark, i got it displayed as type1 or type2.. Now my problem isnt that.
I have a running total field F1 and i have taken the sum of a field say a1 depending on the value of a2. I mean, i have given in the formula field of the running total as A.a2=1 (where A is the table name) .
What i want is I need the the total to be displayed as 0.00 if there is any row satisfying the condition A2=1.
For a particular record there is no such row with a2=1..Hence what is it displaying now is just a blank box in the final report. I need to display 0.0 instead of that. Can you please get me a solution for that??
Hope you got what i meant.
SOLUTION
peter57r

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
cinibia_nidhin

ASKER
THANKS A LOT FOR YOUR GREAT HELP. Everything is working fine for me.