Using running totals in formulas - Crystal reports

0
votes

I have created a set of running totals looking for specific fields in a database. If these fields are located, a subsequent sum is performed to calculate the total for that field. e.g. Field to Summarise - DB.Field.Value-Sum. Evaluate - Use a Formula-Field Name ='1'

This sums the totals for this field. The issue is that I have many running totals doing this, and what I want to do is add these together to provide a total for all of these. Currently I have a formula that uses each field with a '+' between each. This appeared to work fine, but when tested against a record where some of these fields are blank, the subsequent formula displays nothing.

Any advise on what I should do here/ am doing wrong?

Thanks

1
crystal-reportsformulacumulative-sum
Blanks means database returning nulls? – Siva

1 Answers

0
votes

It sounds like a null record (empty value) is breaking your running total. You have a few options

  1. Use a formula to check for, and replace a null value with another value (Zero, for example) and then use that formula in your running total calculation

if isnull({Command.Decimal}) then 0 else {Command.Decimal}

  1. Use a SQL expression to replace null with another value Isnull(Tablename.Columnname,0) - use this in your running total
  2. On the running total, under "evaluate" select Use a formula and use this formula not(isnull({tablename.columnname})) -- If the record IS null, the running total does not evaluate it. It will be ignored by the running total. enter image description here