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

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

I've used following code on the "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 consolidate the report but looks like CR runs these formula in spread!

I need to consolidate these two columns to:

formula1   formula2
6.5        12

To make it clearer I've made a sample of the DB as follow:

Sample_bk | Type | … | size_6 | size_7 | size_8 |
    7      |      A      | … |     ..     |   6.5   |   ..   |
    7      |      B      | … |     ..     |   12    |   ..   |

The condition is the same for both except the type would be different and meanwhile I’m picking up the Data from same field name such as size_7 because sample_bk = 7.


Any help would be greatly appreciated!