CODEWITHSUNDEEP
sqlcrystal-reportsreportcrystal-reports-2008
Frank
Frank

Reputation: 1

Summaries two column with 0 value on a specific column

I'm sure this is fairly unusual situation, and I can't seem to find a solution.

I'm trying to report on a table of SQL DB; this table has field called sample_bucket which defines which size is being used as sample, within this table we have size01-size24 column. Also we have another field called "Type" and I need to choose between type A and B.

I've used following code on tow "Formula Fields" to extract the sizeXX base on the content of sample_bucket as follow:

formula1:

if {zzpom.Type}= "A" and {zzpom.SAMPLE_BK}=1 then {zzpom.SIZE01}
else
if {zzpom.Type}= "A" and {zzpom.SAMPLE_BK}=2 then {zzpom.SIZE02}
else
.
.
if {zzpom.Type}= "A" and {zzpom.SAMPLE_BK}=24 then {zzpom.SIZE24}
else 0

and also

formula2:

if {zzpom.Type}= "B" and {zzpom.SAMPLE_BK}=1 then {zzpom.SIZE01}
else
if {zzpom.Type}= "B" and {zzpom.SAMPLE_BK}=2 then {zzpom.SIZE02}
else
.
.
if {zzpom.Type}= "B" and {zzpom.SAMPLE_BK}=24 then {zzpom.SIZE24}
else 0

And I filtered on {formula1}<>0 or {formula2}<>0 but when I run the report I get two columns instead on one:

formula1   formula2  |  formula1   formula2  
  6.5         0      |     0         12

I tried to summarize the report but looks like CR runs these formula in spread!

I need to consolidate these two columns to:

formula1   formula2  
  6.5         12

Any help would be greatly appreciated!

Upvotes: 0

Views: 80

Answers (1)

Jeremy Peck
Jeremy Peck

Reputation: 156

If this is within CR then I'm guessing that the formulas are in separate formula fields. I would combine the formulas into a single field.

if ({zzpom.Type}= "A" or {zzpom.Type}= "B") and {zzpom.SAMPLE_BK}=1 then {zzpom.SIZE01}
else if ({zzpom.Type}= "A" or {zzpom.Type}= "B") and {zzpom.SAMPLE_BK}=2 then {zzpom.SIZE02}

Upvotes: 1

Related Questions