CODEWITHSUNDEEP
sqlsql-serverexcelreporting-services
Philip
Philip

Reputation: 2628

Calculation, excluding empty values

I have the following calculation that is running per every Row Group in SSRS:

=(count(Fields!RowNumber.Value)*sum(Fields!RowNumber.Value*Fields!Score.Value) - sum(Fields!RowNumber.Value)* sum(Fields!Score.Value))/(count(Fields!RowNumber.Value)*sum(Fields!RowNumber.Value*Fields!RowNumber.Value) - sum(Fields!RowNumber.Value)* sum(Fields!RowNumber.Value))

The calculation works to display the Slope result.

The issue is that some of the Fields!Score.Values are blank in the Row Groups, so it shouldn't include it in the calculation. RowNumber is a field that adds by 1 for each number of Scores within that Row Group.

How could I take that into account with this calculation to exclude empty values in Fields!Score.Value from the calculation?

As an example:

How it seems to be working      How it should work:
1   0 (converting it to 0)      1   (not take into account this row)
2   0 (converting it to 0)      2   (not take into account this row)    
3   4                           3   4
4   4                           4   4
5   4                           5   4
6   4                           6   4

You can see below that the expression above converts blanks to 0 and finishes up with an incorrect result of 0.914286 where-as the correct result is 0 when it doesn't take into account those blank entries. Below are some other examples also.

This is using the SLOPE functionality in Excel (=SLOPE(F5:F10,E5:E10)) that is re-created in SSRS with the above expression. Excel is seemingly smart enough to ignore the blank entries in the series and I need that same functionality in SSRS expression.

enter image description here

Upvotes: 1

Views: 153

Answers (1)

niktrs
niktrs

Reputation: 10056

Your expression should look like the one below

=(
SUM(Iif(Fields!num1.Value=0,0,1) )
* SUM(Fields!rownum.Value*Fields!num1.Value)
- 
SUM(Iif(Fields!num1.Value=0,0,Fields!rownum.Value)) 
* SUM(Fields!num1.Value)
) 
/ 
(
SUM(Iif(Fields!Score.Value=0,0,1) )
* 
SUM(Iif(Fields!Score.Value=0,0,Fields!RowNumber.Value*Fields!RowNumber.Value))
- 
SUM(Iif(Fields!Score.Value=0,0,Fields!RowNumber.Value))
*
SUM(Iif(Fields!Score.Value=0,0,Fields!RowNumber.Value))
)

Upvotes: 1

Related Questions