Search code examples
crystal-reportscrystal-reports-server

How to sum filed based on day type in crystal report

The table has two columns named dayType and total, total column already has value and only want to sum records that have the same dayType.

I coded this 

 if{EB_Tam_DailyMv.dmv_daytype}= "R" then 
 sum({EB_Tam_DailyMv.dmv_totalot})

 else if {EB_Tam_DailyMv.dmv_daytype} = "P" then 
 sum ({EB_Tam_DailyMv.dmv_totalot})

it suppose to print the total of R days but it cannot work or my logic is not right ...

Advance thanks for help

Solution

  • You are adding the same field {EB_Tam_DailyMv.dmv_totalot} in the case the days are "R" type both the days are "P" type, so when you use "sum" formula crystal report add all field {EB_Tam_DailyMv.dmv_totalot} of the report regardless of the type of day.

    if you want to create a sum of all "P" days and separately the sum of "R" days you have to create 2 different formula like this:

    formula1: SumOfP

    if uppercase({EB_Tam_DailyMv.dmv_daytype}) = "P" then 
{EB_Tam_DailyMv.dmv_totalot} else 0

    formula2: SumOfR

    if uppercase({EB_Tam_DailyMv.dmv_daytype}) = "R" then 
{EB_Tam_DailyMv.dmv_totalot} else 0

    and put them in your report(if you want you can hide them), then create two "running total fields", one that sum SumOfP formula and one that sum SumOfR formula.