Link to home
Start Free TrialLog in
Avatar of cinibia_nidhin
cinibia_nidhinFlag 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
SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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  

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry, that should be

if (isnull({A2}) or {A2} = 0) then "TYPE 2" else "TYPE 1"
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
THANKS A LOT FOR YOUR GREAT HELP. Everything is working fine for me.