wrong total values are coming with SUM function

Create report with SharePoint list.

Columns are displaying on the report I used out of the group SUM function and it is group by report. one group total is showing correct total and other groups are showing wrong total and showing too much big value even there is whole column is empty.

where value is not in column I am using "-" with IIF function. these columns are calculating by using date difference function between two date columns. these values coming with minus value . so ABS function is also using.

Columns calculation expression:

=IIF(ISNOTHING(Fields!DateCAPackage.Value)

OR (ISNOTHING(Fields!Date_CA_Application.Value)) , "-" , CINT(Abs(DateDiff("d",Fields!Date_CA_Application.Value,Fields!DateCAPackage.Value))))

Total calculation expression:

=Sum(ABS(DateDiff(

"d",Fields!Date_CA_Application.Value,Fields!DateCAPackage.Value)))


September 7th, 2015 4:42am

Hi John, 

According to your description, when using Sum() function in the text box expression, the results are wrong, right? 

In Reporting Services, we can use Sum() function to return a sum of values at the group or dataset level. In your scenario, you want to sum the values on each column, but the expression return a big value.It's because you haven't set the scope for the Sum() function. For your requirement, you can add the column group into the Sum() function, please refer to the following expression: 

=IIF(ISNOTHING(Fields!DateCAPackage.Value) OR (ISNOTHING(Fields!Date_CA_Application.Value)) , "-" , Sum(Abs(DateDiff("d",Fields!Date_CA_Application.Value,Fields!DateCAPackage.Value)),"ColumnGroupName"))

Reference: 

Sum Function

If you have ant other question, please feel free to ask. 

Regards,
Shrek Li

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 2:32am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics