Get AVERAGE of column data in Reporting Services matrix.

  • I need to create an AVG based on the cell data in row_group1 and the number of months that have data. I have the sum of row_group1, but cannot get a count on number of months, and just entering an AVG function in the total column does not produce the correct results.

    I have two row groups, and a single column group on "Month" that produces row data by "monthname".

    I can get the SUM of my CountDistinct(Fields!CustID.Value), so if I can get the count of the number of "Months" produced by my column group I could calculate my own average, but I cannot get the correct count of months using InScope, (returns all 0's):

    =Iif(InScope("MTH"),(CountDistinct(Fields!CustID.Value)), 0)

    Basic structure of matrix:

    Service Feb Mar Apr AVG # of Distinct Customers per Month

    Cleaning 3 7 2 4

  • Hi,

    Caveat, I've never used or done this before but. . .

    there is a Matrix.Columns Property available in SSRS, which will return the number of columns in the report. Based on that, you should be able to determine how many months are being used, and thus calculate an average.

    I believe that you will have to write some custom code to get to that property (Report, Report Properties, Code) or write you own class/assembly using the language of your choice.

    Otherwise, I would consider calculating it ahead of time in your SQL if possible.

    Good luck.

  • Thank you, I will give that a try, and update you if I can get the results I require.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply