cancel
Showing results for 
Search instead for 
Did you mean: 

Running Total in Cross Table does not consider zero values

Former Member
0 Kudos

Hi to all,

I have created a cross table with Crystal R. 2008 SP1 fix pack 1.3 (that is version 12.1.3.1028).

In this cross table I have used period of time as column (in month/year format) and product categories as rows. I have summarized product quantites per each category/period by using the wizard running total. This running total has been setted to evaluate each record but to be resetted when row changes so when product category changes.

Example: i have 4 periods (1/09, 2/09, 3/09, 4/09) and different categories (A, B). Quantities for a category (i.g. A) are as follows:

category A, period 1/09, qty = 10

category A, period 2/09, qty = 0

category A, period 3/09, qty = 7

category A, period 4/09, qty = 3

Running total displays:

category A, period 1/09, qty = 10

category A, period 2/09, qty = 0

category A, period 3/09, qty =17 (i.e. 10 + 7)

category A, period 4/09, qty = 20 (i.e. 17 +3)

So the running total works but does not consider the period where data (qty) is 0. I want running total to continue summarizing data even in this case so I will get:

category A, period 2/09, qty = 10 (i.e. 10 +0).

Is there any setting to get this result?

Thank you

Iris

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Try this,

Right click on the crosstab > Advanced Calculation > Embedd Summary.

Regards,

Vinay

Former Member
0 Kudos

Hi Vinay,

what kind of formula should I insert in the embedd summary window? I am not very practiced in Crystal. Could you tell me an example of the formula I should use?

Thanks a lot

Regards,

Iris

Former Member
0 Kudos

Is the quantity for category A, period 2/09 zero (i.e., data exists, but the number is zero), or is there no data for category A, period 2/09 (in which case the value would be NULL)?

If the later, you might need to make a formula like (basic syntax):


if isnull({qty}) then
  formula = 0
else
  formula = {qty}
end if

Then, create your running total on the formula field.

HTH,

Carl

Former Member
0 Kudos

Hi Carl,

thank you for your suggestion.

For better understanding I can tell you that in the cross table I have inserted also the field qty which is summarized for each category/period.

This summarized value is 0,00 in those cells (category/period) where there is no data (as in category A, period 2/09) instead of beeing NULL.

And the same occurs for the running total which has been inserted in the cross table as well.

I used formula as per your suggestion but it doesn't work even without syntax error.

So, I changed it in the crystal syntax but without resolving my problem.

My crystal report is based on a simple table where are inserted only rows with data so, in it, there is not a row related to Category A period 2/09. There are olny rows related to category A (and B, and so on) related to periods where qty is <> 0.

unfortunately in a cross table the column of period 2/09 is displayed because other categories have qty <> 0 in that period.

Is there any other way to solve this problem?

Thank you

Regards,

Iris

Answers (0)