Print Page | Close Window

Summaries two column with 0 value on each column

Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17585
Printed Date: 19 Apr 2025 at 12:57pm


Topic: Summaries two column with 0 value on each column
Posted By: Frankn
Subject: Summaries two column with 0 value on each column
Date Posted: 18 Sep 2012 at 7:48am
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!


Replies:
Posted By: hilfy
Date Posted: 20 Sep 2012 at 5:21am
Try this:
 
1. Group by Sample_bk.
2. Put the data in the group header.
3. Instead of using Formula1 and Formula 2 directly, create summaries that get the Max of each and use them to display your numbers.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window