ssrs find count of multiple non-null columns in a row

Question

I need some help with this issue in SSRS to find the YTD(%) for the YTD columns..

I have SSRS 2008 tablix region where the columns are State,Category,January, February, March,...,December, YTD. These columns have both the sums and the percentage values depending on the row. I need help to calculate the YTD when the column is %.

example:

State Category January February March...... YTD

MN A 200 200 0...... 400

MN B 21 12 0........ 33

MN A% 12.5 12.5 0........ ??

For A,B above, the YTD is simply adding the report items January+February+March+.... which will be 400 and 33 as shown(assuming only Jan and Feb have real values > 0).

For the row A% above(??), since this a percent column, my YTD should be the average of the non-null/non-zero columns...in this case (12.5+12.5)/2=12.5 but I am unable to find out the count of the non-null columns for each category (January, February, etc)...I have tried avg,count, sum, etc but i am not getting the correct value. If I can figure out the denomintor(which will be the count of the non-null columns in January, February,...,December), the problem will be solved as I can then do:

( Jan+Feb+March+...+Dec)/Count(Jan,Feb,Mar,..,Dec)

The Count should return me the count of the non-null/non-zero columns only so I can do the average calculation..please help.

Manish Mishra Manish Mishra · Accepted Answer · 2013-04-11T11:01:40

I am not sure what you want.

If all that you want is to find out average of a row in the last column you can do this:

Make the expression of the column in which you want average like this:

Sum(Fields!Jan.Value + Fields!Feb.Value...+Fields!Dec.Value)/
 Sum(IIF(IsNothing(Fields!Jan.Value),0,1) +
     IIF(IsNothing(Fields!Feb.Value),0,1) +
     .
     .
     IIF(IsNothing(Fields!Dec.Value),0,1) 
  )

so, in the denominator, you are doing this:

Sum(1+0+1+1+0...+0)

i.e. 1 is returned if Field is not null, otherwise 0

ssrs find count of multiple non-null columns in a row

1 Answers