Running Totals.

[Rychertate]

I have an Oracle View I have brought into Crystal.
Sample Data is this. There are more CostCenters with consisting of various types:

View:
CostCenter Type Hrs
323 M 10
323 V 20
323 L 30
323 Q 39

I do a running totals for Hrs in my reports. I have 6 running totals variables. M, V, L, Q, R, X That I display on my report grouped by CostCenter. However, if a CostCenter is missing any of the 6 types, nothing is shown in my report under that type column. How can I default it to 0?

I want Crystal to be able to handle this issue. My report can't have blanks scattered throughout my report if a Type doesn't exist for that CostCenter.

 

[beanbrain]

If a {Type} is not M, V, L, Q, R, or X what is returned? A blank or a null?

 

[Rychertate]

I don't even know what it returns. It returns nothing.
Example of the output is below.
Whenever a CostCenter has all the types filled with numbers, my Total field displays a value. I set
{Total} = {#M}+{#V}+{#R}+{#X}+{#L}+{#Q}

Currntly diplays:
COSTcenter M V R X L Q Total
323 10 20 30 39

What I desire:
COSTcenter M V R X L Q Total
323 10 20 0 0 30 39 99

 

[adhhealth]

create a new formula field for each running total

@R-- if R="" then 0 else R.

now add up all these formula fields..

it worked for me.. but there might be a better solution.

 

[Rychertate]

The below doesn't work. Even if I take out ToText function, it wants it to be compared to a number and not a string or a null value. Even after the else statement, setting it to a real number value like 5, still doesn't fill in the blank spot. It almost like it doesn't even evaluate when there is no value for that type.

if (ToText ({#R}) = '') then 0
else
{#R}


I'm totally lost now.

 

[adhhealth]

try this

<code>
if isnull(#R)
then
0
else
#R
</code>

 

[beanbrain]

Rychertate:

There is a difference between a null and blank so knowing what is being returned is pretty important.

The code rajaniadh posted should take care of the problem.

 

[Rychertate]

OMG! I almost nut in my pants. It looks good so far. I'll let you know when I finish my report.

 

[GrlwBranz]

Have you tried going into the your File, Report Options, and making sure your "Convert Null to default" is clicked, that way it will take any nulls and turn it into a zero. with PL/SQL if the NVL is not used on the SQL command pull to turn the Null into a specific area it is difficult to find "null" to convert it by formula. Usually the Convert Null to default under the Report Options will fill your needs. Otherwise, try writing the command instead of pulling your table views and create your nvl statement on your item you want it to house 0 of there are nulls.

GrlwBranz