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

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

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.

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.

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

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

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.

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.

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.

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.

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.

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...

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...

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.

Sorry, that should be

if (isnull({A2}) or {A2} = 0) then "TYPE 2" else "TYPE 1"

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.

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.

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.

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

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??